Condensing multiple rows into one row and adding values
Posted by Caleb N. Diffell on August 23, 2001 5:52 AM
I have a VERY repetitive task that needs a macro to automate: I get a 12,000-line spreadsheet where our invoices are SOMETIMES split out over mulitple lines. What I need to do, is every time (in column AA) an invoice number is listed more than once, to delete the extra lines...BUT...I need to take the dollar values from each of the lines with that invoice (dollar values are in column X) and ADD them to the remaining invoice line. Once the process is done, I need to have only ONE line per invoice. So the macro would first have to find where there is more than 1 line for an invoice by comparing the values in AA (sort by AA first), then ADD the corresponding rows in X to the first row of that particular invoice, then delete the extra invoice lines. Thanks in advance for any ideas!!
Caleb
Posted by Eric on August 23, 2001 7:27 AM
you're on the edge of a pivot table working
Do you know how many unique invoices you have in your 12k line report? Because excel's pivot table can report 8k unique lines and would suit your task well if its within spec.
Posted by Caleb on August 23, 2001 7:32 AM
Re: you're on the edge of a pivot table working
There can be any number of unique invoices. I can't say there will NEVER be more than 8000. Also, there are many other columns of data in this list (that don't get changed); a pivot table will only show the columns I sort by, right? I need to submit this to a vendor once it's done, so I have to have it in a normal excel sheet with no formulas.
Posted by Eric on August 23, 2001 1:07 PM
I agree, but...
What I was thinking was that you could run a pivot table on a second sheet and get the values you need. Then use a formula to put the values back into the original sheet.
So if your pivot table was on sheet 2, and the numbers of interest were in col(b), then on sheet 1 starting in AB2 (assuming a header row, and AB is available) you could type the formula:
=if(aa2=aa1," ",vlookup(aa2,sheet2!a:b,2,false))
and the aa2=aa1 copied down should detect repeat entries and give them blanks, while the first entry in the aa column will get the sum value from the vlookup of the pivot table.
Then you could copy and paste as values to remove all of the formulas prior to sending it to the vendors.
Sorry if I'm not being much help here, they switched me to decaf :(
Posted by IML on August 23, 2001 1:21 PM
I would use the Data consolidate method.
First make sure your columns have titles like "Invoice#" and "Amount"
Simply put go to a new page in the workbook.
In Cell A1 go to data consolidate.
In the reference box highlight cells AA through X
Check the use labels in top row and left column.
Hit okay.
Take a look at http://www.mrexcel.com/tip035.shtml for some other ideas.
Good luck
Posted by Eric on August 23, 2001 1:33 PM
cool! (NT)
