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:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]NAME[/TD]
[TD]Company[/TD]
[TD]Address[/TD]
[TD]Interest[/TD]
[TD]Designation[/TD]
[TD]Specialty[/TD]
[/TR]
[TR]
[TD]TOM SMITH[/TD]
[TD]WALLMART[/TD]
[TD]123 STREET[/TD]
[TD][/TD]
[TD]DR[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TOM SMITH[/TD]
[TD]WALLMART[/TD]
[TD]123 ST.[/TD]
[TD]CRC[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TOM SMITH[/TD]
[TD]WALLMART,INC[/TD]
[TD]123 STREET[/TD]
[TD][/TD]
[TD][/TD]
[TD]FOOD[/TD]
[/TR]
</tbody>[/TABLE]
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 [TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]NAME[/TD]
[TD]COMPANY[/TD]
[TD]ADDRESS[/TD]
[TD]INTEREST[/TD]
[TD]DESIGNATION[/TD]
[TD]SPECIALTY[/TD]
[/TR]
[TR]
[TD]TOM SMITH[/TD]
[TD]WALLMART,INC[/TD]
[TD]123 ST.[/TD]
[TD]CRC[/TD]
[TD]DR[/TD]
[TD]FOOD[/TD]
[/TR]
</tbody>[/TABLE]
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!
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:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]NAME[/TD]
[TD]Company[/TD]
[TD]Address[/TD]
[TD]Interest[/TD]
[TD]Designation[/TD]
[TD]Specialty[/TD]
[/TR]
[TR]
[TD]TOM SMITH[/TD]
[TD]WALLMART[/TD]
[TD]123 STREET[/TD]
[TD][/TD]
[TD]DR[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TOM SMITH[/TD]
[TD]WALLMART[/TD]
[TD]123 ST.[/TD]
[TD]CRC[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TOM SMITH[/TD]
[TD]WALLMART,INC[/TD]
[TD]123 STREET[/TD]
[TD][/TD]
[TD][/TD]
[TD]FOOD[/TD]
[/TR]
</tbody>[/TABLE]
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 [TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]NAME[/TD]
[TD]COMPANY[/TD]
[TD]ADDRESS[/TD]
[TD]INTEREST[/TD]
[TD]DESIGNATION[/TD]
[TD]SPECIALTY[/TD]
[/TR]
[TR]
[TD]TOM SMITH[/TD]
[TD]WALLMART,INC[/TD]
[TD]123 ST.[/TD]
[TD]CRC[/TD]
[TD]DR[/TD]
[TD]FOOD[/TD]
[/TR]
</tbody>[/TABLE]
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!
