Return multiple account numbers based on multiple names from a list

[Joseph Adams]

Apologies in advance. This is my first time posting.

I have a list (DATA A) with two columns of data, Name and Account. I am trying to match the account numbers from Data A to a separate list (List B) which contains names, but where the names are separated into two separate columns for First Name and Last Name. Additionally, I do not want the matching to be exact so that First Name = John and Last Name = Doe will pull the account number from Data A even if the corresponding name from Data A is John Doe, Doe John, John F Doe, and would not pull account numbers from names such as Jane Doe, Doe Jane, John Smith, and so on. The condition is that both "John" and "Doe" be present. Finally, I am trying to get a list of all unique account numbers since my Data A list will have multiple variations of the name and with different account numbers.

The following is the direction I have been going in, but I can't seem to combine both First Name and Last Name.

I apologize, not sure if this image is going to past in. I guess I will be out of luck if it doesn't.

 

[Special-K99]

I doubt I'll solve this as it sounds fiddly and I dont have much time but FYI

You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[Akuini]

You could post a sample data as table.
To copy a simple table from worksheet:
1. In your worksheet select the table/range
2. In the menu, select border icon > select All Borders > then copy.
3. Back to the thread > in the reply box paste the table

 

[Special-K99]

You could post a sample data as table.
To copy a simple table from worksheet:
1. In your worksheet select the table/range
2. In the menu, select border icon > select All Borders > then copy.
3. Back to the thread > in the reply box paste the table

You sure this works? I can't get it to work, it posts the cell contents but not the borders.
Is there some setting that needs to be on to achieve this?

 

[Akuini]

It will show only the content part, like this:

<tbody>
[TD="class: xl65"]Clayton[/TD]
[TD="class: xl66"]Cayson[/TD]

[TD="class: xl66"]Cole[/TD]
[TD="class: xl66"]Isaiah[/TD]

[TD="class: xl66"]Erick[/TD]
[TD="class: xl66"]Jaziel[/TD]

[TD="class: xl66"]Gabriel[/TD]
[TD="class: xl66"]Kaleb[/TD]

[TD="class: xl66"]Jedidiah[/TD]
[TD="class: xl66"]Blaise[/TD]

</tbody>

but you can amend it manually (by typing the column & row around the data), like this:
[TABLE="width: 216"]
<tbody>[TR]
[TD="class: xl66, width: 72"][/TD]
[TD="class: xl66, width: 72"]A[/TD]
[TD="class: xl66, width: 72"]B[/TD]
[/TR]
[TR]
[TD="class: xl67"]1[/TD]
[TD="class: xl65, width: 72"]Clayton[/TD]
[TD="class: xl66"]Cayson[/TD]
[/TR]
[TR]
[TD="class: xl67"]2[/TD]
[TD="class: xl66"]Cole[/TD]
[TD="class: xl66"]Isaiah[/TD]
[/TR]
[TR]
[TD="class: xl67"]3[/TD]
[TD="class: xl66"]Erick[/TD]
[TD="class: xl66"]Jaziel[/TD]
[/TR]
[TR]
[TD="class: xl67"]4[/TD]
[TD="class: xl66"]Gabriel[/TD]
[TD="class: xl66"]Kaleb[/TD]
[/TR]
[TR]
[TD="class: xl67"]5[/TD]
[TD="class: xl66"]Jedidiah[/TD]
[TD="class: xl66"]Blaise[/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

testing...

d e f
a b c

I can only see content atm, just about to press Post Quick Reply button.
@Akuini: Can you post your test Excel file?

 

[Akuini]

Test:

[TABLE="width: 216"]
<colgroup><col style="width:54pt" width="72" span="3"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 72"] [/TD]
[TD="class: xl66, width: 72"]A[/TD]
[TD="class: xl66, width: 72"]B[/TD]
[/TR]
[TR]
[TD="class: xl67"]1[/TD]
[TD="class: xl65, width: 72"]Clayton[/TD]
[TD="class: xl66"]Cayson[/TD]
[/TR]
[TR]
[TD="class: xl67"]2[/TD]
[TD="class: xl66"]Cole[/TD]
[TD="class: xl66"]Isaiah[/TD]
[/TR]
[TR]
[TD="class: xl67"]3[/TD]
[TD="class: xl66"]Erick[/TD]
[TD="class: xl66"]Jaziel[/TD]
[/TR]
[TR]
[TD="class: xl67"]4[/TD]
[TD="class: xl66"]Gabriel[/TD]
[TD="class: xl66"]Kaleb[/TD]
[/TR]
[TR]
[TD="class: xl67"]5[/TD]
[TD="class: xl66"]Jedidiah[/TD]
[TD="class: xl66"]Blaise[/TD]
[/TR]
</tbody>[/TABLE]

 

[Akuini]

Earlier at post 5, I posted via GO Advance but in post 7 I just did it again by pressing Post Quick Reply button, the result are just the same.
I don't know why it's different from yours.

 

[Akuini]

Can you post your test Excel file?

Here's the file:
https://www.dropbox.com/s/enyac3s6hanojbn/test - posting table in thread 1.xlsx?dl=0

 

[Special-K99]

A B
1 Clayton Cayson
2 Cole Isaiah
3 Erick Jaziel
4 Gabriel Kaleb
5 Jedidiah Blaise


That's weird, hasn't worked.
Not to worry, I don't want to clog up this thread, thanks anyway.