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

 

[agatonsaxx]

Hi Aladin,

What i would like is to find a formula that will be able to distinguish between unique values (customer name) during the same month but return an amount depending on a specific status.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer name[/TD]
[TD]Month[/TD]
[TD]Amount[/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]John Doe[/TD]
[TD]September[/TD]
[TD]11 000[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]Mr Black[/TD]
[TD]September[/TD]
[TD]10 000[/TD]
[TD]Active[/TD]
[/TR]
</tbody>[/TABLE]

Expected result:

Active customers in september: 2 (John Doe and Mr Black)
Active amount in september: 22 000 (John Doe and Mr Black)

The issue i have been experiencing is that when i use the formula for unique values, it will only look for the duplicate name and then return the first value (amount) even though that specific customer is "Not active".

I do not want John Doe (10 000) to be returned in value, since he is Inactive. But i understand that I might have to settle for the two active john does, that it will return the amount of the first john doe (12 000 instead of 11 000).

Do you understand how i mean?

Best regards
Agatonsaxx

 

[Aladin Akyurek]

Shouldn't the amount equal 33 000?

 

[agatonsaxx]

Well no, since we have already said that we want to know the unique values for the customers during a month.

As i understood it, since the formula will look at the name and decide if it is the same, it will fetch the value from the first customer. That is why it will not include the second amount for John Doe.

Best regards
AgatonSaxx

 

[Aladin Akyurek]

Well no, since we have already said that we want to know the unique values for the customers during a month.

As i understood it, since the formula will look at the name and decide if it is the same, it will fetch the value from the first customer. That is why it will not include the second amount for John Doe.

Best regards
AgatonSaxx

Book1
	A	B	C	D	E	F	G	H	I
1	Customer name	Month	Amount	Status		month	status	# distinct	amount distinct
2	John Doe	September	10000	Not active		September	Active	2	22000
3	John Doe	September	12000	Active
4	John Doe	September	11000	Active
5	Mr Black	September	10000	Active
Sheet1

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

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

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

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

 

[agatonsaxx]

Hi Aladin,

Thanks for your reply and help! I will try this, it looks like this will solve my issue!

Thanks again for your help!

Best
Agatonsaxx

 

[Aladin Akyurek]

Hi Aladin,

Thanks for your reply and help! I will try this, it looks like this will solve my issue!

Thanks again for your help!

Best
Agatonsaxx

You are welcome.

 

[agatonsaxx]

Hi again Aladin!

The problems just keeps adding up the further i come in this case. Is there anyway to return a value between two different dates? I have used the following formula but it just says #WRONG (something in swedish).

[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer name[/TD]
[TD]Offeringdate[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]2018-09-05[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]2018-09-05[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]Mr Black[/TD]
[TD]2018-08-05[/TD]
[TD]Active[/TD]
[/TR]
[TR]
[TD]Mr Pink[/TD]
[TD]2018-09-02[/TD]
[TD]Active[/TD]
[/TR]
</tbody>[/TABLE]

=SUM(IF(FREQUENCY(IF((A2:A4<>"")*(C2:C4="Active")*(B2:B4&">="&DATE(2018;9;1))*(B2:B4&"<"&DATE(2018;10;1));MATCH(A2:A4;A2:A4;0));ROW(A2:C4)-ROW(INDEX(A2:C4;1;1))+1);1))

The expected result is the following:
Active customers in the offering period (2018-09-01 to 2018-10-01) = 2 (John Doe and Mr Pink).
Mr Black and John Doe (the second) should not be counted.

I guess i have done something wrong with the dateformula.

Best regards
Agatonsaxx

 

[agatonsaxx]

Hi again Aladin, I solved the issue by myself!

 

[Aladin Akyurek]

I used your data sample in post #21 and now you come up with a different sample in post #27 . Can we keep us on track?

Book1
	A	B	C	D	E
1	Customer name	Offeringdate	Status		2018-09-01
2	John Doe	2018-09-05	Active		2
3	John Doe	2018-09-05	Active
4	Mr Black	2018-08-05	Active
5	Mr Pink	2018-09-02	Active
Sheet2

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

=SUM(IF(FREQUENCY(IF(($A$2:$A$5<>"")*($B$2:$B$5>=E1)*($B$2:$B$5<=EOMONTH(E1,0))*($C$2:$C$5="active"),MATCH($A$2:$A$5,$A$2:$A$5,0)),ROW($A$2:$D$5)-ROW(INDEX($A$2:$D$5,1,1))+1),1))

Replace the comma list separator with the semi-colon that your system needs.

 

[agatonsaxx]

Hi Aladin,

I am sorry for being inconsistent with my questions. The thing is that my document consist of a lot of different columns and rows and different scenarios for how to calculate the different states. So the further i come the more questions arrise. Thank you for your help so far it has been really great!

best regards
AgatonSaxx