Excel Reformat Data
Category: Computers > Software
Asked by: speery-ga
List Price: $20.00
06 Nov 2006 13:59 PST
Expires: 07 Nov 2006 09:13 PST
Question ID: 780594
I need to change an excel file from format a to format b without doing it manually. Format A: EmailA Trans1 Date1 EmailA Trans2 Date2 EmailA Trans3 Date3 Format B: EmailA Trans1 Date1 Trans2 Date2 Trans3 Date3 In other words I need to change multiple row entries for one person to show all on the same row for one person.
|There is no answer at this time.|
Re: Excel Reformat Data
From: robotguy-ga on 06 Nov 2006 19:18 PST
Given the first three columns are A, B and C and are sorted by email address, add the following formulas in columns D, E, F, G, H, I ... D: =IF(A11=A10,D10+1,1) E: =IF(D11<D12,0,D11) F: =IF($E11>0,OFFSET($A11,-($D11-1),1),"") G: =IF($E11>0,OFFSET($A11,-($D11-1),2),"") H: =IF($E11>1,OFFSET($A11,-($D11-2),1),"") I: =IF($E11>1,OFFSET($A11,-($D11-2),2),"") J: =IF($E11>2,OFFSET($A11,-($D11-3),1),"") K: =IF($E11>2,OFFSET($A11,-($D11-3),2),"") ... Add as many columns as the largest number of transactions changing the number after $E11 and $D11 appropriately. You can add 'lots' if you don't know how many that is, the formulas will not overstate the transactions Once complete, copy down for every row. Then copy/paste values for every entry. Sort by Transaction1 which will give you the information you need at the top or bottom of the list. Delete the rest.
If you feel that you have found inappropriate content, please let us know by emailing us at firstname.lastname@example.org with the question ID listed above. Thank you.
|Search Google Answers for|