• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

adding values against Duplicate rows

dev.devil.1983

New Member
Hi All,

I need help on the following :

I have an excel with duplicate rows(4 columns), with fifth column having some numbers. I need to identify all the duplicates rows and add up their numbers in first column. Such that only one unique row remains of each combination with the summed up value against it. Also, the solution needs to be in excel formula

eg :

Before:
col1 col2 col3 col4 col5
A B C D 4
A C D B 5
A B C D 3
A C D B 9

After:
col1 col2 col3 col4 col5
A B C D 7
A C D B 14

Thanks,
Dev
 
Hi dev ,

I think I understood you,

In the first column, A always appears ?

There are more than two duplicates rows ?

And upload a file, the most important!

David
 
upload_2018-7-31_14-42-51.png

1] In G3, CSE formula (Confirmed enter with Ctrl+Shift+Enter) copied across and down :

=IFERROR(INDEX(A$3:A$7,SMALL(IF(FREQUENCY(IF(A$3:A$7<>"",MATCH($B$3:$B$7&$C$3:$C$7&$D$3:$D$7,$B$3:$B$7&$C$3:$C$7&$D$3:$D$7,0)),ROW($B$3:$B$7)-ROW($B$2)),ROW($B$3:$B$7)-ROW($B$2)),ROWS($1:1))),"")

2] In K3, copied down :

=IF(G3="","",SUMIFS(E:E,A:A,G3,B:B,H3,C:C,I3,D:D,J3))

Regards
Bosco
 

Attachments

  • RemoveDupInMultiCol.xlsx
    11.9 KB · Views: 5
Thanks Bosco for the reply, but being a novice I am having a hard time understanding the solution, while this solution works for 4+1(data column), I tried but failed to do it for 5+1(data column).
I even tried breaking up the solution to understand how the functions are working but it doesn't work for me ..
for instance the following sub-function
MATCH($B$3:$B$7&$C$3:$C$7&$D$3:$D$7,$B$3:$B$7&$C$3:$C$7&$D$3:$D$7,0)

is supposed to provide the location of the pattern in the array but it returns #VALUE.

It would be of great help and really nice of you if you could please elaborate on the solution and also help me modify the solution in following conditions

1) if there are 5 columns instead of 4, against 1 data column.
2) If the number of rows are more, lets say exceeding 200.

Thanks again,

Regards,
Dev
 
Last edited by a moderator:
Hi dev ,

If you were referring to my questions, you might have gotten a suitable solution.

David
 
Sorry for that David, In the first column as well the letters are random. Attached is a file for your reference.
Also, while the number of columns are fixed, the number of rows can be anywhere between 150 to 200
 

Attachments

  • RemoveDupInMultiCol2.xlsx
    10.5 KB · Views: 6
Last edited by a moderator:
Hi p45cal, if you could please elaborate on this solution please.
The values in columns Data1,Data2,Data3,Data4,Data5 as well as Data6 are being derived using formulas from other sheets. The solution has to be dynamic such that, any changes done on the parent sheets, should automatically reflect here. Also, the process has to be automatic, such that we do not need to do any changes in this sheet everytime there are changes elsewhere.
 
Last edited by a moderator:
The values in columns Data1,Data2,Data3,Data4,Data5 as well as Data6 are being derived using formulas from other sheets.
That doesn't matter to a pivot table.

The solution has to be dynamic such that, any changes done on the parent sheets, should automatically reflect here.
A pivot needs refreshing when the source data changes, however this can be automated very easily, refreshing when the source data changes or when that sheet with the pivot is activated/selected.
There is an option for the pivot table to refresh on the file being opened.

Also, the process has to be automatic, such that we do not need to do any changes in this sheet everytime there are changes elsewhere.
Well, the only thing that might need maintaining is the extent of the source data (which the formula approach needs too), and even that could be rendered unnecessary, either by making the source data into an Excel Table, or by having a dynamic data range (in a Name) that the pivot refers to for it source data.

The attached has a pivot whose source data range far exceeds the current extent of the data - a quick and dirty solution.
 

Attachments

  • vbaExpress39303RemoveDupInMultiCol2.xlsx
    14.4 KB · Views: 3
Back
Top