View Duplicate Rows in Table Return Value "True" (Not true when Filtered)

[Legacy 183598]

Hello.

I cannot get the photo to link correctly: please refer to the photo as a reference. Thanks!

http://s24.photobucket.com/albums/c47/johnnytoscani/?action=view&current=CountIf.jpg

I wanted to see if the data I entered contained any duplicate information for a specific column ("ID") in a table I created and to return a value of "True" when the row had duplicate data

I used a COUNTIF Function and it worked. However, after doing this, I applied filters to some of the columns and the COUNTIF values that were displayed were no longer valid because they were including the hidden rows/cells. I watched a few of the videos on here and some said to use "SUMPRODUCT" and others said to use "SUBTOTAL." I still am not sure what approach to take.

Using the above photo, let's say I wanted to filter the results only by "Procedure Description." How can I get the "True" to become "False" when filtering is applied?

Thank you in advance for your help!

 

[T. Valko]

Hello.

I cannot get the photo to link correctly: please refer to the photo as a reference. Thanks!

http://s24.photobucket.com/albums/c47/johnnytoscani/?action=view&current=CountIf.jpg

I wanted to see if the data I entered contained any duplicate information for a specific column ("ID") in a table I created and to return a value of "True" when the row had duplicate data

I used a COUNTIF Function and it worked. However, after doing this, I applied filters to some of the columns and the COUNTIF values that were displayed were no longer valid because they were including the hidden rows/cells. I watched a few of the videos on here and some said to use "SUMPRODUCT" and others said to use "SUBTOTAL." I still am not sure what approach to take.

Using the above photo, let's say I wanted to filter the results only by "Procedure Description." How can I get the "True" to become "False" when filtering is applied?

Thank you in advance for your help!

Try this...

Book1
	A	B
1	TRUE	_
2	_	_
3	Region	ID
4	East	6
5	North	4
6	South	1
7	South	10
8	East	5
9	South	7
10	South	2
11	North	8
12	West	8
13	East	10
14	North	2
15	East	4
16	South	2
17	East	2
18	East	1
19	North	8
20	West	6
Sheet1

Array formula** entered in A1:

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(B4,ROW(B4:B20)-ROW(B4),0,1)),MATCH(B4:B20,B4:B20,0)),ROW(B4:B20)-ROW(B4)+1)>1,1))>0

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Now, if you were to filter column A on East then the formula will return FALSE as there are no duplicates in the filtered rows of column B.

 

[Legacy 183598]

Hi, and thank you for your help! This formula works for the entire worksheet but how can I get it to work at the end of each row like in my photo?

Given your example, let's say I wanted to add your formula at the end of each row starting in cell C4, C5, et al.

EG:
Cell C4 I think would be:
{=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(B4,ROW(B4:B20)-ROW(B4),0,1)),MATCH(B4:B20,B4:B20,0)),ROW(B4:B20)-ROW(B4)+1)>1,1))>0}

What should cell C5 read?
Cell C5:
{=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(B4,ROW(B4:B20)-ROW(B4),0,1)),MATCH(B4:B20,B4:B20,0)),ROW(B4:B20)-ROW(B4)+1)>1,1))>0}

Currently, when I autofill the rest of the rows, the cell references escalate sequentially (and I don't think this is correct) like so:

{=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(B5,ROW(B5:B21)-ROW(B5),0,1)),MATCH(B5:B21,B5:B21,0)),ROW(B5:B21)-ROW(B5)+1)>1,1))>0}

I'm guessing I need to use dollar signs or something to tell it to do this and will this require an array or not?

Thanks again!

 

[T. Valko]

Hi, and thank you for your help! This formula works for the entire worksheet but how can I get it to work at the end of each row like in my photo?

Given your example, let's say I wanted to add your formula at the end of each row starting in cell C4, C5, et al.

EG:
Cell C4 I think would be:
{=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(B4,ROW(B4:B20)-ROW(B4),0,1)),MATCH(B4:B20,B4:B20,0)),ROW(B4:B20)-ROW(B4)+1)>1,1))>0}

What should cell C5 read?
Cell C5:
{=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(B4,ROW(B4:B20)-ROW(B4),0,1)),MATCH(B4:B20,B4:B20,0)),ROW(B4:B20)-ROW(B4)+1)>1,1))>0}

Currently, when I autofill the rest of the rows, the cell references escalate sequentially (and I don't think this is correct) like so:

{=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(B5,ROW(B5:B21)-ROW(B5),0,1)),MATCH(B5:B21,B5:B21,0)),ROW(B5:B21)-ROW(B5)+1)>1,1))>0}

I'm guessing I need to use dollar signs or something to tell it to do this and will this require an array or not?

Thanks again!

Maybe this...

Book1
	A	B	C
3	Region	ID	Dupes
4	East	6	TRUE
5	North	4	TRUE
6	South	1	TRUE
7	South	10	TRUE
8	East	5	FALSE
9	South	7	FALSE
10	South	2	TRUE
11	North	8	TRUE
12	West	8	TRUE
13	East	10	TRUE
14	North	2	TRUE
15	East	4	TRUE
16	South	2	TRUE
17	East	2	TRUE
18	East	1	TRUE
19	North	8	TRUE
20	West	6	TRUE
Sheet1

Formula entered in C4 and copied down:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B$4,ROW(B$4:B$20)-ROW(B$4),0,1)),--(B$4:B$20=B4))>1

If you filter on East this will be the result:

Book1
	A	B	C
3	Region	ID	Dupes
4	East	6	FALSE
8	East	5	FALSE
13	East	10	FALSE
15	East	4	FALSE
17	East	2	FALSE
18	East	1	FALSE
Sheet1

If you filter on North this will be the result:

Book1
	A	B	C
3	Region	ID	Dupes
5	North	4	FALSE
11	North	8	TRUE
14	North	2	FALSE
19	North	8	TRUE
Sheet1

 

[Legacy 183598]

Works perfectly! Thank you for help!

 

[T. Valko]

Works perfectly! Thank you for help!

You're welcome. Thanks for the feedback!