Aligning Multiple Name Lists

[ThomasEinolf]

I am trying to align a list of names and their corresponding values to each other. For example, here is what I am working with: Let's say I have a list of 15 names in column A, each with a number related to them in Column B. Then in Column D I have another list of 10 names, with a number corresponding to each other in E. There are members who are in both lists, or only one list. My goal is to match them up without manually inserting columns. (in reality I have 2000 names to work with). 

I have a file to attached to illustrate the raw data and my goal. 

[#2 · Accepted solution]

@ThomasEinolf That would be a piece of cake for Power Query (PQ), provided that the names in both data sets are spelled exactly the same. I noted that some names had a trailing space and other did not. Then, you need to create som extra steps in PQ.

Connect to both lists, do some cleaning up. Then merged the two (full outer).

See attached, in columns O:R. If you are not familiar with PQ, here's a good start:

https://exceloffthegrid.com/power-query-introduction/ 

[#3]

Thank you! I am currently trying this example, and when I am going to merge the two, did you click that the values were both in common or the names? I am getting stuck on merging them together.

[#4]

@ThomasEinolf Not sure what you mean, but you need to trim the names first to get rid of the trailing spaces. Then you merge the two tables by the Name columns, full outer. Then expand the mergered records.

[#5]

Amazing! Worked perfectly! Thank you!