Matching sequence of 'n' number of lettes in 2 coulmns

[skf786]

Hi, I would like to match 2 columns of data. both columns contain names of customers (or a transaction number) somewhere in the text in each column (start, middle or end). Basically need to match 'n' number of letters in a sequence in both columns where 'n' is a maximum number of letters.

e.g. Column A and B are data columns and Column C and D (Row Number) are the required output columns

ROW # Column A Column B Column C Column D
1 Reference 435345 John Wayne loan number 535235 customer: Jack Reacher Patrick ref 535235 John Wayne 2
2 Pack my box with Jack Reacher loan number 535245 customer: John Wayne ref 535235 Jack Reacher 1
3 Reference 435345 John Bush loan number 535235 Richard Gere ref 535235 John Bush 4
4 Richard Gere loan number 535235 customer: John Bush ref 535235 Richard Gere 3
5 Reference FT1234567899 dsfsddfs Albert Robert dfsdfs dsfdfsd FT1234567899 6
6 Albert Robert loan number 535234 FT1234567899 sdfasfsfs Albert Robert 5

Thank you in advance!

Khalid

 

[jasonb75]

There isn't much anyone can do with that data sample, this should help you to post it in a legible format XL2BB - Excel Range to BBCode

 

[Rick Rothstein]

There isn't much anyone can do with that data sample, this should help you to post it in a legible format XL2BB - Excel Range to BBCode

Try hitting the Reply link on Message #1 and you will see the layout in a better format.

 

[skf786]

Thank Jason, Pasting the data in the image below as I cant get the addin on this computer. Hope this helps in explaining the problem. So the data is in columns A and B (Rows 2 to 7) and Required output is in C and D (Row Number.

 

[jasonb75]

Thanks, Rick, that is useful to know!

Not sure that seeing the data properly formatted has helped much though

 

[skf786]

Basically, i'm looking to pick customer names in two separate columns of about 30,000 rows of data. Would like to put/sort the data in an order where entries for same customer name come together.

Not sure that seeing the data properly formatted has helped much though
[/QUOTE]

 

[jasonb75]

Sorry, I can see what you need, but not how to resolve the problem.

I think that you will most likely benefit from fuzzy logic, but that will require an addin. Download Fuzzy Lookup Add-In for Excel from Official Microsoft Download Center

 

[navic]

I'm not sure I'll help you much, but maybe this idea will direct you to a solution.
If you have the options, add a helper column and list all your customers then try this one below.
Put in a 'C2' cell

=LOOKUP(10^10,SEARCH($G$2:$G$10,A2),$G$2:$G$10)

Put in a 'D2' cell

=LOOKUP(10^10,SEARCH($G$2:$G$10,B2),$G$2:$G$10)

Put in a 'E2' cell

=MATCH(C2,$D$1:$D$10,0)

Of course, you can hide certain columns. Also, You can use IFERROR function to remove '#N/A'

 

[skf786]

Thank you. This works great!

 

[navic]

You're welcome