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

 

[acustico]

How could I do it with Date, summing hours? Re: If duplicate numbers exist then sum values in another column / same row -

I have a similar problem and cannot find an answer anywhere else. I already checked the forum question.

I have in Column A (trips/walks), in column B (date and time for a "turn" during the trip). Trip 1 had two turns, therefore we collected two "readings" of date/time. Trip 2 has three turns, etc..

I want to estimate the amount of time spent in each trip by subtracting the first reading of date/time to the last reading. e.g. =B2-B1 for trip 1, =B5-B3 for trip 2, =B7-B6 for trip 3. I have thousands of trips and readings for each trip. Ideally, I would have a formula and copy down this formula in column C to get the hours for each trip.

[TABLE="width: 169"]
<tbody>[TR]
[TD="align: right"]A1=1[/TD]
[TD="align: right"] B1=5/23/2013 7:48[/TD]
[/TR]
[TR]
[TD="align: right"]A2=1[/TD]
[TD="align: right"]B2=5/23/2013 8:00[/TD]
[/TR]
[TR]
[TD="align: right"]A3=2[/TD]
[TD="align: right"] B3=5/23/2013 8:14[/TD]
[/TR]
[TR]
[TD="align: right"]A4=2[/TD]
[TD="align: right"]B4=5/23/2013 8:22[/TD]
[/TR]
[TR]
[TD="align: right"]A5=2[/TD]
[TD="align: right"] B5=5/23/2013 8:24[/TD]
[/TR]
[TR]
[TD="align: right"]A6=3[/TD]
[TD="align: right"] B6=5/23/2013 11:08[/TD]
[/TR]
[TR]
[TD="align: right"]A7=3[/TD]
[TD="align: right"]B7=5/23/2013 11:16[/TD]
[/TR]
</tbody>[/TABLE]


I have tried to modify the code below (from Andrew Poulsom) but I cannot make it to work. Any suggestions welcomed?

Thanks in advance
Tony

In C1:

=IF(COUNTIF(A$1:A1,A1)=COUNTIF(A:A,A1),SUMIF(A:A,A1,B:B),"")

copied down.

Edit

For the duplicates only:

=IF(AND(COUNTIF(A$1:A1,A1)=COUNTIF(A:A,A1),COUNTIF(A:A,A1)>1),SUMIF(A:A,A1,B:B),"")

 

[Andrew Poulsom]

Re: How could I do it with Date, summing hours? Re: If duplicate numbers exist then sum values in another column / same row -

In C1 coped down:

=IF(A2=A1,B2-B1,0)

 

[acustico]

Re: How could I do it with Date, summing hours? Re: If duplicate numbers exist then sum values in another column / same row -

In C1 coped down:

=IF(A2=A1,B2-B1,0)

Hi Andrew, thanks for the prompt response. This formula works great but if I have many more readings in a trip, I could not estimate the hours for the entire trip. Or after this formula, should i use the formula you suggested previously?

I probably did not explain that sometimes I have more than two of readings listed for a single trip (sometimes i have dozens). My goal was trying to use a formula that subtract the oldest date/time from the newest date/time for a single trip, e.g., in trip 2, which has three readings, A5-A3. Or if I have 25 readings in a trip, subtract the first reading of the trip (the oldest date/time) from the last reading (newest date/time).

Please, If i am not explaining well, let me know.

Thanks
Tony

 

[acustico]

Re: How could I do it with Date, summing hours? Re: If duplicate numbers exist then sum values in another column / same row -

