Excel Function to selectively transpose data from column to row 1

[SpeedThink]

Currently, I have an Excel Worksheet with approximately 10000 records. Several records contain the same account number but different payment amounts and the corresponding payment dates as displayed below.

Acct---Pymt Amt----Pymt Date---Date_1st Pymt---Amt_1st Pymt
90382--$500.00-----01/13/2004
90382--$466.00-----03/24/2004
90382--$234.56-----04/20/2004
90386--$300.00-----02/05/2004
90386--$476.45-----03/10/2004

My challenge is to "normalize" the worksheet by using a function that will populate several columns titled as such "date of 1st payment", "amt of 1st payment", "date of 2nd payment", "amt of 2nd payment", and so on...

In essence, the data is currently in a columnar format and I want to "selectively transpose" certain values per account number. Can this be done? Thus far, I have experimented with the following formula with no success:

=IF(OFFSET(A2,MATCH(A2,J2:J$5072,0)-1,0)=F2,OFFSET(J2,MATCH(A2,J2:J$5072,0)-1,0),"")

After "selectively transposing" the desired data, I will delete all records for that account number except the first one.

Thanks in advance.

 

[PeterMoran]

Hi Speedthink,

Firstly I think what you want to do is the opposite to actually normalizing the data!

Secondly I think you should have a look at Pivot Tables, I'm no expert on them but they enable you to massage large lists of data.

Thirdly if you want to stay with your original thoughts the following function loaded into your workbook (if you put it in your personal.xls you have to invoke it as '=personal.xls!vlookupnth(a,b,c,d)') may help:

Function VLOOKUPNTH(lookup_value, table_array As Range, _
col_index_num As Integer, nth_value)
' Extension to VLOOKUP function. Allows for finding
' the "nth" item that matches the lookup value.
Dim nRow As Long
Dim nVal As Integer
Dim bFound As Boolean
VLOOKUPNTH = "Not Found"
With table_array
For nRow = 1 To .Rows.Count
If .Cells(nRow, 1).Value = lookup_value Then
nVal = nVal + 1
End If
If nVal = nth_value Then
VLOOKUPNTH = .Cells(nRow, col_index_num).Text
Exit Function
End If
Next nRow
End With
End Function

This enables you to effectively do a VLOOKUP on on a table and get the second, third, and fifteenth entries.

Good Luck!

Peter Moran

 

[SpeedThink]

Thanks. This worked perfectly!