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

 

[Aladin Akyurek]

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

A moment of inattention, sorry about that...

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

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

 

[agatonsaxx]

Hi Peter,

Thanks for your help. I have got almost everything to work. But now i experience that we have several unique values for different years. So for example i want to know all unique customers in september, but only for 2018 not 2017. Is it possible to include 2 differnet If formulas in the original formula? I have not got it to work.

Easy example:
Year Month Name
2017 September Mr Pink
2018 September John Doe
2018 September John Doe
2018 September Mr Black

The result i would like to get is that for 2018 we have 2 unique customers (John Doe and Mr Black) but it does not include the 2017 customer even though it is a unique customer in September (although 2017).

Hope you understand what i mean.

Best regards
Agatonsaxx

 

[Aladin Akyurek]

[...]Easy example:
Year Month Name
2017 September Mr Pink
2018 September John Doe
2018 September John Doe
2018 September Mr Black

The result i would like to get is that for 2018 we have 2 unique customers (John Doe and Mr Black) but it does not include the 2017 customer even though it is a unique customer in September (although 2017).

[…]

What is the expected count?

 

[agatonsaxx]

Hi Aladin,

Well what do you mean with expected count?

I have a list with over 2000 customers from both 2016, 2017, 2018. I have columns showing what salesperson met the customer and what month and week.
Sometimes we have a customer that meets the same salesperson but for 3 different companies.

What i would like to do then is to register the customer 3 times and show in a report that during week 37 employeX had 13 customers, but only 9 unique customers (since 2 of the customermeetings occured with the same customers).

