If duplicate numbers exist then sum values in another column / same row

[HarryFröhlich]

Hi Everybody!

Although it was easy to figure out how to find duplicate values in a column and to calculate the number of times each entry has been duplicated, if at all, my need extends beyond that and this is where I now need your assistance, please.

The scenario: If a number is duplicated in a specific range of column A (e.g.), I need to sum all the values of column B (e.g.) that are on the same row as the duplicate entry and list that value in the row where the "last" (down in the column) duplicate entry is situated.

EXAMPLE

A1: 1 B1: 10
A2: 2 B2: 100
A3: 2 B3: 100
A4: 2 B4: 10
A5: 3 B5: 10
A6: 3 B6: 100
A7: 4 B7: 50

As you can see, Column A has two sets of duplicates: 2 is duplicated 3x and 3 is duplicated 2x.

I need to sum the values of column B in the rows where the 2 is duplicated in column A (answer = 100 + 100 + 10 = 210) AND the same for 3 (10 + 100 = 210) and place these values in column C.

Where in column C?

IN ADDITION to the above, I need the formula to write the totals of the duplicates in the row of each LAST unique duplicate number, that is, 210 must be placed in C4 and 110 in C6.

And that folks, is it!

Thanks for your attention — oh, and by the way, I'd like to be able to do it without VBA as I'd like a continuous update on the values in column C without having to run a macro. A function written in VBA is, obviously, just dandy.

If all else fails and only VBA will do the trick, is there some code that can simply run the macro when duplicate values are encountered as they are entered into the spreadsheet and ignore it otherwise?

Regards

Harry

 

[Aladin Akyurek]

I did this and it worked when it was one value. However, when it has more ID's, it messes up. Such as sorting a table by date or the amount if Col C. If I sort the table by lowest number in Col C. Which is 0's, the value becomes 0.

Did you apply control+shift+enter to the formula, that is, press down the control and the shift keys at the same time, while you hit the enter key? If done properly, Excel itself puts a pair of { and } around the formula in recognition.

 

[dbsays]

This post has been very very helpful- thank you. I'm trying to modify this to match in two columns instead of 1. Is that possible? I have multiple location IDs with some duplicates, each location id has a county and sometimes the counties match and sometimes they don't. I need to count how many total visits per location id and county.


[TABLE="width: 234"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 298"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD]location ID[/TD]
[TD]county[/TD]
[TD]visits[/TD]
[TD]hoping for[/TD]
[/TR]
[TR]
[TD]3194002[/TD]
[TD]A [/TD]
[TD]17[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3194002[/TD]
[TD]A[/TD]
[TD]2.8[/TD]
[TD="align: right"]19.8[/TD]
[/TR]
[TR]
[TD]3871708[/TD]
[TD]B [/TD]
[TD]2.8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3871708[/TD]
[TD]C[/TD]
[TD]2.8[/TD]
[TD="align: right"]5.6[/TD]
[/TR]
[TR]
[TD]3081702[/TD]
[TD]A[/TD]
[TD]2.8[/TD]
[TD="align: right"]2.8[/TD]
[/TR]
[TR]
[TD]3081702[/TD]
[TD]P[/TD]
[TD]77[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3081702[/TD]
[TD]P[/TD]
[TD]46[/TD]
[TD="align: right"]123[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

This is what I wrote, but it's not summing by correctly:
=IF(AND(COUNTIF(A$2:A2,A2)=COUNTIF(A:A,A2),COUNTIF(B:B,B2)),SUMIFS($C:$C,A:A,A2),"")
adding B to the SUMIFS statement doesn't work either.

 

[dbsays]

I think I figured it out:
=IFS(AND(COUNTIF(A$2:A2,A2)=COUNTIF(A:A,A2),COUNTIF(B1,B2)=COUNTIF(B1,B2)),SUMIFS($C$2:$C2,$A$2:A2,A2,$B$2:B2,B2),AND(COUNTIF(A$2:A2,A2)<>COUNTIF(A:A,A2)),SUMIFS($C$2:$C2,$A$2:A2,A2))

If anyone has any tips or a cleaner version I'm all ears.

I pasted the values into the next column, sorted by visits, largest to smallest, then hit remove duplicates by location ID and county and it kept the 1st instance (the one with the largest visit value because of how I sorted).

 

[dbsays]

I actually used:
=IFS(AND(COUNTIF(A$2:A2,A2)=COUNTIF(A:A,A2),COUNTIF(B1,B2)=COUNTIF(B1,B2)),SUMIFS($C$2:$C2,$A$2:A2,A2,$B$2:B2,B2),AND(COUNTIF(A$2:A2,A2)=COUNTIF(A:A,A2),COUNTIF(B1,B2)<>COUNTIF(B1,B2)),C2,AND(COUNTIF(A$2:A2,A2)<>COUNTIF(A:A,A2)),C2)

 

[Aladin Akyurek]

I actually used:
=IFS(AND(COUNTIF(A$2:A2,A2)=COUNTIF(A:A,A2),COUNTIF(B1,B2)=COUNTIF(B1,B2)),SUMIFS($C$2:$C2,$A$2:A2,A2,$B$2:B2,B2),AND(COUNTIF(A$2:A2,A2)=COUNTIF(A:A,A2),COUNTIF(B1,B2)<>COUNTIF(B1,B2)),C2,AND(COUNTIF(A$2:A2,A2)<>COUNTIF(A:A,A2)),C2)

This formula does not produce the output you posted in post #22 ...

In fact it doesn't agree with the following either...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][/tr][tr][td]

1​

[/td][td]location ID[/td][td]A[/td][td]B[/td][td]C[/td][td]P[/td][/tr]
[tr][td]

2​

[/td][td]

3194002​

[/td][td]

19.8​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][/tr]
[tr][td]

3​

[/td][td]

3871708​

[/td][td]

0​

[/td][td]

2.8​

[/td][td]

2.8​

[/td][td]

0​

[/td][/tr]
[tr][td]

4​

[/td][td]

3081702​

[/td][td]

2.8​

[/td][td]

0​

[/td][td]

0​

[/td][td]

123​

[/td][/tr]
[/table]

where

=SUMIFS($C$2:$C$8,$A$2:$A$8,$E2,$B$2:$B$8,F$1)

is implemented in in F2, copied across, and down.

 

[savosean]

I have a very similar issue but it's a tad more complex.

I'd like to find duplicate entries column A lets say, and have it sum column B, and consolidate that total by deleting the other rows.

For Example:

Column A: Column B:

1111 300
1112 300
1111 700
11112 700

would like it to become

A: B:
1111 1000
1112 1000

I would need to do this for over 5000+ entries, and that's why I've been trying to find a way to complete this without going in and doing it manually or adding them all together. Thanks in advance, sorry if I bumped an old thread but this was very similar to what I needed.

 

[Aladin Akyurek]

@ savosean

Consider building a pivot table from your source data.