# Solved Need to combine two sheets and sort / align columns in excel June 4, 2013 at 23:10:55
Specs: Windows 7
 I have two separate excel worksheets - both have a column which contains a reference number and there are common and unique numbers in both sheets. I need to combine the two sheets and align the two columns so that the rows of data from the second sheet line up with the rows of data from the first using the common policy number. The data in the third column needs to align with the data in the 2nd column so effectively there would be some spaces in the final result.Policy Number Policy Number Ref86119946117 86219941732 a86119700141 66509837115 b86120463761 86120700505 c86120166364 86219442365 d86120705856 65508447920 e86119459680 86119946117 f8611025800 86119700141 g65509155938 86120463761 h86220712280 86120166364 i86120206618 86120705856 j See More: Need to combine two sheets and sort / align columns in excel

#1 June 5, 2013 at 07:33:01
 A =VLOOKUP() should get you what you want.With your data like:Sheet 1``` A B C 1) Policy Number 2) 86119946117 3) 86119700141 4) 86120463761 5) 86120166364 6) 86120705856 7) 86119459680 8) 8611025800 9) 65509155938 10) 86220712280 11) 86120206618 Sheet 2 A B C 1) Policy Number Ref 2) 86219941732 a 3) 66509837115 b 4) 86120700505 c 5) 86219442365 d 6) 65508447920 e 7) 86119946117 f 8) 86119700141 g 9) 86120463761 h 10) 86120166364 i 11) 86120705856 j ```On Sheet 1, Cell B2 enter the formula:=VLOOKUP(A2,Sheet2!\$A\$2:\$B\$11,1,0)On Sheet 1, Cell C2 enter the formula:=VLOOKUP(A2,Sheet2!\$A\$2:\$B\$11,2,0)Drag down 10 rows and your Sheet 1 should now look like:```Sheet 1 A B C 1) Policy Number 2) 86119946117 86119946117 f 3) 86119700141 86119700141 g 4) 86120463761 86120463761 h 5) 86120166364 86120166364 i 6) 86120705856 86120705856 j 7) 86119459680 #N/A #N/A 8) 8611025800 #N/A #N/A 9) 65509155938 #N/A #N/A 10) 86220712280 #N/A #N/A 11) 86120206618 #N/A #N/A ```The #N/A is the error value that means "no value is available"in other words the =VLOOKUP() found no match.If you do not want the #N/A to display then use the formula:On Sheet 1, Cell B2=IF(ISNA(VLOOKUP(A2,Sheet2!\$A\$2:\$B\$11,1,0)),"",VLOOKUP(A2,Sheet2!\$A\$2:\$B\$11,1,0))On Sheet 1, Cell C2 =IF(ISNA(VLOOKUP(A2,Sheet2!\$A\$2:\$B\$11,2,0)),"",VLOOKUP(A2,Sheet2!\$A\$2:\$B\$11,2,0))See how that works for you.MIKEhttp://www.skeptic.com/ 