CODEWITHSUNDEEP
excelexcel-formula
Lanzer
Lanzer

Reputation: 15

excel formula list substring of string

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,

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

Upvotes: 0

Views: 404

Answers (5)

Monofuse
Monofuse

Reputation: 827

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

Upvotes: 0

jeffreyweir
jeffreyweir

Reputation: 4824

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)

Upvotes: 0

jeffreyweir
jeffreyweir

Reputation: 4824

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.

Upvotes: 0

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

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.

Upvotes: 1

Maddy
Maddy

Reputation: 781

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.

Upvotes: 0

Related Questions