I need a formula please.

[John WEC]

Please does anyone know a formula I can enter into a cell that will check the
11 cells immediately above it (which all have a text entry, i.e., a name, and
it will be just one word, not mutliple words) to see if any name appears in
more than 2 of the cells? I also need to be able to copy the formula to
lots of other cells in the same worksheet. By the way, the 11 cells that
need to be checked have an empty cell immediately above the 11 cells.

I hope you can understand what I'm looking for here.

If you can help, I'd really appreciate it.

Thanks, John C

 

[Rick Rothstein]

Let's assume the cell you want to put the formula in is A11, then put this
array-entered** formula in A11...

=MAX(COUNTIF(A1:A10,A1:A10))

**Commit this formula with Ctrl+Shift+Enter, not just Enter by itself.

Note that all I did is specify the range covering the 10 cells above the
cell the formula is placed in. You can copy this formula anywhere (lower
than Row 10) and it will always refer to the 10 cells above it.

 

[Bernard Liengme]

=IF(SUMPRODUCT(1/COUNTIF(A2:A12,A2:A12&""))<11,"Duplicates","All unique")
best wishes

 

[Chip Pearson]

You can use the following array formula to test whether there are any
duplicates in the range of cells consisting of the 11 rows above D20.

=IF(SUM(1/(COUNTIF(OFFSET(D20,-11,0,11,1),OFFSET(D20,-11,0,11,1))))<>11,"duplicate","no
duplicates")

Change D20 to the cell in which you enter the formula. It will return
"duplicate" if there is one or more duplicate items in the 11 cells
above the formula, or "no duplicates" if there are no duplicates.

Since this is an array formula, you *must* press CTRL SHIFT ENTER
rather than just ENTER when you first enter the formula and whenever
you edit it later. If you do this properly, Excel will display
the formula enclosed in curly braces { }. The formula will not
work properly if it is not entered as an array formula. For
much more information about array formulas, see
http://www.cpearson.com/Excel/ArrayFormulas.aspx.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[John WEC]

That's great Rick. I can't believe the speed of your reply.

One thing. Can I extend the formula so that it gives me a visible warning
if there are 3 or more entries the same? Like format the cell (with the
formula in) Red or something?

Thanks

 

[Rick Rothstein]

The formula I gave you will give you the maximum number of repeats from all
the repeated names in the range. That, in itself, does not answer the exact
question you asked. This array-entered** formula comes closer...

=MAX(COUNTIF(A1:A10,A1:A10))>2

**Commit this formula with Ctrl+Shift+Enter, not just Enter by itself.

The formula reports TRUE if any name appears 3 or more times and FALSE
otherwise; and, like the previous formula, can be copied anywhere.

 

[Rick Rothstein]

Select A11 (the cell with the formula in it) and click this menu item from
Excel's menu bar: Format/Conditional Formatting. On the dialog box that
appears, select "Formula Is" from the first drop down and put this formula
in the empty field that appears next to it...

=MAX(COUNTIF(A1:A10,A1:A10))>2

This is the same formula, but with a test for greater than 2 added to it.
Next, click the Format button and click the Patterns tab, then select the
color you want the cell to be filled in with from the chart of colors
displayed. Finally, OK your way back to the worksheet. That should do it.
You can copy this cell (A11) to other locations and the Conditional Format
you just set up will copy with it.

 

[John WEC]

You're a star Rick. It works perfectly. Just what I wanted.

Thanks a million.

John C

 

[John WEC]

Hi Rick,

I just finished my spreadsheet, and it works a treat. Absolutely
brilliant. I've only been looking for that formula for 2 years!!!

Thank you so much.

John C