Lookup unique values from two different columns

[agatonsaxx]

Hi everyone,

I would like to be able to summarize unique values from two different columns. Here is my example:

Column A (Month) | Column B (Name)
January John Doe
January John Doe
January Clark Kent
February Clark Kent
February Mr Black
April Mr Black
April Mr Black

What i would like to be able to do, is to write a formula that can look inside the two columns and sort out the unique values of the name based on in which month the name occured.

In this case i would like it to show:

Unique customers in january: 2 (John Doe + Clark Kent)
Unique customers in februari 2 (Clark Kent + Mr Black)
Unique customers in April 1 (Mr Black)

I have the following formula that only looks in the Name column and gives me the correct value, but it looks on all the values, instead of including the month criteria.

[FONT=&quot]=SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""), IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""))>0,1))

[/FONT]Can somebody please help me with this or say if it is possible in any way?

Best regards
AgatonSaxx

 

[Peter_SSs]

Like this, copied across and down?

Excel Workbook
	A	B	C	D	E
1
2	January	John Doe		January	John Doe
3	January	John Doe		January	Clark Kent
4	January	Clark Kent		February	Clark Kent
5	February	Clark Kent		February	Mr Black
6	February	Mr Black		April	Mr Black
7	April	Mr Black
8	April	Mr Black
Unique 2 Cols

 

[agatonsaxx]

Hi Peter, thanks for your answer.

I do not really get this to work. I don't fully understand what you have done.

Cell D and E, is that where you actually put the formula? Because when i try to insert the formula, it only returns a blank value.

What i would like to return is something like:

Unique customers in January: 2
Unique customers in February: 1
etc.

Do you understand how i mean?

Thanks in advance,

Best regards
AgatonSaxx

Like this, copied across and down?

Unique 2 Cols

	A	B	C	D	E
January	John Doe		January	John Doe
January	John Doe		January	Clark Kent
January	Clark Kent		February	Clark Kent
February	Clark Kent		February	Mr Black
February	Mr Black		April	Mr Black
April	Mr Black
April	Mr Black

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:88px;"><col style="width:80px;"><col style="width:17px;"><col style="width:103px;"><col style="width:109px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula D2 =IFERROR(INDEX(A$2:A$8,AGGREGATE(15,6,(ROW(A$2:A$8)-ROW(A$2)+1)/(ROW(A$2:A$8)-ROW(A$2)+1=MATCH($A$2:$A$8&"|"&$B$2:$B$8,INDEX($A$2:$A$8&"|"&$B$2:$B$8,0),0)),ROWS(D$2:D2))),"") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F	G
1				January	February	April
2	January	John Doe		John Doe	Clark Kent	Mr Black
3	January	John Doe		Clark Kent	Mr Black
4	January	Clark Kent
5	February	Clark Kent
6	February	Mr Black
7	April	Mr Black
8	April	Mr Black
Sheet1

In D1 control+shift+enter, not just enter, copy across.

=IFERROR(INDEX($A$2:$A$8,SMALL(IF(FREQUENCY(IF($A$2:$A$8<>"",MATCH($A$2:$A$8,$A$2:$A$8,0)),ROW($A$2:$B$8)-ROW(INDEX($A$2:$B$8,1,1))+1),ROW($A$2:$B$8)-ROW(INDEX($A$2:$B$8,1,1))+1),COLUMNS($D$1:D1))),"")

In D2 control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX($B$2:$B$8,SMALL(IF(FREQUENCY(IF($B$2:$B$8<>"",IF($A$2:$A$8=D$1,MATCH($B$2:$B$8,$B$2:$B$8,0))),ROW($A$2:$B$8)-ROW(INDEX($A$2:$B$8,1,1))+1),ROW($A$2:$B$8)-ROW(INDEX($A$2:$B$8,1,1))+1),ROWS(D$2:D2))),"")

If interested only in counts...

In D2 control+shift+enter, not just enter, and copy across:

=IF(D$1="","",SUM(IF(FREQUENCY(IF($B$2:$B$8<>"",IF($A$2:$A$8=D$1,MATCH($B$2:$B$8,$B$2:$B$8,0))),ROW($A$2:$B$8)-ROW(INDEX($A$2:$B$8,1,1))+1),1)))

 

[sandy666]

You can try PivotTable with DataModel


