Consolidation: 3 Separate Lists Contacts That Have Partial Match but no Unique ID to merge rows.

[Atlanta]

Hi Folks,

So today I was thrown a curve ball on a project I'm working on. I Have 3 sheets of contacts from 3 different vendors which are all different sizes. These Sheets have very little overlap besides a few columns..."FULL NAME", "COMPANY NAME","ADDRESS LINE 1" and maybe a few other columns. But there is roughly 175 columns where there is no over lap at all. I've managed to consolidate the 3 sheets all into one sheet and sort the Contacts A>Z. This spreadsheet is now roughly 20,000 rows x 180 columns.

In Total there should only be around 11,000 of unique contacts.

The problem I am having now is that since I stacked 3 separate sheets on top of each other into 1 sheet, I now have 3 Rows x 175 Columns for many of my contacts with each of the 3 lines containing delicate information.

for example:

NAME	Company	Address	Interest	Designation	Specialty
TOM SMITH	WALLMART	123 STREET		DR
TOM SMITH	WALLMART	123 ST.	CRC
TOM SMITH	WALLMART,INC	123 STREET			FOOD

<tbody>
</tbody>

Basically I have 8000 contacts listed 3 times each for a total of 20,000 lines...

I can't remove duplicates because each line contains sensative information that I want to merge.

So if we are looking at the table above .. I would want my final output to look like this

NAME	COMPANY	ADDRESS	INTEREST	DESIGNATION	SPECIALTY
TOM SMITH	WALLMART,INC	123 ST.	CRC	DR	FOOD

<tbody>
</tbody>

I have each line color coded so since they are stacked I can differentiate which list they came from. I would like for the argument to be if a column contains information in all three rows that the "Blue" row wins.

If I haven't lost you guys by now.. I have attached a small scale sample of my workbook (9 rows) x ( 180 Columns)

Here is a link to what its current state is on sheet 1 on sheet two is my desired format!

https://www.dropbox.com/s/kal9q0pizyoy0h6/REQUEST FOR HELP.xlsx?dl=0


PS. i dont have any way to differentiate a difference between tom smith from tx and.. tom smith from NY. I need to find a way to group these contacts together through some sort of relationship. becausec the current way I have it stacked. there is 9 lines of tom smith, but in reality its 3 different guys names tom smith who live in three different states work at different companies.

I'm not the best at explaining things as this post has taken me over 1 hr to create. But feel free to ask me for additional information if you need it!

Im on a PC, Excel 2013, Windows 7

PLEASE PLEASE feel free to ask me to elaborate more.

Thanks guys!