Formula to Identify Duplicates and Get Count

[legalhustler]

Hello,

I need an Excel formula to find duplicates based on two columns (Region/Station) and return the count in the last occurrence. For example, there two duplicates of North/40 so the last occurrence shows 2, likewise there is 4 duplicates for East/20 so the result in the last occurrence shows 4. The ones that are unique should be empty/blank. Please help.

Region	Station	Result
East	20
North	40
South	50
East	20
North	40	2
East	20
East	20	4

 

[legalhustler]

Small correction, the ones that are unique should return a 1 not empty/blank. So in the above table, only South/50 will have a 1 in the result column.

 

[Jeffrey Mahoney]

How about this? It doesn't return the row, it returns the number in the set.

Book4
	A	B	C
1	Region	Station	Result
2	East	20	0
3	North	40	0
4	South	50	0
5	East	20	1
6	North	40	2
7	East	20	4
8	East	20	6
Sheet4

Cell Formulas
Range	Formula
C2:C8	C2	=MAX((($A$1:A1)&($B$1:B1)=(A2&B2))*(ROW($A$1:A1))-1,0)

 

[Joe4]

Here is how I did it:

Formula in cell C2 and copy down:

=LET(x,COUNTIFS(A:A,A2,B:B,B2),IF(AND(x>1,x=COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2)),x,""))

 

[Jeffrey Mahoney]

Small correction, the ones that are unique should return a 1 not empty/blank.

Then how do you know that it doesn't match the first row?

 

[Joe4]

Small correction, the ones that are unique should return a 1 not empty/blank.

In that case:

Formula in C2:

=LET(x,COUNTIFS(A:A,A2,B:B,B2),IF(x=COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2),x,""))

 

[Jeffrey Mahoney]

Joe, shouldn't the result in row 5 be 1 also, given that row 2 is identical?

 

[Joe4]

Joe, shouldn't the result in row 5 be 1 also, given that row 2 is identical?

No, only the last one.

Basically, it sounds like what they are saying is that for each unique combination (East/20, North/40, South/50), the total count should only appear on the line with the last entry for that group (regardless of how many times it occurs).
Since there are only 3 unique combinations in that example, there should only be 3 numbers in column C.

Even without the amendment to the original question, there are problems with your first response.
How did you get 6 for the last row?

 

[legalhustler]

In that case:

View attachment 112530

Formula in C2:

=LET(x,COUNTIFS(A:A,A2,B:B,B2),IF(x=COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2),x,""))

Works well for a regular data set like the above, but I tried to use it without the LET function (for ease of reading and evaluation) for a Excel table but it gives me the count for each row instead for the last occurrence. What am I doing wrong? Is my absolute table reference structure correct?

=IF(COUNTIFS(Table1[Region],Table1[@Region],Table1[Station],Table1[@Station])=COUNTIFS(Table1[[Region]:[Region]],Table1[@Region],Table1[[Station]:[Station]],Table1[@Station]),COUNTIFS(Table1[Region],Table1[@Region],Table1[Station],Table1[@Station]),"")

 

[Cubist]

Modifying Joe4 's formula, try:

Book1
	A	B	C
1	Region	Station	Result
2	East	20
3	North	40
4	South	50	1
5	East	20
6	North	40	2
7	East	20
8	East	20	4
Sheet5

Cell Formulas
Range	Formula
C2:C8	C2	=LET(x,COUNTIFS([Region],[@Region],[Station],[@Station]), IF(x=COUNTIFS(INDEX([Region],1):[@Region],[@Region],INDEX([Station],1):[@Station],[@Station]),x,""))