Identify top 20 clients/coditional formatting

[Guest]

I have a spread sheet that has 1,500 rows and 27 colums. Starting at 9P are
client names and I am trying to indentify specific clients by name only, no
formulas or sums. How can I identify the 20 clients by name?

 

[Steve G]

I have a spread sheet that has 1,500 rows and 27 colums. Starting at 9P are
client names and I am trying to indentify specific clients by name only, no
formulas or sums. How can I identify the 20 clients by name?

Use advance filtering. Make sure you have a row with the titles that
matches your spreadsheet. The row should be on the top. Enter the 20
clients names using the OR operand. Filter in place. Copy the
results to another worksheet.

Steve G

 

[Guest]

Sorry but I am confused. The "clients" are in colum P and the 20 that I am am
trying to find may or may not be in the 1500 rows in colum p. Will that still
work?

 

[Guest]

Here's a way to conditionally format it ..

Assume your list of 20 client names are in Sheet2's A1:A20

First, define a named range: SpecialClients
to refer to this list

In Sheet2,
Select A1:A20, then click inside the namebox*, key-in: SpecialClients, press
ENTER
*the box with the dropdown just to left of the formula bar

In Sheet1,
Assume source data in cols A to AA, from row 9 down,
with client names running in P9 down

Select all the 1500 rows from row 9 down
(select the row headers)

Click Format > Conditional Formatting,
Under Condition 1, Formula is:
=ISNUMBER(MATCH($P9,SpecialClients,0))
Click Format > Patterns > Light Brown? > OK
Click OK

The above will format entire rows where the client names in col P matches
those within SpecialClients

 

[Guest]

Thanks a bunch. This works like a charm. Now to take this one step further
can I add to the formula or how do I filter out to see only the ones
identified?

 

[Guest]

In Sheet1,
Just put the same formula into an adjacent empty col, say in AB9:
=ISNUMBER(MATCH($P9,SpecialClients,0))
Copy down. Then autofilter on col AB, select: TRUE