[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Month[/TD]
[TD]Week[/TD]
[TD]Salesperson[/TD]
[TD]Company[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]January[/TD]
[TD]1[/TD]
[TD]Christian[/TD]
[TD]Company X[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]January[/TD]
[TD]1[/TD]
[TD]Christian[/TD]
[TD]Company Y[/TD]
[/TR]
[TR]
[TD]Mr Black[/TD]
[TD]January[/TD]
[TD]2[/TD]
[TD]Christian[/TD]
[TD]Company X[/TD]
[/TR]
[TR]
[TD]Mr Pink[/TD]
[TD]February[/TD]
[TD]5[/TD]
[TD]Christian[/TD]
[TD]Company X[/TD]
[/TR]
[TR]
[TD]Mr Black[/TD]
[TD]March[/TD]
[TD]8[/TD]
[TD]Christian[/TD]
[TD]Company Z[/TD]
[/TR]
[TR]
[TD]Mr Sweet[/TD]
[TD]March[/TD]
[TD]9[/TD]
[TD]Christian[/TD]
[TD]Company X[/TD]
[/TR]
</tbody>[/TABLE]

From this table i would like to be able to get the following result when:

Reporting Christians total meetings and unique meetings during:
Week 1: 2 total, 1 unique
Month January: 3 total, 2 unique

I have been able to report unique values for a month and a week. But when i try to add another variable (in this case Christian) i do not know how to move forward.

Best regards
Agatonsaxx

 

[agatonsaxx]

Hi again, i will try to be as clear as i can sorry for making this so difficult to explain.

I have a table with the following values:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Year[/TD]
[TD]Month[/TD]
[TD]Week[/TD]
[TD]Customer[/TD]
[TD]Salesperson[/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD]January[/TD]
[TD]1[/TD]
[TD]Mr Black[/TD]
[TD]Christian[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]January[/TD]
[TD]1[/TD]
[TD]Mr Black[/TD]
[TD]Christian[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]January[/TD]
[TD]1[/TD]
[TD]Mr Pink[/TD]
[TD]Christian[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]January[/TD]
[TD]1[/TD]
[TD]Mr White[/TD]
[TD]Christian[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]January[/TD]
[TD]1[/TD]
[TD]Mr Purple[/TD]
[TD]Christian[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]January[/TD]
[TD]1[/TD]
[TD]Mr Red[/TD]
[TD]Christian[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]January[/TD]
[TD]1[/TD]
[TD]Mr Blue[/TD]
[TD]Christian[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]January[/TD]
[TD]1[/TD]
[TD]Mr Green[/TD]
[TD]Christian[/TD]
[/TR]
[TR]
[TD]2018[/TD]
[TD]January[/TD]
[TD]1[/TD]
[TD]Mr Black[/TD]
[TD]Christian[/TD]
[/TR]
</tbody>[/TABLE]

In this case we have a total of 9 customers in January (1 in 2017, 8 in 2018).

We also have a duplicate customer (Mr Black), which means that in my report i would like it to reflect:

Meetings for Christian during Week 1 in January: 8 total customers, 7 unique customers

The issue i have been experiencing is not to find unique values (you have already helped me with that issue). The problem i have now is that we have several variables (Year, Month, Salesperson) or (Year, Week, Salesperson). Before we only sorted the values based on a Month or a Week and then looked for the unique customer names. Now i would like to include the variable Year and also variable Salesperson.

Is this even possible?

Thanks in advance,

Best regards
Agatonsaxx

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Year	Month	Week	Customer	Salesperson		SP	YEAR	MONTH	WEEK	# records	# customers
2	2017	January	1	Mr Black	Christian		christian	2018	january	1	8	7
3	2018	January	1	Mr Black	Christian
4	2018	January	1	Mr Pink	Christian
5	2018	January	1	Mr White	Christian
6	2018	January	1	Mr Purple	Christian
7	2018	January	1	Mr Red	Christian
8	2018	January	1	Mr Blue	Christian
9	2018	January	1	Mr Green	Christian
10	2018	January	1	Mr Black	Christian
Sheet2

In K2 just enter:

=COUNTIFS($A$2:$A$10,H2,$B$2:$B$10,I2,$C$2:$C$10,1,$E$2:$E$10,G2)

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

=SUM(IF(FREQUENCY(IF(($D$2:$D$10<>"")*($A$2:$A$10=H2)*($B$2:$B$10=I2)*($C$2:$C$10=J2)*($E$2:$E$10=G2),MATCH($D$2:$D$10,$D$2:$D$10,0)),ROW($A$2:$E$10)-ROW(INDEX($A$2:$E$10,1,1))+1),1))

 

[agatonsaxx]

Thank you Aladin! This solved my issue and was clearly explained!

Thank both Peter and Aladin for your help with this. I really appreciate it!

Best regards
Agatonsaxx

 

[Aladin Akyurek]

You are welcome.

 

[agatonsaxx]

Hi again Aladin.

I have found another issue related to this post. You might be able to help me with this one (since both you and Peter have been very helpful in the past).

My issue is as follows:
I have a list of customers that all have got different offerings from me. I need to know the unique values per customer depending on a status. But what i have been experiencing now, is that it will always fetch the value from the cell that is "first" in the list, no matter if the status is different.

Example:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Month[/TD]
[TD]Offering[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]September[/TD]
[TD]10 000[/TD]
[TD]Not active[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]September[/TD]
[TD]12 000[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]Mr Black[/TD]
[TD]September[/TD]
[TD]15 000[/TD]
[TD]Active[/TD]
[/TR]
</tbody>[/TABLE]

What i would like to be able to do. Is to have a formula that looks inside the table and are able to find out that in September we have active offerings on the market for 27 000 (and not 25 000). Right now, my formula will look at the unique values on customer and then return the first value for John Doe (even though he is inactive). I have used your formula to try and get this to work.

Do you have any suggestions on how to solve this?

Best regards
Agatonsaxx

 

[Aladin Akyurek]

@ agatonsaxxI have found another issue related to this post. You might be able to help me with this one (since both you and Peter have been very helpful in the past).

[…]

Example:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Month[/TD]
[TD]Offering[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]September[/TD]
[TD]10 000[/TD]
[TD]Not active[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]September[/TD]
[TD]12 000[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]Mr Black[/TD]
[TD]September[/TD]
[TD]15 000[/TD]
[TD]Active[/TD]
[/TR]
</tbody>[/TABLE]

[…]

Care to state this new question in terms of the example data you posted? If you would, what is the expected result?