Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Tricky comparing question
I have my list of Criteria in Column A - (In this case the Criteria are names)
In column B, C, D, and so on are days of the month. I paste the raw data underneath the spreadsheet in the coresponding columns. So, day 1 is under column B. Day 2 under column C and so on. I have formulas setup in the spreadsheet to count every time it sees the name in the data for each day. Those formulas work great, so I dont want to change them. Here is a small example of the layout. A B C D E Day 1 Day 2 Day 3 Day 4 John 1 0 1 1 Mark 2 0 0 0 Steve 0 3 1 0 Dan 0 0 1 1 John Steve John John <--This data is pasted from a text file Mark Steve Dan Dan Mark Steve Steve Jason Notice that in column E, the name Jason is in the data, but not anywhere in column A. So there are 3 names in the data list, but only 2 are counted. So I would then have to insert a new row for Jason. What I would like to do is to somehow compare the names after I paste the data in, and if there is a name that is not in column A, then it would point that out - maybe changing the text color to red, or returning a value into another cell. Thank you in advance for any ideas. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Tricky comparing question
Corben, You could use conditional formatting. Select the first cell you want to see if is in your list of criteria (in my example B5). Go to FormatConditional formatting. Change the Cell value is option to Formula is. Enter this formula. =COUNTIF($A:$A,B5)=0 Click on Format, Font and change the text color option to Red. Click ok and ok. Use the format painter to apply this to all other cells where you will be pasting in the data. This is where column A is not one of the columns where you will be pasting the data. HTH Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=535094 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Tricky comparing question
Use conditional formatting.
Select all of the imported data, let's assume it starts at A40 FormatConditional Formatting Change Condition 1 to Formula Is Enter the formula =ISNUMBER(MATCH(A40,$A$2:$A$20,0)) Click format Select the pattern tab Choose a colour OK out That should work, change the cell references to suit. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Corben" wrote in message ... I have my list of Criteria in Column A - (In this case the Criteria are names) In column B, C, D, and so on are days of the month. I paste the raw data underneath the spreadsheet in the coresponding columns. So, day 1 is under column B. Day 2 under column C and so on. I have formulas setup in the spreadsheet to count every time it sees the name in the data for each day. Those formulas work great, so I dont want to change them. Here is a small example of the layout. A B C D E Day 1 Day 2 Day 3 Day 4 John 1 0 1 1 Mark 2 0 0 0 Steve 0 3 1 0 Dan 0 0 1 1 John Steve John John <--This data is pasted from a text file Mark Steve Dan Dan Mark Steve Steve Jason Notice that in column E, the name Jason is in the data, but not anywhere in column A. So there are 3 names in the data list, but only 2 are counted. So I would then have to insert a new row for Jason. What I would like to do is to somehow compare the names after I paste the data in, and if there is a name that is not in column A, then it would point that out - maybe changing the text color to red, or returning a value into another cell. Thank you in advance for any ideas. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Tricky comparing question
Thanks,
I tried this in a few of the cells - Cell range for Day 1 is B250-B1000 so I tried in B251, B252, B253 and it worked, but I'm not sure what you meant by applying it to all other cells. "SteveG" wrote: Corben, You could use conditional formatting. Select the first cell you want to see if is in your list of criteria (in my example B5). Go to FormatConditional formatting. Change the Cell value is option to Formula is. Enter this formula. =COUNTIF($A:$A,B5)=0 Click on Format, Font and change the text color option to Red. Click ok and ok. Use the format painter to apply this to all other cells where you will be pasting in the data. This is where column A is not one of the columns where you will be pasting the data. HTH Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=535094 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
question about tricky autofill for series | Excel Discussion (Misc queries) | |||
Pivot table question | Excel Discussion (Misc queries) | |||
tricky data entry question | Excel Discussion (Misc queries) | |||
Tricky Question | Excel Worksheet Functions | |||
rota question - very tricky... | Excel Discussion (Misc queries) |