countif

sara100

New member
Joined
May 30, 2013
Messages
8
Reaction score
0
Points
0
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
 
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
 
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.
 
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 15sheet April30
13660393791366388567
13660378951366377757
13660370401366293217
13660276261366296217
13660338101366303080
13660404801366295504
13660397921366295366
13660298201366215442
13656861841366136053
13656841991366217796
13656012661366143920
13656085431366143791
13655992561366208735
13656021921366121798
13654502631366051915
13655289881366131912
13654468151366122864
13655119601366108854
13655105041366118188
13655118201366039379
13655156881366118562
13654278041366115070
13654287111365513167
13651852971366398067
13651739621366734356
13651752231366379378
13651641031366397654
1365107226
 
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.....
 

Attachments

  • Sample3.xlsx
    11.9 KB · Views: 25
Back
Top