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)

"Frank Situmorang" wrote in message
...
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