compare two lists

  • Thread starter Thread starter saman110 via OfficeKB.com
  • Start date Start date
S

saman110 via OfficeKB.com

Hello,

I would like to compare two wks and copy the corresponding data. For example.

In Sheet 1 I have:

Concord 94518
Concord 94519
Concord 94520
Concord 94521
Concord 94523


In Sheet 2 I have:

Concord
Concord
Concord
Concord
Concord
Concord
Concord
Concord

The result I should get in Sheet 1:

Concord 94518
Concord 94519
Concord 94520
Concord 94521
Concord 94523
Concord 94518
Concord 94519
Concord 94520


Any Help?

Thx.
 
You have to do a trick.
1st add a column before Concord column and type
=countif(b$2:b2, b2)
in sheet two (if there are only 5 record in sheet 1 you have to start your
data from row 6 in sheet 2)
type in column B
=VLOOKUP(IF(MOD(ROW(6:6),5)=0,5,MOD(ROW(6:6), 5)),Sheet1!$A$6:$c$10,3,0)
I hope it will work for you.
 
Hi,

your formula would work if I had concord sorted, but I have other concord in
other states.

ex.

CA Concord
CA Walnut Creek
CA Pittsburg
PA Concord
 
Back
Top