View Question
Q: Excel Reformat Data ( No Answer,   1 Comment )
 Question
 Subject: Excel Reformat Data Category: Computers > Software Asked by: speery-ga List Price: \$20.00 Posted: 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.```
 ```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(D110,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.```