Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Countif Index Match
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,M nthPlotters!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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Countif Index Match
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,MnthPlotte rs!B:B,0))) you could try by changing the above to INDEX(MnthPlotters!A:A,MATCH(TRIM(October!A2),Mnth Plotters!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!A 2),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 -- Regards, Peo Sjoblom "Vicki" wrote in message ... 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,M nthPlotters!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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Countif Index Match
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! "Peo Sjoblom" wrote: 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,MnthPlotte rs!B:B,0))) you could try by changing the above to INDEX(MnthPlotters!A:A,MATCH(TRIM(October!A2),Mnth Plotters!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!A 2),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 -- Regards, Peo Sjoblom "Vicki" wrote in message ... 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,M nthPlotters!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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
INDEX / MATCH with SUMIF/COUNTIF/SUMPRODUCT Statements? | Excel Worksheet Functions | |||
Countif and Index Match copy and paste | Excel Discussion (Misc queries) | |||
existing unique value formula help (index(match(countif))) | Excel Worksheet Functions | |||
Countif, Index, Match... not sure which to use | Excel Worksheet Functions | |||
need help with Index, Match and Countif in the same complicated formula | Excel Discussion (Misc queries) |