Match identical certain digits in 1 column with another column and higlight match

[ricklou]

Goodday

Try to apply below on my sheet attached

Just want to find out which is the best a macro or a formulae to match the matches in 1 column with another column but here is the tricky part the one colum have 10 digits and i only want to match the last 5 with the other column.If a match occur then all that must be done Column G Values must be highlighted red. Is there a way to achieve this ?. Thanks for any help

Column G

Column H

19765 >> 1, 17, 5, 40, 12

1,0,25,9,40

37482>> 1, 17, 3, 14, 40

1,17,40,9,8

78657 >> 1, 17, 0, 40, 17
64591 >> 1, 17, 8, 9, 40
44587 >> 1, 17, 14, 3, 40
55662 >> 1, 17, 17, 0, 40
22113 >> 1, 17, 17, 40, 0
22334 >> 1, 17, 17, 40, 0
88775 >> 1, 17, 40, 9, 8
51555 >> 1, 17, 40, 5, 12
10012 >> 1, 17, 40, 1, 16
18860 >> 1, 0, 25, 9, 40
11445 >> 1, 25, 3, 40, 6
22335 >> 1, 25, 5, 40, 4



Got this solution but my spacing somewhere is wrong
In F column, you can enter an array formula to find, which are the rows match your criteria
Formula: [Select Code] copy to clipboard

Please Login or Register  to view this content.

This will work only if the text in H column exactly matches with the numers after >> in g column.

Then to format the cells, use conditional formatting, =F1>0

[Ace_XL]

Use this in conditional formatting

=ISNUMBER(MATCH(TRIM(RIGHT(G1,LEN(G1)-FIND(">> ",G1)-1)),$H$1:$H$461,0))

format --colour

[ricklou]

Hi Ace

Thanks very much for your reply, should this be copied to column F and copied down then conditional formatting =F1>0 ?

[Ace_XL]

Nope..not quite

- Select your data in Column G.
- Go to conditional formatting
- Use formuals to determine ...type in above formula in my earlier post
- format -- colour -- font, fill etc.

See attached.

[ricklou]

Ace works like a Charm -thank you very much!!!!!!!!!!!!!!!!!!!!!!!!!