Google Answers Logo
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.
Answer  
There is no answer at this time.

Comments  
Subject: 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.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  


Google Home - Answers FAQ - Terms of Service - Privacy Policy