How to Return/Sum Multiple Values in Excel while disragarding Duplicates

[douglasjoh]

Hello!

First time Posting,

I've been given a new project at work where I need to look up customers and come back with the customers multiple contract numbers in one cell. As well as Summing their total order quantities in these contracts (of the same product) in a separate cell - While Disregarding Duplicates in the data. I realise this is quite complicated, but is there anyway this is possible? I have been using Index/Match but this brings back the first value it finds.

Please see below an example of the sort of data I have to retrieve these answers from:


Thank you!
[TABLE="width: 214"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Contract[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]A1[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]A2[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]A3[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]A4[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]A5[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]A6[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]A7[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Julie[/TD]
[TD]A8[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]A5[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]A10[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]A2[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]A12[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]A13[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]A3[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]A15[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]A16[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]A2[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]A18[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]A13[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Julie[/TD]
[TD]A20[/TD]
[TD="align: right"]15[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]

 

[sergioMabres]

What is yr expected result
1. Jim A1, A2, A18 140
2. Jim 140
Sergio

 

[douglasjoh]

What is yr expected result
1. Jim A1, A2, A18 140
2. Jim 140
Sergio

Hi Sergio,

I'm looking for answers in two separate boxes so:

Jim:
A1, A2
140

Cheers,

Douglas

 

[sergioMabres]

Hi Douglas
For starters you need to eliminate duplicates in other area of your sheet, that is to say have unique values of the column Customer, then you have a couple of ways to do this, there are very clever ppl that can do this using formulas, I will give you an answer with a UDF
For Contracts use the UDF =catbykey(E2,$A$2:$B$21,2) this function concatenates from a range using a key value
Here is the function

Function catbykey(key As Range, r As Range, col As Integer) As String
Dim sep As String
Dim isin As Integer
catbykey = ""
For i = 1 To r.Rows.Count
   If key.Value = r(i, 1) Then
      If catbykey = "" Then
         sep = ""
      Else
         sep = ", "
      End If
      isin = InStr(1, catbykey, r(i, col))
      If isin <= 0 Then
         catbykey = catbykey & sep & r(i, col)
      End If
   End If
Next i
End Function

For the sum you can use a sumif like =SUMIF($A$2:$A$21,E2,$C$2:$C$21)

Here is a sample sheet to download https://1drv.ms/x/s!AovCE1fDrrdSj1RiMzb_flymElQ_

Cheers
Sergio

 

[Eric W]

If you want a formula solution:

Excel 2012

	A	B	C	D	E	F	G	H	I
Jim	A1, A2, A18
A1	A1
A2	A1, A2
A18	A1, A2, A18

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF"]Customer[/TD]
[TD="bgcolor: #FFFFFF"]Contract[/TD]
[TD="bgcolor: #FFFFFF"]Quantity[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFFF"]Customer[/TD]
[TD="bgcolor: #FFFFFF"]Contracts[/TD]
[TD="bgcolor: #FFFFFF"]Quantity[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF"]Jim[/TD]
[TD="bgcolor: #FFFFFF"]A1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]40[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]80[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF"]Jim[/TD]
[TD="bgcolor: #FFFFFF"]A2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFFFF"]John[/TD]
[TD="bgcolor: #FFFFFF"]A3[/TD]
[TD="bgcolor: #FFFFFF, align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFFFF"][/TD]
[TD="bgcolor: #FFFFFF"]Contracts[/TD]
[TD="bgcolor: #FFFFFF"]Quantity[/TD]
[TD="bgcolor: #FFFFFF"]Helper[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FFFFFF"]John[/TD]
[TD="bgcolor: #FFFFFF"]A4[/TD]
[TD="bgcolor: #FFFFFF, align: right"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]40[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FFFFFF"]Bill[/TD]
[TD="bgcolor: #FFFFFF"]A5[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]30[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FFFFFF"]Bill[/TD]
[TD="bgcolor: #FFFFFF"]A6[/TD]
[TD="bgcolor: #FFFFFF, align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FFFFFF"]Mary[/TD]
[TD="bgcolor: #FFFFFF"]A7[/TD]
[TD="bgcolor: #FFFFFF, align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FFFFFF"]Julie[/TD]
[TD="bgcolor: #FFFFFF"]A8[/TD]
[TD="bgcolor: #FFFFFF, align: right"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FFFFFF"]Bill[/TD]
[TD="bgcolor: #FFFFFF"]A5[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FFFFFF"]John[/TD]
[TD="bgcolor: #FFFFFF"]A10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FFFFFF"]Jim[/TD]
[TD="bgcolor: #FFFFFF"]A2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FFFFFF"]Bill[/TD]
[TD="bgcolor: #FFFFFF"]A12[/TD]
[TD="bgcolor: #FFFFFF, align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #FFFFFF"]Mary[/TD]
[TD="bgcolor: #FFFFFF"]A13[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #FFFFFF"]John[/TD]
[TD="bgcolor: #FFFFFF"]A3[/TD]
[TD="bgcolor: #FFFFFF, align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #FFFFFF"]Mary[/TD]
[TD="bgcolor: #FFFFFF"]A15[/TD]
[TD="bgcolor: #FFFFFF, align: right"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #FFFFFF"]John[/TD]
[TD="bgcolor: #FFFFFF"]A16[/TD]
[TD="bgcolor: #FFFFFF, align: right"]40[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: #FFFFFF"]Jim[/TD]
[TD="bgcolor: #FFFFFF"]A2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: #FFFFFF"]Jim[/TD]
[TD="bgcolor: #FFFFFF"]A18[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="bgcolor: #FFFFFF"]Mary[/TD]
[TD="bgcolor: #FFFFFF"]A13[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="bgcolor: #FFFFFF"]Julie[/TD]
[TD="bgcolor: #FFFFFF"]A20[/TD]
[TD="bgcolor: #FFFFFF, align: right"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]H2[/TH]
[TD="align: left"]=SUM(H5:H21)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]I5[/TH]
[TD="align: left"]=G5[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]I6[/TH]
[TD="align: left"]=IF(G6="","",I5&", "&G6)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G2[/TH]
[TD="align: left"]{=INDEX($I$4:$I$21,MIN(IF($I$4:$I$21="",ROW($I$4:$I$21)-ROW($I$4))))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]G5[/TH]
[TD="align: left"]{=IF(G4="","",IFERROR(INDEX($B$2:$B$21,SMALL(IF(($A$2:$A$21=$F$2)*ISERROR(MATCH($B$2:$B$21,$G$4:$G4,0)),ROW($B$2:$B$21)-ROW($B$2)+1),1)),""))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]H5[/TH]
[TD="align: left"]{=IFERROR(INDEX($C$2:$C$21,MATCH($F$2&G5,$A$2:$A$21&$B$2:$B$21,0)),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Although I think Sergio's VBA solution would be much easier to maintain.

 

[douglasjoh]

Thank you Sergio & Eric.

I would like to understand VBA before I start using it but its definitely something I will be building onto.

In the meantime I have applied this formula and it all seems to work except the Contract formula. Would the formula be affected if I am looking at data in another sheet in the workbook? The range I am currently using is 29:1662. Also one more question why does it look at the header in the contracts formula?

Thanks again,

Douglas

 

[Eric W]

Hi Douglas,

Do you mean that the G2 or the G5 formula isn't working? It should work fine if you look at another sheet, just use Sheet9!$B$29:$B$1662 for the references.

The reason I reference the header in the G5 formula is this: Array formulas can be very computationally demanding. If your range is A29:A1662, that's 1632 cells it looks at, plus any other ranges in the formula. And if you drag it down 100 rows, that's 163200 cells being checked. Those cells will be checked even if the result is an empty cell. That potentially can really slow down your sheet. So ideally, we need a way to prevent all those calculations from being done if we don't have to. There are a few ways to do that, but I typically look at the cell above. So the first row looks at the header, the second row looks at the first row, the third row looks at the second row, etc. So if the row above the current row is empty, then we know the current row will also be empty. Thus the IF(G4="","",etc. The IF checks the row above, if it's empty it immediately puts in the null, and the rest of the complicated function is not executed, saving a lot of processing.

Hope this helps.

 

[douglasjoh]

Hi Eric,

It seems that G5 is coming with blank. Ive triple checked the formula and everything is referencing what it should be. Do you think It could be possible for me to send you a copy to see if you can see where I went wrong?

Cheers,

Douglas

 

[Eric W]

Make sure you have a heading in G4. I use a trick in the formula to make sure I don't calculate any more than I need to. The formula looks at the cell above it to make sure it needs to do the calculation. After all the matches are found, an empty cell tells the formulas lower down to immediately put in a space, and avoid the lengthy array calculations. But that trick requires a header at the top of the column so the first match is calculated.

 

[douglasjoh]

I just copied it exactly the same as your example with the headers exactly the same.

Not sure where I have gone wrong.