armchairandy
Board Regular
- Joined
- Mar 27, 2012
- Messages
- 53
I am looking to compare two columns A & B and find if there are any sub-strings that are the same & Infill column C either with the same text or 1 if it matches - whichever is easier
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Col A[/TD]
[TD]Col B[/TD]
[TD]Col C[/TD]
[TD]Alt Col C[/TD]
[/TR]
[TR]
[TD]R5[/TD]
[TD]201F35[/TD]
[TD]030` 020` 712[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]R6[/TD]
[TD]NFL1006235` 1001 [/TD]
[TD]1001[/TD]
[TD]1001[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]R7[/TD]
[TD]050` 070` 010[/TD]
[TD]070[/TD]
[TD]070[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]R8[/TD]
[TD]125` PP120653[/TD]
[TD]PP120653` 5001` 100`125[/TD]
[TD]PP120653` 125[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]R9[/TD]
[TD]040` 050` 060[/TD]
[TD]060` 1001[/TD]
[TD]060[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]R10[/TD]
[TD]2222` 234` QF45[/TD]
[TD]561` GH675` 1234[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]
The values of each column can be numbers, text or numbers & text.
I do not necessarily need the same text extracted to column C, a 1,0 would be sufficient. I have thousands of rows in each column and therefore would be looking for a formula rather than VBA. I have seen some similar examples on the board, but none that would meet my needs. If there is more than one sub-string present they are always differentiated by "` " with a space after the character, and the sub-string is a varying length.
Any help would be appreciated
Regards
Andrew
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Col A[/TD]
[TD]Col B[/TD]
[TD]Col C[/TD]
[TD]Alt Col C[/TD]
[/TR]
[TR]
[TD]R5[/TD]
[TD]201F35[/TD]
[TD]030` 020` 712[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]R6[/TD]
[TD]NFL1006235` 1001 [/TD]
[TD]1001[/TD]
[TD]1001[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]R7[/TD]
[TD]050` 070` 010[/TD]
[TD]070[/TD]
[TD]070[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]R8[/TD]
[TD]125` PP120653[/TD]
[TD]PP120653` 5001` 100`125[/TD]
[TD]PP120653` 125[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]R9[/TD]
[TD]040` 050` 060[/TD]
[TD]060` 1001[/TD]
[TD]060[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]R10[/TD]
[TD]2222` 234` QF45[/TD]
[TD]561` GH675` 1234[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]
The values of each column can be numbers, text or numbers & text.
I do not necessarily need the same text extracted to column C, a 1,0 would be sufficient. I have thousands of rows in each column and therefore would be looking for a formula rather than VBA. I have seen some similar examples on the board, but none that would meet my needs. If there is more than one sub-string present they are always differentiated by "` " with a space after the character, and the sub-string is a varying length.
Any help would be appreciated
Regards
Andrew