Match identical certain digits in 1column with another column

[ricklou]

Goodday

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

[Sindhus]

In F column, you can enter an array formula to find, which are the rows match your criteria

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

Regards,
Sindhu

[ricklou]

Hi Sindus thanx very much for your help but if I copy this in Column F it puts a zero there? #confused....Copied it down too. My data sits in Column G and H.Should I do conditional formatting now to see results.Thanx very much for your help!

[Sindhus]

The value gets counted, only if they exactly match. The spaces between the numbers in H column should also match with G column.

[ricklou]

1, 17, 40, 9, 8 wont match 1,17,40,9,8 the spacing is more in the first column..........That correct ? Conditional format must be formula equal to =F1>0 ?Thanx Sindhus

Testing quickly

[ricklou]

Will this ignore the first 5 numbers and the >> Sindus.....Tried it but I dont get it highlighted red....must be doing something wrong

[Sindhus]

Yes. The spacing is considered for matching the data. The formula is taking only the portion to the right ">> ".

And regarding conditional formatting, hope you applied the formula to the whole column. If so, make sure the formula doesn't have $ symbol. It should be F1 only, not $F$1

[ricklou]

Can you please confirm for me the spacing will work for 1, 25, 7, 40, 2 ..........Copied column F down got a 0 in every cell. Applied conditional formatting on whole column G formula =F1>0 -patern red but still no joy! Very tricky! Thanx Sindhus

[Sindhus]

Please Check the attached file. If your G column data is in G1:G100, then select F1:F100 and enter the array formula with Ctrl+Shift+Enter. If you enter in F1 and copy to others cells, it wont work. The F column data will be 1, wherever it matches with H column.

[ricklou]

Rep + to u for your help.........check and test quick Thanx so much Sindhus -I added to your reputation Can I ask you something LEN($G$1:$G$15) Its digit 8-12 I need to check example 12345>> 1, 25, 7, 40, 2 next colum H value 1, 25, 7, 40, 2

[Sindhus]

Thanks for the rep What is your question. I couldn't understand it.

[ricklou]

Thanx so much for your help and spreedsheet -may I just ask u the folowing question if I want the same applied to sample below what need to change as there are a different spacing and format ? Can this be done Sindhus ???? REp for u!!!!!

1, 10, 25, 33, 38 >> 1, 17, 40, 5, 12

Hvalue = 1, 17, 40, 5, 12

PS I am trying to understand your formula.........its quite complicated......lol

[ricklou]

Works flawlessly!!!!!!!!!!!!!!!!! Solved by Sindhus -Thank you Sindhus!!!!!!!!!!!!!!! Sindhus can you just confirm for me if the following can be done for example above ??? I have quite a simmiliar one with different data but same principle. Thank you!

[Sindhus]

Or you can remove the spaces in G and H column and use the formula in F column. =substitute(G1," ","") will remove the space in G1.

[Sindhus]

For the above case, you need not change anything. ">" symbol is the key for matching. Anything before it is not considered in the formula. Make sure your value is preceded by three characters ">> " (one space included)