Lining up rows

  • Thread starter Thread starter Kevin
  • Start date Start date
K

Kevin

I've got a long list of zip codes (about 30 or 40
thousand) in column a. Columns b-d are composed of info
tha corresponds with the zips in a. I have another list
of zips with data, but this one is only about 20,000 rows
long, 2 columns wide. I want to be able to match these
two rows, so that the same zips are on the same lines. In
other words, the shorter zip code list that's on the right
will have some blank rows in between data so it matches.
An example is below. Thanks for your help. (Right now it
looks like "BEFORE", I want it to look like "AFTER".):

BEFORE:

00013 yellow 1 a 00015 2
00015 green 3 b 00020 5
00017 orange 6 r 00027 9
00020 blue 6 w 00033 1

AFTER:

00013 yellow 1 a
00015 green 3 b 00015 2
00017 orange 6 r
00020 blue 6 w 00020 5
..
..
..
00027 purple 3 a 00027 9
..
..
..
00033 white 2 l 00033 1
 
Assuming your second set of data is in Cols E & F, insert some more columns after D so that you
push your second set of data out to Cols H & I.

Now assuming your fisrt set of data is in A1:D40000, and your second set is in H1:I20000, in cell
E1 put the following formula:-

=IF(ISERROR(VLOOKUP($A1,$H$1:$I$20000,1,0)),"",VLOOKUP($A1,$H$1:$I$20000,1,0))

Then in cell F1, put the following formula:-

=IF(ISERROR(VLOOKUP($A1,$H$1:$I$20000,2,0)),"",VLOOKUP($A1,$H$1:$I$20000,2,0))

Now select cells E1:F1, and then copy down to E2:F40000.

Probably easier to just select E1, and then double click on the little black square at the bottom
right of the cell - Then do the same for cell F1.

When done, select all of E1:F40000, do Edit / Copy, and then Edit / Paste Special / Values

You can now delete Cols H & I
 
Back
Top