0

I have two columns that contain huge lists. My objective is to use conditional formatting to highlight cells in Column A if they are also listed in Column D (duplicate values). I figured that out by using the formula

=VLOOKUP($A1,$D:$D,1,0)=$A1

The complication occurs when the individuals cells in Column A will sometimes have more characters than their Column D duplicates. I still need the cells in Column A to highlight.

For example,

  • D4 shows FA666610F8009. I need it to identify the both duplicates in
  • A3, which shows FA666610F8009, and
  • A5, which shows FA666610F8009RS56.

I have tried diligently to find the right formulaic variation, but I have failed. The below linked excel file is as far as I could get on my own.

Excel worksheet link

Lanzer
  • 15
  • 5
  • How huge is huge? Hundreds? Thousands? Hundreds of Thousands? The permutations might well dictate the approach if the latter. And when you say "more characters", will those additional characters if present be on the end of the string? Or could one string appear anywhere within the other? – jeffreyweir Apr 02 '18 at 13:32
  • Thank you for asking those clarifying questions. Hundreds that will turn into thousands. It's a running list in the Column that I add to as I am given contracts to process. The 4 string addition will be added to the end of the 13 character string. – Lanzer Apr 03 '18 at 22:13
  • Cool. Will there be any duplicates within column D? i.e. say D1000 has the same value as D4? If so, do you want these highlighted as well? And would it help if you had duplicates also outputted somewhere else, so you have a handy list in one place and don't have to filter by color? (I have an answer somewhere on this site that does this, and that can easily be altered to trim the extra characters from Column A. And it's blazingly fast.) – jeffreyweir Apr 03 '18 at 22:18
  • And will everything in column D be 13 characters long? Or does length in D vary? – jeffreyweir Apr 03 '18 at 22:29
  • There may be duplicates, yes. I do not have a handy list. Column D will vary. – Lanzer Apr 04 '18 at 23:34

5 Answers5

1

You can use following formula.

  • Select Range A2:A45 in your file.

  • Apply following formula in conditional formatting to it:

=ISNUMBER(LOOKUP(2^15,SEARCH($D$2:$D$22,A2,1)))

It will match substring and highlight desired child cells.

shrivallabha.redij
  • 5,832
  • 1
  • 12
  • 27
  • This formula highlights only three cells and I'm unsure why. When I change $D$22 to $D$25, it appears highlight all cells. What is happening? I updated the linked excel with your answer. I updated the linked excel with your answer. – Lanzer Apr 03 '18 at 22:10
0

Use the formula method MATCH and ISERROR. So something like

=AND(NOT(ISERROR(MATCH(A1, $D:$D, 0))), NOT(A1=""))

This will look to see if it can find a match for the value. If it can't it returns an error, as we only want matches we invert the true/false result.

https://support.office.com/en-ie/article/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a

Monofuse
  • 735
  • 6
  • 14
  • This highlights a number of cells, and then highlights A22 all the way down to the end. I'm unsure why. I updated the linked excel with your answer. – Lanzer Apr 03 '18 at 22:11
  • You can add conditions on this to =if (A1 = "", false, NOT(ISERROR(MATCH(A1, $D:$D, 0)). It was meant more of a template for you to use. – Monofuse Apr 04 '18 at 09:14
  • I see. I'm a little bit confused how to apply that. Could you amend the template to fit what I have so far? – Lanzer Apr 04 '18 at 23:33
  • =AND(NOT(ISERROR(MATCH(A1, $D:$D, 0))), NOT(A1="")) – Monofuse Apr 06 '18 at 14:17
  • The AND function seemed easier today. Also you could create a second rule which is sit below the rule and sets background colors to white if it's empty. So 1 rules which is "=NOT(ISERROR(MATCH(A1, $D:$D, 0)))" and second one which will set the white color =A1="" – Monofuse Apr 06 '18 at 14:18
  • I put =NOT(ISERROR(MATCH(A1, $D:$D, 0))) in the conditional formatting, applying it to =$A:$A. The only thing that highlights is A3. A5 does not highlight as it should. What am I doing wrong? – Lanzer Apr 10 '18 at 17:05
0

Try this

=OR(NOT(ISNA(VLOOKUP(A1,$D:$D,1,FALSE))),NOT(ISNA(VLOOKUP("*"&D1&"*",$A:$A,1,FALSE))))

I have added second vlookup so it will return true in your situation.

Maddy
  • 771
  • 5
  • 14
  • This formula highlights only one cell and I'm unsure why. I updated the linked excel with your answer. – Lanzer Apr 04 '18 at 23:33
0

This is similar to a question at Good way to compare and highlight thousands of rows in VBA

There's two answers there that can easily be amended to suit. My answer at that thread optionally allows you to output a list of the duplicates.

I'll wait till I get clarification of my comments above, then post amended code here.

jeffreyweir
  • 4,668
  • 1
  • 16
  • 27
0

If you don't want to use VBA, the most efficient approach is to select your range in column A where you want the formatting to apply, and use this formula:

=OR(LEFT(A3,13)=[FIRSTROW]:[LASTROW])

...where FIRSTROW is the first cell in your master list in row D, and LASTROW is the last.

Don't use whole column references to do this. In fact, I'd recommend you turn both lists into separate Excel Tables, and assign a named range of CF_Master to the column of the Table in row D, and use that name in your CF formula i.e.:

=OR(LEFT(A3,13)=CF_Master)
jeffreyweir
  • 4,668
  • 1
  • 16
  • 27