Compare and Populate two Sheets

[ctackett6407]

Hi Folks,

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

Sheet1

Column1	Column2	Column3	Column4	Column5	Column6
Unique Key1	Data4	Address	City	State	Zip
Unique Key2	Data5	Data8	Data11	Data14	Data17
Unique Key2	Data6	Data9	Data12	Data15	Data18

<tbody>
</tbody>

Sheet2

Column1	Column2	Column3	Column4	Column5	Column6
	Data4	Identity	OtherData	State	Zip
	Data5
	Data6

<tbody>
</tbody>

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>

Unique Key1

Data

Identity

OtherData

Address

City

State

Zip

<tbody>
</tbody>

<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.

 

[oldbrewer]

key

data

Address

City

State

Zip

Unique Key1

Data4

addr1

city1

state1

zip1

Unique Key3

Data5

addr2

city2

state2

zip2

Unique Key2

Data6

addr3

city3

state3

zip3

Column1

Column2

Address

City

State

Zip

#####

Unique Key2

Data6

addr3

city3

state3

zip3

no match

Data7

#N/A

#N/A

#N/A

#N/A

Unique Key3

Data5

addr2

city2

state2

zip2

####

=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))

<colgroup><col><col><col span="7"><col><col><col span="3"></colgroup><tbody>
</tbody>