Compare and Populate two Sheets

ctackett6407

Board Regular
Joined
Mar 18, 2018
Messages
66
Office Version
  1. 365
Platform
  1. Windows
Hi Folks,

I was given an excel sheet today that contained data that was missing a few fields.

Sheet1

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column1[/TD]
[TD]Column2
[/TD]
[TD]Column3[/TD]
[TD]Column4[/TD]
[TD]Column5[/TD]
[TD]Column6[/TD]
[/TR]
[TR]
[TD]Unique Key1[/TD]
[TD]Data4
[/TD]
[TD]Address[/TD]
[TD]City[/TD]
[TD]State[/TD]
[TD]Zip[/TD]
[/TR]
[TR]
[TD]Unique Key2[/TD]
[TD]Data5
[/TD]
[TD]Data8[/TD]
[TD]Data11[/TD]
[TD]Data14[/TD]
[TD]Data17[/TD]
[/TR]
[TR]
[TD]Unique Key2[/TD]
[TD]Data6
[/TD]
[TD]Data9[/TD]
[TD]Data12[/TD]
[TD]Data15[/TD]
[TD]Data18[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column1[/TD]
[TD]Column2
[/TD]
[TD]Column3[/TD]
[TD]Column4[/TD]
[TD]Column5[/TD]
[TD]Column6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Data4
[/TD]
[TD]Identity[/TD]
[TD]OtherData[/TD]
[TD]State[/TD]
[TD]Zip[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Data5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Data6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Okay so what I've been doing is I just copy and pasted the lists next to each other in the same sheet and used <code>=IF(EXACT(A2, B2), "Match", "") and am now manually going to each one that doesn't match and cutting it to a new sheet and I'll manually fix those later. the problem is I now have 300 that do not match and 3000 lines left to go through.

What I am trying to do is compare Column2 in Sheet1 to Column2 in Sheet2 and if they match I'm wanting to end with the following information;

Example using the first row under the column headers.

</code>[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Unique Key1[/TD]
[TD]Data[/TD]
[TD]Identity[/TD]
[TD]OtherData[/TD]
[TD]Address[/TD]
[TD]City[/TD]
[TD]State[/TD]
[TD]Zip[/TD]
[/TR]
</tbody>[/TABLE]
<code>
</code>
I'm trying to get it to compare Column2 from Sheet1 and Sheet2 and if they match then grab the Unique Key1 from Sheet1's matching row and grab Identity, Otherdata, Address, City, State Zip from Sheet1

Basically the Sheet1 was returned to me because it's missing the Identity and OtherData and I need to compare the Column2 against each other and find and return the missing data back to the company.

Any help would be great, I spent almost 5 hours doing this manually and using the Match formula and it just isn't working.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
[TABLE="width: 986"]
<colgroup><col><col><col span="7"><col><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]key
[/TD]
[TD]data[/TD]
[TD]Address[/TD]
[TD]City[/TD]
[TD]State[/TD]
[TD]Zip[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Unique Key1[/TD]
[TD]Data4[/TD]
[TD]addr1[/TD]
[TD]city1[/TD]
[TD]state1[/TD]
[TD]zip1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Unique Key3[/TD]
[TD]Data5[/TD]
[TD]addr2[/TD]
[TD]city2[/TD]
[TD]state2[/TD]
[TD]zip2[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Unique Key2[/TD]
[TD]Data6[/TD]
[TD]addr3[/TD]
[TD]city3[/TD]
[TD]state3[/TD]
[TD]zip3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Column1
[/TD]
[TD]Column2[/TD]
[TD]Address[/TD]
[TD]City[/TD]
[TD]State[/TD]
[TD]Zip[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]#####[/TD]
[TD]Unique Key2[/TD]
[TD]Data6[/TD]
[TD]addr3[/TD]
[TD]city3[/TD]
[TD]state3[/TD]
[TD]zip3[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]no match[/TD]
[TD]Data7[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Unique Key3[/TD]
[TD]Data5[/TD]
[TD]addr2[/TD]
[TD]city2[/TD]
[TD]state2[/TD]
[TD]zip2[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]####[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 12"]=IF(ISERROR(OFFSET($C$3,MATCH($C11,$C$4:$C$6,0),-1)),"no match",OFFSET($C$3,MATCH($C11,$C$4:$C$6,0),-1))[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top