How to remove duplicates based on several columns, summate values and calculate weightings

[chrisab]

Hi all,

I've been trying to work out how to do this and have done a lot of google research but can't find the answer to my particular task. So I was hoping for some help please

I've got a lot of data (200k lines) that I need to perform some analysis on. This includes removing duplicates based on several columns, summating the values of certain columns in each individual group of rows that were duplicates and then performing some further calculations and adding of additional columns.

I can use the remove duplicates command in Excel (2010) but can't do the additional processes. I think I could use an array sumif on the raw data based on the processed (removed duplicates) data to find the summated values but thought there must be a better way of doing it!

I created a dummy excel file showing before and after but can I not upload it?

I've copied the data from excel below but sorry for the formatting. The remove duplicates needs to be based on columns (Eng,Code,NORM and Job Number), columns NORM thru to units actual (conv) are to be summated for each consolidated row.

I then need to calculate, for each processed row (which for a given Eng, Code and Norm will be differentiated by Job number) the weighted % of units and Desc (G or R) - this is to be weighted by the SUM of units (converted) or SUM of units actual (converted) for the Eng and Code that the job number belongs to.

Hope this makes sense but can put down the calculations (shame I can't upload the file) - I've added in a brief desc of the calcs below.

Many thanks for any help!

BEFORE
Eng	Code	NORM	Conversion	Units	Units (converted)	Norm Actual	Units Actual	Units Actual (conv)	Desc	Job Number
Loc 1 TME	75	2	10	10	100	3	8	80	G	1992
Loc 1 TME	75	2	10	10	100	4	8	80	R	1992
Loc 1 TME	75	2	10	7	70	2	10	100	G	1992
Loc 1 TME	75	1	5	8	40	4	12	60	R	1991
Loc 1 TME	75	1	5	15	75	1.5	16	80	G	1991
Loc 1 TME	75	3	1	18	18	3	19	19	G	1400

<tbody>
</tbody><colgroup><col><col><col span="3"><col><col span="2"><col><col><col></colgroup>

AFTER											Weighted by units (conv)	Weighted by actual units (conv)
Eng	Code	NORM	Conversion	Units	Units (converted)	Norm Actual	Units Actual	Units Actual (conv)		Job Number	Units (conv) %	% G	% R	Units actual (conv) %	% G	% R
Loc 1 TME	75	2	10	27	270	9	26	260		1992	67% (this is 270/(270+115+18)	63% (this is (100+70)/100+70+100)	37%	62%	69%	31%
Loc 1 TME	75	1	5	23	115	5.5	28	140		1991	29%	65%	35%	33%	58%	42%
Loc 1 TME	75	3	1	18	18	3	19	19		1400	4%	100%	0%	5%	75%	25%

<tbody>
</tbody><colgroup><col><col><col span="3"><col><col span="2"><col><col><col><col><col><col><col><col><col></colgroup>

Thanks,
Chris

 

[Ruca13]

I have not thought about the exact calculation you need since i think after this they will be "normal" formulas.

On your raw data add two columns, with one being: =Eng & Code & Norm & Job Number
And the other a countif for that whole column, using as criteria the cell on the left (the lookup on that row), which will give you an idea of how many times you have the values. This formula is not decisive for the rest of the job, but since you're doing an analyses, it's probably a good info to hold.

With this you can do simples sumifs (I've tried it the the units column and works fine). Personally, I would copy that lookup column to a new sheet, remove duplicates (you would need to do this step from time to time but as long as the formulas are functional should work correctly), and then decompose the lookup with lefts and middles and rights to also get the information in that table (on the first formula use "-" as a separator, it would help if you try to follow this advice).

 

[chrisab]

I have not thought about the exact calculation you need since i think after this they will be "normal" formulas.

On your raw data add two columns, with one being: =Eng & Code & Norm & Job Number
And the other a countif for that whole column, using as criteria the cell on the left (the lookup on that row), which will give you an idea of how many times you have the values. This formula is not decisive for the rest of the job, but since you're doing an analyses, it's probably a good info to hold.

With this you can do simples sumifs (I've tried it the the units column and works fine). Personally, I would copy that lookup column to a new sheet, remove duplicates (you would need to do this step from time to time but as long as the formulas are functional should work correctly), and then decompose the lookup with lefts and middles and rights to also get the information in that table (on the first formula use "-" as a separator, it would help if you try to follow this advice).

Hi Ruca13,

Thanks for the reply. I have followed your advice but as expected evern doing the first column of sumif's on 200k of lines is taking ages to calculate (only got to 5% after 10mins). This just isn't going to work unfortunately, even as a one off, as we need the spreadsheet to be edited in future.

There must be a more efficient way of doing it - possibly VBA? The remove duplicates function based on the 4 columns works in seconds so presumably adding in the sums wouldn't add too much more time? I just don't know how to code in VBA!

Thanks,

 

[chrisab]

Maybe the other option is to create the unique identifier columns as you say and then use a pivot table to calculate the sums, then copy that into another sheet and use a lookup to pull the information.

Not very elegant though!