0

I have two columns of numeric values in Excel. This is a task list, column A is the task number, column C shows the number of any tasks that must be done before this task. Both columns are formatted as "General".

I want to highlight cells in column A that are listed as parents in column C. In some cases, a task might have more than one parent so the cell might have more than one number in it:

6, 17, 105

My data spans from rows 6 through 1004 and I've tried the following:

=ISNUMBER(SEARCH($A6,$C6:$C1004))

That works fine in other situations when I'm dealing with text, but not when the data is numbers. At least that's my theory for why it's not working here.

I've tried searching here and a general web search, but all the answers seem to apply to text data not numbers. How can I accomplish this with numeric data?

Edit:

Here's a screenshot of some of the data:

Screenshot

First column is the task number, third is a listing of "parent" task for the given task. For example, looking at task 81 at the bottom, it can't be done until tasks 17, 64 and 84 are done. What I want to do is format any task number that has children. So, in the example I just mentioned, task 84 (near the top) should be colored differently since it has a "child".

salguod
  • 1
  • 2
  • 1
    mind sharing some screenshot/sample data? like : [Example1](https://stackoverflow.com/questions/51462803/formula-to-combine-similar-rows-but-sum-values-from-1-column/51462879) [Example2](https://stackoverflow.com/questions/51439093/excel-formula-how-to-split-string-by-capital-letters/51446616) [Example3](https://stackoverflow.com/questions/51449555/excel-sumifs-checking-if-a-column-contains-text/51451498) – p._phidot_ Jul 23 '18 at 16:46
  • Absolutely, I edited the original to include a screenshot. – salguod Jul 23 '18 at 17:45
  • If the value at C123 is a number, then using `=match($A6,$C$6:$C$1004,0)` will do. but your data is a mix of both.. I don't know how. | Other direction/suggestion : use additional column to separate/convert the C123 like text to numbers, then use match() function. | Sorry, couldn't solve this /[-_-]. – p._phidot_ Jul 23 '18 at 18:12
  • Match worked for all the cells with a single number in them, but not for cells like C123. :-( – salguod Jul 23 '18 at 18:26

1 Answers1

0

Please clear any existing CF from ColumnA, select it and HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true::

=AND(A1<>"",MATCH("*"&A1&"*",C:C,0))

Format..., select choice of formatting, OK.

Assumes all of ColumnC is Text format.

If that does not suit please (a) ensure your text and your data sample are compatible, (b) provide the sample in Text format (not only image) and (c) indicate exactly what you would like highlighted.

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • That didn't work. It highlighted things that shouldn't have been and did not highlight others that should have. Both columns are formatted as "general" however and the numbers are stored as numbers. Thinking more about it, I don't need these as numbers, they are simply labels so I can convert to text. Just need to figure out how. Selecting the column, right clicking and selecting "text" doesn't seem to actually convert the cells with data in them. – salguod Jul 24 '18 at 11:48
  • OK, I used text to columns to convert everything to text and this is now mostly working. However, I'm getting some false positives for 1 and 2 digit numbers that are part of a 2 or 3 digit number. For example, if the cell in column A is 4 or 26 and column C contains 84 or 126 the cell in A is formatted. Checking for that will get much more complicated, assuming it's even possible. – salguod Jul 24 '18 at 12:13