countif

[sara100]

Hi everyone,

I need help with countif to find duplicates in the first columns of multiple sheets.
the sheets have names April15/April30/May15 etc.I use excel 2010.

I'm a rookie with formulas so please explain why it works this way.


Thanks

[Greg Glynn]

Are you looking across the sheets to find duplicates in the same cell, or are you looking at each sheet seperately and looking for duplictes in Column A?

Greg

[sara100]

just Column A.

thanks

[NBVC]

Try this.

Go to the first sheet, select column A... then name it (by typing into the Name Box above A1 and left of the Formula bar)... call it ColA1

Go to the next sheet, and repeat calling that column A, ColA2

repeat similarly for each sheet. The key is to use consecutively numbered naming.. (don't skip any numbers).

Then go back to sheet 1, go to Home|Conditional Formatting|New Rule.

Select Use a formula to determine which cells to format.

Enter this formula:

=SUMPRODUCT(COUNTIF(INDIRECT("ColA"&ROW(INDIRECT("1:3"))),A1))>1

Then click Format and choose the preferred colour.

Repeat for each of the sheets.

Only the duplicated values should be highlighted.

[sara100]

NVBC,thank you for your help.I used it but the duplicates value didn't show up.I think I do something wrong or didn't understand how to do it.

Here is the example,the columns are longer than I put here .

sheet April 15			sheet April30
1366039379			1366388567
1366037895			1366377757
1366037040			1366293217
1366027626			1366296217
1366033810			1366303080
1366040480			1366295504
1366039792			1366295366
1366029820			1366215442
1365686184			1366136053
1365684199			1366217796
1365601266			1366143920
1365608543			1366143791
1365599256			1366208735
1365602192			1366121798
1365450263			1366051915
1365528988			1366131912
1365446815			1366122864
1365511960			1366108854
1365510504			1366118188
1365511820			1366039379
1365515688			1366118562
1365427804			1366115070
1365428711			1365513167
1365185297			1366398067
1365173962			1366734356
1365175223			1366379378
1365164103			1366397654
1365107226

[NBVC]

hHeris a sample. Please review it. Notice that it doesn't matter the sheetnames. I named column A of Sheet1, ColA1, and column A of Sheet2 is ColA2.The formula for conditional formatting is then: =SUMPRODUCT(COUNTIF(INDIRECT("ColA"&ROW(INDIRECT("1: 2 "))),A1))>1 Notice that the "1:3" is now "1:2" tbecause you have only 2 sheets in this sample.....

[sara100]

I really appreciate it.

all the best