Home/ Questions/Q 12185
Next
Answered
computing
  • 30
Asked: 2021-12-09T00:28:54+00:00 In:

Solved Need To Combine Two Sheets And Sort / Align Columns In Excel

  • 30

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 Ref
86119946117 86219941732 a
86119700141 66509837115 b
86120463761 86120700505 c
86120166364 86219442365 d
86120705856 65508447920 e
86119459680 86119946117 f
8611025800 86119700141 g
65509155938 86120463761 h
86220712280 86120166364 i
86120206618 86120705856 j

Share

    1 Answer

    1. Best Answer
      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.

      MIKE

      http://www.skeptic.com/

      • 0