R B
asked on
Excel 2007: How can I easily change rows of data to populate into columns?
I have one invoice with several lines going down. I have a charge-card bill that has several line items. I've downloaded it and added columns to encode it out to enter to our Financial/Accounting Software. For me to upload it - I need to have all of that data reading on "one line" - i.e. all in columns (not rows).
What is the simplest way for me to do this? - without me getting into any elaborate pivoting process. Or - is pivoting the only way to do it?
I want to auto load it - and - need to have all the data that is now in one column for account number, $ amount, charge-description : to go into the same row, horizontally. Now, the whole file is vertically place with each row having the same invoice number, same vendor name, and then one line item (account to charge, charge-vendor description, $ amount) per row.
Please assist.
Thank you,
RajB.
What is the simplest way for me to do this? - without me getting into any elaborate pivoting process. Or - is pivoting the only way to do it?
I want to auto load it - and - need to have all the data that is now in one column for account number, $ amount, charge-description : to go into the same row, horizontally. Now, the whole file is vertically place with each row having the same invoice number, same vendor name, and then one line item (account to charge, charge-vendor description, $ amount) per row.
Please assist.
Thank you,
RajB.
If a simple copy, select cell, pastespecial transpose won't work, can you share an example of the before and after?
ASKER
Here is a sample: One Tab shows what I have, and the other tab shows a simulation of what I want to do automatedly. Thank you...
ASKER
Here is a sample: One Tab shows what I have, and the other tab shows a simulation of what I want to do automatedly. Thank you...
Sample-Invoice---Vertical-Data-t.xlsx
Sample-Invoice---Vertical-Data-t.xlsx
You'll need VBA do do this. Pivot table will not put it all on one row, but if you could, you'd still not get exactly that output.
Do you want a VBA macro?
Dave
Do you want a VBA macro?
Dave
ASKER
Yes... Surely - Dave... Can you advise me on how to do a VBA Macro for this? I will have this scenario replicating several times during a week. Thanks....
The key is: Invoice Number System Vendor ID Invoice Date Invoice Post Date
Correct? For every unique combination of the 4 above, we want to add the charge date,item,amount,descripti on until there is no more data for that combination.
Also, will the data be sorted?
Dave
Correct? For every unique combination of the 4 above, we want to add the charge date,item,amount,descripti
Also, will the data be sorted?
Dave
ASKER
Yes...
ASKER
Hi, Dave... Yes, we can have multiple charges on the same day. Actually - the only unique fields would be "invoice number" and "vendor id". The date of the invoice nor its post date are relavant. So - the two unique items would be: Vendor ID, and Invoice Number. That's it. Does that make the VBA Macro easier to create?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great!! Thank you!!