Combine Rows with Calculation (Dupe in one column)

[NewYears1978]

What is the best way to combine rows where one column contains a duplicate field (in this case an order number) - and then one column is a dollar amount that needs to be summed.
Basically each line is a line item on an order, and so the order number appears multiple times for each item. Want to combine the sales orders into one row with total sales.

I have tried manually combine/group options in Excel and they don't work as intended.

I tried kutools which has a very specific tool to do this but won't work if you have more than around 500 rows.

I have columns A:BJ (a lot of columns)
The Primary key item is in Column E (SalesOrder) and the Dollar amount is in Column BJ (TotalLineSalesPrice)
I would really like to do diff things in some other rows besides take the first value but at this point I don't care about that I just want to combine the rows with same SalesOrder value and sum the TotaLineSalesPrice value.

Is VBA the best way to do this? not sure why this has been so difficult for me - been fighting it for three weeks now.


I really don't need all the columns so to simplify if we want to just say we have 4 columns and I can try to modify it from there.
Order Date, Sales Order, Part Number, LinePrice

Combine based on dupe in SalesOrder, and sum the LinePrice

The closest thing I found was the Consolidate feature but this seems to only work when you just have two columns and not extra columns (or any extra columns will either sum also or just be blank)

[maniacb]

Can you share a sample file as per the yellow banner above? Make sure to include the before and after views.

[NewYears1978]

Can you share a sample file as per the yellow banner above? Make sure to include the before and after views.

Yes sorry - I always forget to do that on my initial post because it's sensitive data and I have to make a fake version, will do that now.
Also there are so many columns that it's hard to read the fake data =D

Sample attached.

You can see the "duplicate" column to look at is SalesOrder and then the very last column is Summed, everything else just keeps the first value (for simplicity sake)

Been struggling so much with Excel lately because these files have 800k rows and it is just not wanting to handle that much data

[sintek]

One way...Assumes data is in order...

Please Login or Register  to view this content.

[NewYears1978]

I will try that, thank you. I really need to fully learn VBA - I can write some stuff but mostly it's piecing together things here and there!


I got a type mismatch on this:
Data = .Cells(1).CurrentRegion.Value: ReDim Temp(1 To UBound(Data, 1), 1 To UBound(Data, 2))

[maniacb]

Here is another solution for you. This will create a 'Data' sheet where the results will be placed.

Please Login or Register  to view this content.

[NewYears1978]

That one gave me an error also after running awhile (I have a ton of rows) it then created the data tab and gave error which I accidentally closed.

I am running it again to see if I can see the error again.

Type mismatch:
h(r, 33) = h(r, 33) + a(j, 33)

[maniacb]

Try opening the file we provided and see if the file structure looks different from your working file. I assume all the column data in your working file is the same as the sample data you provided. The error implies that the data type in column 33("AG") is not a number, is it in your working data?

[maniacb]

I amended the code to only add when the value in column AG is a numeral. But if the number is being presented as text, you'll need to review that data column so as not to get inaccurate results. Also, ensure column AG is formatted as a number, not text.

Please Login or Register  to view this content.

[sintek]

I got a type mismatch on this:
Data = .Cells(1).CurrentRegion.Value: ReDim Temp(1 To UBound(Data, 1), 1 To UBound(Data, 2))

Does not happen with your sample file and upload in Post 4...Perhaps your actual file setup is not exactly the same as sample?

[NewYears1978]

I am dumb, I forgot in the sample I removed a bunch of unnecessary columns.
I was running the code on my original with all columns up to BJ. I will try again.

Both seemed to work now. Now I just need to see if it will work on 800k rows. I tested it on 70k and it worked. Previously all methods I tried was crashing. Even "kutools" built in feature for that could not handle it.

Thanks!