Thank you Andrew, I think I got it. I used the both your formulas suggested in this Thread. The second one to get the hours (=IF(A2=A1,B2-B1,0) and the first one (=IF(COUNTIF(A$1:A1,A1)=COUNTIF(A:A,A1),SUMIF(A:A,A1,B:B),"")) to get the sum of hours spent in a trip.

I appreciate your help,

Tony Celis

Hi Andrew, thanks for the prompt response. This formula works great but if I have many more readings in a trip, I could not estimate the hours for the entire trip. Or after this formula, should i use the formula you suggested previously?

I probably did not explain that sometimes I have more than two of readings listed for a single trip (sometimes i have dozens). My goal was trying to use a formula that subtract the oldest date/time from the newest date/time for a single trip, e.g., in trip 2, which has three readings, A5-A3. Or if I have 25 readings in a trip, subtract the first reading of the trip (the oldest date/time) from the last reading (newest date/time).

Please, If i am not explaining well, let me know.

Thanks
Tony

 

[Shriddhi Parab]

Not an answer to you question but don't you mean 5=2 (rows 1 and 5)?

Hell All,

I have a complex condition to be applied in my macro. I am new to macros so any guidance will be of help to me.
following is the condition:

please find below sample table:
[TABLE="class: cms_table, width: 192"]
<tbody>[TR]
[TD="class: cms_table_xl64, width: 64"]CHECK ID[/TD]
[TD="class: cms_table_xl64, width: 64"]Remarks[/TD]
[TD="class: cms_table_xl64, width: 64"]marks[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63"]math001[/TD]
[TD="class: cms_table_xl63"]correct[/TD]
[TD="class: cms_table_xl63, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63"]math001[/TD]
[TD="class: cms_table_xl63"]wrong[/TD]
[TD="class: cms_table_xl63, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63"]eng002[/TD]
[TD="class: cms_table_xl63"]wrong[/TD]
[TD="class: cms_table_xl63, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63"]math001[/TD]
[TD="class: cms_table_xl63"]correct[/TD]
[TD="class: cms_table_xl63, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63"]eng002[/TD]
[TD="class: cms_table_xl63"]wrong[/TD]
[TD="class: cms_table_xl63, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63"]geo003[/TD]
[TD="class: cms_table_xl63"]correct[/TD]
[TD="class: cms_table_xl63, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63"]eng002[/TD]
[TD="class: cms_table_xl63"]wrong[/TD]
[TD="class: cms_table_xl63, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63"]geo003[/TD]
[TD="class: cms_table_xl63"]correct[/TD]
[TD="class: cms_table_xl63, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63"]geo003[/TD]
[TD="class: cms_table_xl63"]correct[/TD]
[TD="class: cms_table_xl63, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63"]geo003[/TD]
[TD="class: cms_table_xl63"]correct[/TD]
[TD="class: cms_table_xl63, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63"]sci004[/TD]
[TD="class: cms_table_xl63"]wrong[/TD]
[TD="class: cms_table_xl63, align: right"]0[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63"]sci004[/TD]
[TD="class: cms_table_xl63"]correct[/TD]
[TD="class: cms_table_xl63, align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]


now for every duplicate entry in column CheckID I want to check the remarks, if first entry is "correct" then marks have to be considered as 1 for that entry, if second occurrence of the CheckiD column is also "correct" then NO marks have to be considered for that entry, i.e marks should be 0 and not 1.

If all the remarks value for any particular CheckID is "wrong", then just one of them should be considered and have marks 0, the rest can be ignored.

 

[leadheadph]

In C1:

=IF(COUNTIF(A$1:A1,A1)=COUNTIF(A:A,A1),SUMIF(A:A,A1,B:B),"")

copied down.

Edit

For the duplicates only:

=IF(AND(COUNTIF(A$1:A1,A1)=COUNTIF(A:A,A1),COUNTIF(A:A,A1)>1),SUMIF(A:A,A1,B:B),"")

Andrew,

This worked perfect for a similar problem I had. However, I want the summed value to show up in the row of the FIRST unique duplicate number. So if my duplicates are A3:A6, I would want the total to show in C3.

 

[Aladin Akyurek]

Andrew,

This worked perfect for a similar problem I had. However, I want the summed value to show up in the row of the FIRST unique duplicate number. So if my duplicates are A3:A6, I would want the total to show in C3.

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

A​

[/td][td]

B​

[/td][td]

C​

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

1​

[/td][td]

1​

[/td][td]

10​

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


[tr][td]

2​

[/td][td]

2​

[/td][td]

100​

[/td][td]

210​

[/td][/tr]


[tr][td]

3​

[/td][td]

2​

[/td][td]

100​

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


[tr][td]

4​

[/td][td]

2​

[/td][td]

10​

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


[tr][td]

5​

[/td][td]

3​

[/td][td]

10​

[/td][td]

110​

[/td][/tr]


[tr][td]

6​

[/td][td]

3​

[/td][td]

100​

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


[tr][td]

7​

[/td][td]

4​

[/td][td]

50​

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

In C1 enter and copy down:

=IF(INDEX(FREQUENCY(MATCH($A$1:$A$7,$A$1:$A$7,0),
    MATCH($A$1:$A$7,$A$1:$A$7,0)),ROWS($A$1:A1))>1,
    SUMIFS($B$1:$B$7,$A$1:$A$7,$A1),"")

SUMIFS can be replaced with SUMIF if the former is not available to you.

 

[JesterJayJoker]

Hi, I have a similar question. I have 3 Columns here.

So basically,

Col A. Col B Col C
Ef555 12/2/2016 15
Ef555 12/2/2016. 15
Ef555 12/3/2016. 4
Ef555. 12/6/2016. 1
Ef555. 12/6/2016. 1


So basically the Sum of Ef555 Col C should be 20. Anytime Col C is duplicated by Col A I want it to be ignored.

Any help is appreciated. Thanks!

 

[Aladin Akyurek]

@JesterJayJoker

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

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

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

1​

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

2​

[/td][td]Ef555[/td][td]

12/2/2016​

[/td][td]

15​

[/td][td][/td][td]Ef555[/td][td]

20​

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

3​

[/td][td]Ef555[/td][td]

12/2/2016​

[/td][td]

15​

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

4​

[/td][td]Ef555[/td][td]

12/3/2016​

[/td][td]

4​

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

5​

[/td][td]Ef555[/td][td]

12/6/2016​

[/td][td]

1​

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

6​

[/td][td]Ef555[/td][td]

12/6/2016​

[/td][td]

1​

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

In F2 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($A$2:$A$6=E2,IF(ISNUMBER(1/$B$2:$B$6),MATCH($B$2:$B$6,$B$2:$B$6,0))),ROW($B$2:$B$6)-ROW($B$2)+1),$C$2:$C$6))

 

[JesterJayJoker]

In F2 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($A$2:$A$6=E2,IF(ISNUMBER(1/$B$2:$B$6),MATCH($B$2:$B$6,$B$2:$B$6,0))),ROW($B$2:$B$6)-ROW($B$2)+1),$C$2:$C$6))

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.