Conversion from columnwise to rowwise

[Frank Situmorang]

Hello,

When importing my access report into excel this is the layout:
Summary of Outstanding Items:
Prj no. Project name Currency Invoice Amount
-------- ---------------- ------------------
xx02 ABC project RP. 2,500
USD. 100

XX03 PQR Project EURO 200
RP 10,000
SGD 2000
Yen 2500
Not all project has the currency, some times it has only Rp./local Currency.

I want to have convert it in one row by projects so that I can make formula
to convert it into Rp/our reporting currency more or less as follows:

Prj no. Project name Euro Rp. SGD USD Yen

xx02 ABC project 2500 100

XX03 PQR Project 200 10,000 2000 2500

I appreciate your helping on what formula/VBA/Macro can I have to do it

Thanks a lot

Frank

 

[Bob Phillips]

Public Sub ProcessData()
Dim i As Long
Dim iLastRow As Long
Dim iRow As Long
Dim iCol As Long
Dim sh As Worksheet

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
Set sh = Worksheets("Sheet2")
sh.Range("A1:G1").Value = Array( _
"Prj no.", "Project name", "Euro", "RP", "SGD", "USD", "Yen")
iRow = 1
For i = 2 To iLastRow
If .Cells(i, "A").Value <> "" Then
iRow = iRow + 1
sh.Cells(iRow, "A").Value = .Cells(i, "A").Value
sh.Cells(iRow, "B").Value = .Cells(i, "B").Value
End If
iCol = Application.Match(.Cells(i, "C").Value, sh.Rows(1), 0)
sh.Cells(iRow, iCol).Value = .Cells(i, "D").Value
Next i
End With

End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Joel]

This code should work. It will copy data from sheet1 and place it in sheet
2. Enter in sheet 2 the header row from your posting. Put all the
currencies in this header row, not just the ones from your example. the code
searches this header row to find the correct column to place the amounts.

Add to sheet 2 row 1
Prj no. Project name Euro Rp. SGD USD Yen


Sub FixCurrencies()

'Get Range of currencies on Sheet2
With Sheets("Sheet2")
LastColumn = .Cells(1, Columns.Count). _
End(xlToLeft).Column
Set CurrRange = .Range(.Cells(1, "C"), _
.Cells(1, LastColumn))
End With

With Sheets("Sheet1")
'get last row
LastRow = 0
For ColumnCount = 1 To 7
Last = .Cells(Rows.Count, ColumnCount). _
End(xlUp).Row
If Last > LastRow Then LastRow = Last
Next ColumnCount

'Get each row of original sheet
Sh2RowCount = 1
For Sh1RowCount = 1 To LastRow
If Not IsEmpty(.Cells(Sh1RowCount, "A")) Then
Sh2RowCount = Sh2RowCount + 1
'copy project info to new worksheet
Sheets("Sheet2").Cells(Sh2RowCount, "A") = _
.Cells(Sh1RowCount, "A")
Sheets("Sheet2").Cells(Sh2RowCount, "B") = _
.Cells(Sh1RowCount, "B")
StartCol = 3
Else
StartCol = 1
End If

'find next column with data
Do While StartCol <= Columns.Count
If Not IsEmpty _
(.Cells(Sh1RowCount, StartCol)) Then

Exit Do
End If
StartCol = StartCol + 1
Loop

'skip rows with no data
If StartCol <= Columns.Count Then
Set c = CurrRange.Find _
(what:=.Cells(Sh1RowCount, StartCol), _
LookIn:=xlValues)
If Not c Is Nothing Then
'find next column with data
StartCol = StartCol + 1
Do While IsEmpty _
(.Cells(Sh1RowCount, StartCol)) And _
StartCol <= Columns.Count

StartCol = StartCol + 1
Loop

Sheets("Sheet2"). _
Cells(Sh2RowCount, c.Column) = _
.Cells(Sh1RowCount, StartCol)
Else
MsgBox ("Missing Currnecy : " & _
.Cells(Sh1RowCount, StartCol))

End If
End If
Next Sh1RowCount
End With

End Sub

 

[Frank Situmorang]

Bob, thank your for your quick response. Could you explain some more, on how
you get ("A1:G1 and sh.Cells(iRow, "A").Value = .Cells(i, "A").Value

I am not so clear on this since I am an Accountant, but try to do it my self
by just selfthought.

Thanks very much.

Frank

 

[Bob Phillips]

Frank,

A1:G1 is just the cells in row 1 of the target sheet that I drop the
headings into.

I use iRow to keep track of the row written to on the second sheet, and i is
used to track the row I am on within the source sheet. So .Cells(i,
"A").Value is the current value in column A of the source sheet, and I write
that to the next free row on sheet 2.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Frank Situmorang]

Thanks Joel, I will start understanding all this codes first. But anyway, are
all thes codes to be combined as one macro?, and I will assingn it to a
Autoshape button?. Where shall I write these code is it in sheet 1 or sheet 2.

Thanks in advance.

Frank

 

[Frank Situmorang]

Joel,

Maybe I need to give you full description of my excel imported report. Since
this a summary of multi grouping in access database, so it is not excactly in
a line for the amount in currency so the layout is like this to be exactly
shown with line number
A B C D
1 Prj no. Project name Currency Invoice Amount
2 xx02 ABC project
3 RP. 2,500
4 USD. 100
5 2,600

There are 2 things which may be taken into consideration in the VBA:
1. Line 2 or project number and name is not in one line with Rp. Currencies
2. There is a subtotal for each project which is actually not needed (see
2600), because it is the total of mixed currencty, but since it was orginated
by the access program itself, so it carries the amount. We do not need this
to be taken in the rowwise report ( or sheet 2 as you said)

Does you suggested code will work even the the format is like that?

Thanks in addvance for your help.

Frank