[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]Month[/td][td]Name[/td][td][/td][td=bgcolor:#DDEBF7]Month[/td][td=bgcolor:#DDEBF7]Name[/td][/tr]

[tr=bgcolor:#FFFFFF][td]January[/td][td]John Doe[/td][td][/td][td]April[/td][td]Mr Black[/td][/tr]

[tr=bgcolor:#FFFFFF][td]January[/td][td]John Doe[/td][td][/td][td]February[/td][td]Clark Kent[/td][/tr]

[tr=bgcolor:#FFFFFF][td]January[/td][td]Clark Kent[/td][td][/td][td][/td][td]Mr Black[/td][/tr]

[tr=bgcolor:#FFFFFF][td]February[/td][td]Clark Kent[/td][td][/td][td]January[/td][td]Clark Kent[/td][/tr]

[tr=bgcolor:#FFFFFF][td]February[/td][td]Mr Black[/td][td][/td][td][/td][td]John Doe[/td][/tr]

[tr=bgcolor:#FFFFFF][td]April[/td][td]Mr Black[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]April[/td][td]Mr Black[/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[Peter_SSs]

What i would like to return is something like:

Unique customers in January: 2
Unique customers in February: 1
etc.

Sorry, I misinterpreted your original request. Try these formulas, copied down. The E2 formula is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Excel Workbook
	A	B	C	D	E
1	Month	Name		Month	Count
2	January	John Doe		January	2
3	January	John Doe		February	2
4	January	Clark Kent		April	1
5	February	Clark Kent
6	February	Mr Black
7	April	Mr Black
8	April	Mr Black
Unique 2 Cols

 

[agatonsaxx]

Hi Peter thanks for your answer. From what i can see, this will solve my issue!

I have another question on the same topic. This is a bit more complex i think.

Scenario:

We have a customer that has recieved 3 different offerings (quotes) from us. We want to keep track of this in our salessystem but we do not want the offering (values) to be trippled.

Example:

Month | Name | Value
January | John Doe | 1000
January | John Doe | 2000
January | Clark Kent | 3000
February | Clark Kent | 2000

What we would like this to reflect is:
Possible sales in january: 4000 (not 6000)
Possible sales in february: 2000 (even though Clark Kent got an offer in january)

Do you have any suggestions on how this would be possible?

Thanks in advance,

Best regards
Agatonsaxx

 

[Aladin Akyurek]

Hi Peter thanks for your answer. From what i can see, this will solve my issue!

What I offered solves your issue too.

I have another question on the same topic. This is a bit more complex i think.

Scenario:

We have a customer that has recieved 3 different offerings (quotes) from us. We want to keep track of this in our salessystem but we do not want the offering (values) to be trippled.

Example:

Month | Name | Value
January | John Doe | 1000
January | John Doe | 2000
January | Clark Kent | 3000
February | Clark Kent | 2000

What we would like this to reflect is:
Possible sales in january: 4000 (not 6000)
Possible sales in february: 2000 (even though Clark Kent got an offer in january)

Do you have any suggestions on how this would be possible?

Thanks in advance,

Best regards
Agatonsaxx

Book1
	A	B	C	D	E	F
1	Month	Name	Value
2	January	John Doe	1000		January	4000
3	January	John Doe	2000		February	3000
4	January	Clark Kent	3000
5	February	Clark Kent	2000
6
Sheet2

In E2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$2:$A$5,SMALL(IF(FREQUENCY(IF($A$2:$A$5<>"",MATCH($A$2:$A$5,$A$2:$A$5,0)),ROW($A$2:$C$5)-ROW(INDEX($A$2:$C$5,1,1))+1),ROW($A$2:$C$5)-ROW(INDEX($A$2:$C$5,1,1))+1),ROWS($E$2:E2))),"")

In F2 control+shift+enter, not just enter, and copy down:

=IF($E2="","",SUM(IF(FREQUENCY(IF($A$2:$A$5=$E2,MATCH($B$2:$B$5,$B$2:$B$5,0)),ROW($A$2:$C$5)-ROW(INDEX($A$2:$C$5,1,1))+1),$C$2:$C$5)))

 

[agatonsaxx]

What I offered solves your issue too.

	A	B	C	D	E	F
Month	Name	Value
January	John Doe	January
January	John Doe	February
January	Clark Kent
February	Clark Kent

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

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

[TD="align: center"]2[/TD]

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

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

[TD="align: center"]3[/TD]

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

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

[TD="align: center"]4[/TD]

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

[TD="align: center"]5[/TD]

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

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

[TD="align: center"]6[/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>

Sheet2​

In E2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$2:$A$5,SMALL(IF(FREQUENCY(IF($A$2:$A$5<>"",MATCH($A$2:$A$5,$A$2:$A$5,0)),ROW($A$2:$C$5)-ROW(INDEX($A$2:$C$5,1,1))+1),ROW($A$2:$C$5)-ROW(INDEX($A$2:$C$5,1,1))+1),ROWS($E$2:E2))),"")

In F2 control+shift+enter, not just enter, and copy down:

=IF($E2="","",SUM(IF(FREQUENCY(IF($A$2:$A$5=$E2,MATCH($B$2:$B$5,$B$2:$B$5,0)),ROW($A$2:$C$5)-ROW(INDEX($A$2:$C$5,1,1))+1),$C$2:$C$5)))

Hi Peter,

It looks like it works for january, but it gets summarized wrong in february (it says 3000 instead of 2000), do you know why?

Best regards
Agatonsaxx

 

[Peter_SSs]

Hi Peter thanks for your answer. From what i can see, this will solve my issue!

You are welcome.

Hi Peter,

It looks like it works for january, ..

I think you meant "Hi Aladin"

Also, best not to fully quote long posts as it makes the thread harder to read/navigate. If you want to quote, quote small, relevant parts only.

See if this does what you want. If not, please explain why in your example in post 7, the expected value for January is 4000 (1000 for John Doe + 3000 for Clark Kent) and not 5000 (2000 for John Doe + 3000 for Clark Kent)

Excel Workbook
	A	B	C	D	E	F
1	Month	Name	Value		Month	Value
2	January	John Doe	1000		January	4000
3	January	John Doe	2000		February	2000
4	January	Clark Kent	3000
5	February	Clark Kent	2000
Unique 2 Cols (2)