Formula Countif Index Match

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Vicki - 05 Oct 2007 20:04 GMT

Hello,

I'm using Excel 2003 and this is my first post to the discussion group. I'm
trying to help a coworker and don't have a lot of experience working with
formulas.

I've searched the discussion groups, etc. and have tried several different
formulas with partial success. I'm not even sure at this point if I'm going
about this the right way.

Scenario: Sheet MnthPlotters contains a master list of plotters (by name) in
Column A. The other sheets in the workbook are named by month. A query is run
each month in Access to get a list of the plotters (by name) that were used.
The list is copied and pasted into the sheet for that month in Column A.

The question I'm trying to solve is whether there is a way to flag/highlight
any new plotter names in ColA on the monthly sheets that need to be added to
the master list of plotters.

Test 1: In ColA on the monthly sheet I made up a plotter name in A2 and
tried the following formula in B2 and copied it down.

=IF(COUNTIF(MnthPlotters!A:A,A2)=0,"Add
Plotter",INDEX(MnthPlotters!A:A,MATCH(October!A2,MnthPlotters!B:B,0)))

Results: B2 said Add Plotter which is correct. However, some of the other
cells in ColB are incorrect because they also said Add Plotter and/or #N/A
and they're in the master list. Not sure if this happens because of
nulls/spaces.

Question: What do I need to adjust in the formula to make this work?

I also tried using a Rept Countif Index Match formula but it had too few
arguments. I gave up on trying to fix it and deleted it.

There must be a better way to do this and I'm open to any suggestions.

Thanks so much for any help you can give me!

Reply to this Message

Peo Sjoblom - 05 Oct 2007 21:23 GMT

What is a plotter in this regard, a type of printer?
Usually when there is a mismatch there is either different formats
like if a number is imported as text and when matched it will return N/A
because the matching list are numerical numbers. Or as you state there are
hidden characters like trailing and leading spaces.

INDEX(MnthPlotters!A:A,MATCH(October!A2,MnthPlotters!B:B,0)))

you could try by changing the above to

INDEX(MnthPlotters!A:A,MATCH(TRIM(October!A2),MnthPlotters!B:B,0)))

and also this part

COUNTIF(MnthPlotters!A:A,"*A2*")=0

just to test,. Btw, I would probably write the formula like this

=IF(ISNUMBER(MATCH(TRIM(October!A2),MnthPlotters!B:B,0)),INDEX(MnthPlotters!A:A,MATCH(TRIM(October!A2),MnthPlotters!B:B,0)),"add
plotter")

Also while you are at it, find a match that is returned as a mismatch and
compare the cells like

=LEN(lookup_cell)

=LEN(cell_that_looks_like_a_match)

if you get a different number in the 2 cells that you expected to be equal
then the one with largest number has extra characters, then select that cell
and press F2, is there a trailing space then select it and copy it to a new
cell then use this

=CODE(new_cell)

if it is 32 then there is a space, if it is something else post back

HTH

Signature

Regards,

Peo Sjoblom

> Hello,
>
[quoted text clipped - 41 lines]
>
> Thanks so much for any help you can give me!

Reply to this Message

Vicki - 08 Oct 2007 15:55 GMT

Hi Peo,

Thanks so much for responding. When I adjusted my formula as you suggested I
received "The formula you typed contains an error" message.

The formula that I ended up using and seems to be working is:

=IF(COUNTIF(MnthPlotters!A:A,A3)=0,"Add Plotter","")

Thanks again and have a great day!

> What is a plotter in this regard, a type of printer?
> Usually when there is a mismatch there is either different formats
[quoted text clipped - 80 lines]
> >
> > Thanks so much for any help you can give me!

Reply to this Message