Sorting imported values against a master column

**rdough** · 03-08-2009, 07:53 PM

This problem involves comparing an incomplete column against a complete master column, then sorting the corresponding values to their correct rows. Column 1 is text, column 2 is decimal, 2 places.

PRESORT
Sheet 1 ______ Sheet 2_______
1 2 1 2
93100 25.00 93100 27.00
93101 37.00 93102 36.00
93102 16.00 93103 11.00
93103 14.00 * notice that 93101 is missing
G0010 20.00

Sheet 2 columns 1 and 2 are imported to sheet 1 as columns 3 and 4 as below.

POSTSORT
Sheet 1 ______________________
1 2 3 4
93100 25.00 93100 27.00
93101 37.00
93102 16.00 93102 36.00
93103 14.00 93103 11.00
G0010 20.00

Thanks, rdough

**JBeaucaire** · 03-08-2009, 08:13 PM

I would use two simple formulas to transfer the data.

In Sheet1 C1 enter this:

=IF(COUNTIF(Sheet2!$A:$A,$A1)>0,$A1,"")

In D1 enter this:

=IF(C1="","",INDEX(Sheet2!$B:$B,MATCH(C1,Sheet2!$A:$A,0)))

Copy those two cells over and your data will appear in the proper positions.

To make the data permanent, just highlight it all, CTRL-C, then EDIT > Paste Special > Values over the top of itself.