Compair two sheets and line-up data - VLOOKUP( But not sure)

S

simonmiller

New Member
Joined
Dec 31, 2010
Messages
19
Hallo Everyone.

I hope someone can help me. I have a sent of data like this. (Sheet One)

Excel 2007
ABC
1Unique IDhandleNew Code
26101f5b9-9c82-11e3-a0f5-b8ca3a64f8f4NO01040-5000
3613b7cbf-9c82-11e3-a0f5-b8ca3a64f8f4NO02A040-5003
4612a23b5-9c82-11e3-a0f5-b8ca3a64f8f4NO02040-5002
56118c87c-9c82-11e3-a0f5-b8ca3a64f8f4NO01A040-5001
6614c7b6d-9c82-11e3-a0f5-b8ca3a64f8f4NO03040-5004
76180f876-9c82-11e3-a0f5-b8ca3a64f8f4NO07040-5007
8616f448a-9c82-11e3-a0f5-b8ca3a64f8f4NO05040-5006
9615dd1cf-9c82-11e3-a0f5-b8ca3a64f8f4NO04040-5005
1061b64337-9c82-11e3-a0f5-b8ca3a64f8f4NO09A040-5010
1161a44f63-9c82-11e3-a0f5-b8ca3a64f8f4NO09040-5009
126192933a-9c82-11e3-a0f5-b8ca3a64f8f4NO08040-5008
1361c7e53d-9c82-11e3-a0f5-b8ca3a64f8f4NO10040-5011
1461d938d3-9c82-11e3-a0f5-b8ca3a64f8f4NO11040-5013
1561ea442a-9c82-11e3-a0f5-b8ca3a64f8f4NO12040-5014

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1​



And another sheet with this Data (Sheet 2)

Excel 2007
ABCDEFGHI
1New CodeDiscripNew Codeold Code Cost ExclRetail Incl
2040-5014NYLON MALE ADAPTOR 50MM040-5014NO.1215.577.9417.7520.00
3040-5013NYLON CONNECTOR 50MM040-5013NO.1113.316.7915.1815.00
4040-5011NYLON INSERT ELBOW 90 DEG 50MM040-5011NO.1020.5810.4923.4625.00
5040-5010NYLON COMBINATION ELBOW 45 DEG 50MM040-5010NO.09A21.8111.1224.8625.00
6040-5009NYLON COMBINATION ELBOW 90 DEG 50MM040-5009NO.0919.8310.1122.6125.00
7040-5008NYLON REDUCING BUSH 50MM - 40MM040-5008NO.0832.3416.5036.8735.00
8040-5007NYLON REDUCING CONNECTER 50MM - 40MM040-5007NO.0713.636.9515.5415.00
9040-5006NYLON INSERT T PIECE 40MM040-5006NO.0523.9012.1927.2425.00
10040-5005NYLON MALE ADAPTOR 40MM040-5005NO.0412.026.1313.7115.00
11040-5004NYLON CONNECTOR 40MM040-5004NO.0310.815.5112.3210.00
12040-5003NYLON INSERT ELBOW 45 DEG 40MM040-5003NO.02A17.979.1620.4820.00
13040-5002NYLON INSERT ELBOW 90 DEG 40MM040-5002NO.0213.616.9415.5215.00
14040-5001NYLON COMBINATION ELBOW 45 DEG 40MM040-5001NO.01A15.447.8817.6020.00
15040-5000NYLON COMBINATION ELBOW 90 DEG 40MM040-5000NO.0115.858.0818.0720.00

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2​



You will notice the last column in Sheet one (New Product) and the First Colomb in (Sheet 2) is both New Product.

I need it to look like this.

Excel 2007
ABCDEFGHIJKL
1Unique IDhandleNew CodeNew CodeDiscripNew Codeold Code Cost ExclRetail Incl
261ea442a-9c82-11e3-a0f5-b8ca3a64f8f4NO12040-5014040-5014NYLON MALE ADAPTOR 50MM040-5014NO.1215.577.9417.7520.00
361d938d3-9c82-11e3-a0f5-b8ca3a64f8f4NO11040-5013040-5013NYLON CONNECTOR 50MM040-5013NO.1113.316.7915.1815.00
461c7e53d-9c82-11e3-a0f5-b8ca3a64f8f4NO10040-5011040-5011NYLON INSERT ELBOW 90 DEG 50MM040-5011NO.1020.5810.4923.4625.00
561b64337-9c82-11e3-a0f5-b8ca3a64f8f4NO09A040-5010040-5010NYLON COMBINATION ELBOW 45 DEG 50MM040-5010NO.09A21.8111.1224.8625.00
661a44f63-9c82-11e3-a0f5-b8ca3a64f8f4NO09040-5009040-5009NYLON COMBINATION ELBOW 90 DEG 50MM040-5009NO.0919.8310.1122.6125.00
76192933a-9c82-11e3-a0f5-b8ca3a64f8f4NO08040-5008040-5008NYLON REDUCING BUSH 50MM - 40MM040-5008NO.0832.3416.5036.8735.00
86180f876-9c82-11e3-a0f5-b8ca3a64f8f4NO07040-5007040-5007NYLON REDUCING CONNECTER 50MM - 40MM040-5007NO.0713.636.9515.5415.00
9616f448a-9c82-11e3-a0f5-b8ca3a64f8f4NO05040-5006040-5006NYLON INSERT T PIECE 40MM040-5006NO.0523.9012.1927.2425.00
10615dd1cf-9c82-11e3-a0f5-b8ca3a64f8f4NO04040-5005040-5005NYLON MALE ADAPTOR 40MM040-5005NO.0412.026.1313.7115.00
11614c7b6d-9c82-11e3-a0f5-b8ca3a64f8f4NO03040-5004040-5004NYLON CONNECTOR 40MM040-5004NO.0310.815.5112.3210.00
12613b7cbf-9c82-11e3-a0f5-b8ca3a64f8f4NO02A040-5003040-5003NYLON INSERT ELBOW 45 DEG 40MM040-5003NO.02A17.979.1620.4820.00
13612a23b5-9c82-11e3-a0f5-b8ca3a64f8f4NO02040-5002040-5002NYLON INSERT ELBOW 90 DEG 40MM040-5002NO.0213.616.9415.5215.00
146118c87c-9c82-11e3-a0f5-b8ca3a64f8f4NO01A040-5001040-5001NYLON COMBINATION ELBOW 45 DEG 40MM040-5001NO.01A15.447.8817.6020.00
156101f5b9-9c82-11e3-a0f5-b8ca3a64f8f4NO01040-5000040-5000NYLON COMBINATION ELBOW 90 DEG 40MM040-5000NO.0115.858.0818.0720.00

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
vend-products-31282​
So basically getting the data from Sheet 2 and Copying in next to the data from sheet one. HOWEVER and this is what the issue is. In the last Screenshot above - The tow (New Products) column (C and D ) need to be matched up to the same code. And the data next to it has to remain inline with the code when it its sorting and lineing it up.

Please keep in mine the above info is just a example. There might be places where the sheet might not find the code to match. A error will be ok.

I hope it makes sense.

Thank you
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
<html><head><title>Excel Jeanie HTML</title></head><body>
<!-- ######### Start Created Html Code To Copy ########## -->
Excel Workbook
ABCDEFGHIJKL
1New CodeUnique IDhandleNew CodeVlookupIDVlookupCodeNew CodeDiscripNew Codeold CodeCost Excl
2040-50006101f5b9-9c82-11e3-a0f5-b8ca3a64f8f4NO01040-500061ea442a-9c82-11e3-a0f5-b8ca3a64f8f4NO12040-5014NYLON MALE ADAPTOR 50MM040-5014NO.1215.57
3040-5003613b7cbf-9c82-11e3-a0f5-b8ca3a64f8f4NO02A040-5003040-5013NYLON CONNECTOR 50MM040-5013NO.1113.31
4040-5002612a23b5-9c82-11e3-a0f5-b8ca3a64f8f4NO02040-5002040-5011NYLON INSERT ELBOW 90 DEG 50MM040-5011NO.1020.58
5040-50016118c87c-9c82-11e3-a0f5-b8ca3a64f8f4NO01A040-5001040-5010NYLON COMBINATION ELBOW 45 DEG 50MM040-5010NO.09A21.81
6040-5004614c7b6d-9c82-11e3-a0f5-b8ca3a64f8f4NO03040-5004040-5009NYLON COMBINATION ELBOW 90 DEG 50MM040-5009NO.0919.83
7040-50076180f876-9c82-11e3-a0f5-b8ca3a64f8f4NO07040-5007040-5008NYLON REDUCING BUSH 50MM - 40MM040-5008NO.0832.34
8040-5006616f448a-9c82-11e3-a0f5-b8ca3a64f8f4NO05040-5006040-5007NYLON REDUCING CONNECTER 50MM - 40MM040-5007NO.0713.63
9040-5005615dd1cf-9c82-11e3-a0f5-b8ca3a64f8f4NO04040-5005040-5006NYLON INSERT T PIECE 40MM040-5006NO.0523.9
10040-501061b64337-9c82-11e3-a0f5-b8ca3a64f8f4NO09A040-5010040-5005NYLON MALE ADAPTOR 40MM040-5005NO.0412.02
11040-500961a44f63-9c82-11e3-a0f5-b8ca3a64f8f4NO09040-5009040-5004NYLON CONNECTOR 40MM040-5004NO.0310.81
12040-50086192933a-9c82-11e3-a0f5-b8ca3a64f8f4NO08040-5008040-5003NYLON INSERT ELBOW 45 DEG 40MM040-5003NO.02A17.97
13040-501161c7e53d-9c82-11e3-a0f5-b8ca3a64f8f4NO10040-5011040-5002NYLON INSERT ELBOW 90 DEG 40MM040-5002NO.0213.61
14040-501361d938d3-9c82-11e3-a0f5-b8ca3a64f8f4NO11040-5013040-5001NYLON COMBINATION ELBOW 45 DEG 40MM040-5001NO.01A15.44
15040-501461ea442a-9c82-11e3-a0f5-b8ca3a64f8f4NO12040-5014040-5000NYLON COMBINATION ELBOW 90 DEG 40MM040-5000NO.0115.85
Sheet2

?
?
<!-- ######### End Created Html Code To Copy ########## -->
</body></html>

 
Upvote 0
simonmiller,

How about a macro solution, that will adjust for the varying number of rows in worksheets Sheet1, and, Sheet2?

Sample worksheets:


Excel 2007
ABC
1Unique IDhandleNew Code
26101f5b9-9c82-11e3-a0f5-b8ca3a64f8f4NO01040-5000
3613b7cbf-9c82-11e3-a0f5-b8ca3a64f8f4NO02A040-5003
4612a23b5-9c82-11e3-a0f5-b8ca3a64f8f4NO02040-5002
56118c87c-9c82-11e3-a0f5-b8ca3a64f8f4NO01A040-5001
6614c7b6d-9c82-11e3-a0f5-b8ca3a64f8f4NO03040-5004
76180f876-9c82-11e3-a0f5-b8ca3a64f8f4NO07040-5007
8616f448a-9c82-11e3-a0f5-b8ca3a64f8f4NO05040-5006
9615dd1cf-9c82-11e3-a0f5-b8ca3a64f8f4NO04040-5005
1061b64337-9c82-11e3-a0f5-b8ca3a64f8f4NO09A040-5010
1161a44f63-9c82-11e3-a0f5-b8ca3a64f8f4NO09040-5009
126192933a-9c82-11e3-a0f5-b8ca3a64f8f4NO08040-5008
1361c7e53d-9c82-11e3-a0f5-b8ca3a64f8f4NO10040-5011
1461d938d3-9c82-11e3-a0f5-b8ca3a64f8f4NO11040-5013
1561ea442a-9c82-11e3-a0f5-b8ca3a64f8f4NO12040-5014
16
Sheet1



Excel 2007
ABCDEFGHI
1New CodeDiscripNew Codeold CodeCost ExclRetail Incl
2040-5014NYLON MALE ADAPTOR 50MM040-5014NO.1215.577.9417.7520
3040-5013NYLON CONNECTOR 50MM040-5013NO.1113.316.7915.1815
4040-5011NYLON INSERT ELBOW 90 DEG 50MM040-5011NO.1020.5810.4923.4625
5040-5010NYLON COMBINATION ELBOW 45 DEG 50MM040-5010NO.09A21.8111.1224.8625
6040-5009NYLON COMBINATION ELBOW 90 DEG 50MM040-5009NO.0919.8310.1122.6125
7040-5008NYLON REDUCING BUSH 50MM - 40MM040-5008NO.0832.3416.536.8735
8040-5007NYLON REDUCING CONNECTER 50MM - 40MM040-5007NO.0713.636.9515.5415
9040-5006NYLON INSERT T PIECE 40MM040-5006NO.0523.912.1927.2425
10040-5005NYLON MALE ADAPTOR 40MM040-5005NO.0412.026.1313.7115
11040-5004NYLON CONNECTOR 40MM040-5004NO.0310.815.5112.3210
12040-5003NYLON INSERT ELBOW 45 DEG 40MM040-5003NO.02A17.979.1620.4820
13040-5002NYLON INSERT ELBOW 90 DEG 40MM040-5002NO.0213.616.9415.5215
14040-5001NYLON COMBINATION ELBOW 45 DEG 40MM040-5001NO.01A15.447.8817.620
15040-5000NYLON COMBINATION ELBOW 90 DEG 40MM040-5000NO.0115.858.0818.0720
16
Sheet2


The macro will ask for the 5 digit number, in this case 31282, and, if worksheet vend-products-31282 does NOT exist, the macro will create it. If the worksheet DOES exist, the macro will clear the worksheet, and, produce the new results in the NEW worksheet.

After the macro in worksheet vend-products-31282:


Excel 2007
ABCDEFGHIJKL
1Unique IDhandleNew CodeNew CodeDiscripNew Codeold CodeCost ExclRetail Incl
261ea442a-9c82-11e3-a0f5-b8ca3a64f8f4NO12040-5014040-5014NYLON MALE ADAPTOR 50MM040-5014NO.1215.577.9417.7520
361d938d3-9c82-11e3-a0f5-b8ca3a64f8f4NO11040-5013040-5013NYLON CONNECTOR 50MM040-5013NO.1113.316.7915.1815
461c7e53d-9c82-11e3-a0f5-b8ca3a64f8f4NO10040-5011040-5011NYLON INSERT ELBOW 90 DEG 50MM040-5011NO.1020.5810.4923.4625
561b64337-9c82-11e3-a0f5-b8ca3a64f8f4NO09A040-5010040-5010NYLON COMBINATION ELBOW 45 DEG 50MM040-5010NO.09A21.8111.1224.8625
661a44f63-9c82-11e3-a0f5-b8ca3a64f8f4NO09040-5009040-5009NYLON COMBINATION ELBOW 90 DEG 50MM040-5009NO.0919.8310.1122.6125
76192933a-9c82-11e3-a0f5-b8ca3a64f8f4NO08040-5008040-5008NYLON REDUCING BUSH 50MM - 40MM040-5008NO.0832.3416.536.8735
86180f876-9c82-11e3-a0f5-b8ca3a64f8f4NO07040-5007040-5007NYLON REDUCING CONNECTER 50MM - 40MM040-5007NO.0713.636.9515.5415
9616f448a-9c82-11e3-a0f5-b8ca3a64f8f4NO05040-5006040-5006NYLON INSERT T PIECE 40MM040-5006NO.0523.912.1927.2425
10615dd1cf-9c82-11e3-a0f5-b8ca3a64f8f4NO04040-5005040-5005NYLON MALE ADAPTOR 40MM040-5005NO.0412.026.1313.7115
11614c7b6d-9c82-11e3-a0f5-b8ca3a64f8f4NO03040-5004040-5004NYLON CONNECTOR 40MM040-5004NO.0310.815.5112.3210
12613b7cbf-9c82-11e3-a0f5-b8ca3a64f8f4NO02A040-5003040-5003NYLON INSERT ELBOW 45 DEG 40MM040-5003NO.02A17.979.1620.4820
13612a23b5-9c82-11e3-a0f5-b8ca3a64f8f4NO02040-5002040-5002NYLON INSERT ELBOW 90 DEG 40MM040-5002NO.0213.616.9415.5215
146118c87c-9c82-11e3-a0f5-b8ca3a64f8f4NO01A040-5001040-5001NYLON COMBINATION ELBOW 45 DEG 40MM040-5001NO.01A15.447.8817.620
156101f5b9-9c82-11e3-a0f5-b8ca3a64f8f4NO01040-5000040-5000NYLON COMBINATION ELBOW 90 DEG 40MM040-5000NO.0115.858.0818.0720
16
vend-products-31282


The colored cells are the cells that were copied from worksheets Sheet1, and, Sheet2 to show where the cells were copied from.

I do have another macro that does not use copy/paste, and, is faster. I will post it in a little while.
 
Last edited:
Upvote 0
simonmiller,

Here is the macro, and, function, for my reply #3.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below macro code, and, function.
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Sub ReorgData()
' hiker95, 07/26/2014, ME794381
Dim w1 As Worksheet, w2 As Worksheet, wv As Worksheet
Dim lr1 As Long, lr2 As Long, vpn As String
Dim c As Range, rng As Range, lrv As Long, ncrng As Range
Application.ScreenUpdating = False
Set w1 = Sheets("Sheet1")
lr1 = w1.Cells(Rows.Count, "A").End(xlUp).Row
Set w2 = Sheets("Sheet2")
lr2 = w2.Cells(Rows.Count, "A").End(xlUp).Row
vpn = "vend-products-"
vpn = vpn & InputBox("What is the 5 digit number for the NEW worksheet 'vend-products-#####'?")
If Not WorksheetExists(vpn) Then
  Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = vpn
End If
Set wv = Sheets(vpn)
With wv
  .UsedRange.ClearContents
  .Cells(1, 1).Resize(, 12).Value = Array("Unique ID", "handle", "New Code", "New Code", _
    "Discrip", "New Code", "old Code", "Cost Excl", "", "", "", "Retail Incl")
End With
With CreateObject("Scripting.Dictionary")
  .CompareMode = vbTextCompare
  Set rng = w1.Range("C2:C" & lr1)
  For Each c In rng
    If Not .Exists(c.Value) Then .Add c.Value, 1
  Next c
  Set rng = w2.Range("A2:A" & lr2)
  For Each c In rng
    If Not .Exists(c.Value) Then .Add c.Value, 1
  Next c
  wv.Range("C2").Resize(.Count) = Application.Transpose(Array(.Keys))
End With
With wv
  lrv = .Cells(Rows.Count, "C").End(xlUp).Row
  .Range("C2:C" & lrv).Sort key1:=.Range("C2"), order1:=2
  For Each c In .Range("C2:C" & lrv)
    Set ncrng = w1.Columns(3).Find(c.Value, LookAt:=xlWhole)
    If Not ncrng Is Nothing Then
      w1.Cells(ncrng.Row, 1).Resize(, 2).Copy .Cells(c.Row, 1).Resize(, 2)
      Application.CutCopyMode = False
      Set ncrng = Nothing
    End If
    Set ncrng = w2.Columns(1).Find(c.Value, LookAt:=xlWhole)
    If Not ncrng Is Nothing Then
      w2.Cells(ncrng.Row, 1).Resize(, 9).Copy .Cells(c.Row, 4).Resize(, 9)
      Application.CutCopyMode = False
      Set ncrng = Nothing
    End If
  Next c
  .Columns.AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub
Function WorksheetExists(WSName As String) As Boolean
On Error Resume Next
WorksheetExists = Worksheets(WSName).Name = WSName
On Error GoTo 0
End Function

You may have to add the Microsoft Scripting Runtime to the References - VBA Project.

With your workbook that contains the above:

Press the keys ALT + F11 to open the Visual Basic Editor

In the VBA Editor, click on:
Tools
References...

Put a checkmark in the box marked
Microsoft Scripting Runtime

Then click on the OK button.

And, exit out of the VBA Editor.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgData macro.
 
Upvote 0
simonmiller,

Here is the updated macro, and, function:

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Code: 
Sub ReorgData_V2()
' hiker95, 07/26/2014, ME794381
Dim w1 As Worksheet, w2 As Worksheet, wv As Worksheet
Dim lr1 As Long, lr2 As Long, vpn As String
Dim c As Range, rng As Range, lrv As Long, ncrng As Range
Application.ScreenUpdating = False
Set w1 = Sheets("Sheet1")
lr1 = w1.Cells(Rows.Count, "A").End(xlUp).Row
Set w2 = Sheets("Sheet2")
lr2 = w2.Cells(Rows.Count, "A").End(xlUp).Row
vpn = "vend-products-"
vpn = vpn & InputBox("What is the 5 digit number for the NEW worksheet 'vend-products-#####'?")
If Not WorksheetExists_V2(vpn) Then
  Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = vpn
End If
Set wv = Sheets(vpn)
With wv
  .UsedRange.ClearContents
  .Cells(1, 1).Resize(, 12).Value = Array("Unique ID", "handle", "New Code", "New Code", _
    "Discrip", "New Code", "old Code", "Cost Excl", "", "", "", "Retail Incl")
End With
With CreateObject("Scripting.Dictionary")
  .CompareMode = vbTextCompare
  Set rng = w1.Range("C2:C" & lr1)
  For Each c In rng
    If Not .Exists(c.Value) Then .Add c.Value, 1
  Next c
  Set rng = w2.Range("A2:A" & lr2)
  For Each c In rng
    If Not .Exists(c.Value) Then .Add c.Value, 1
  Next c
  wv.Range("C2").Resize(.Count) = Application.Transpose(Array(.Keys))
End With
With wv
  lrv = .Cells(Rows.Count, "C").End(xlUp).Row
  .Range("C2:C" & lrv).Sort key1:=.Range("C2"), order1:=2
  For Each c In .Range("C2:C" & lrv)
    Set ncrng = w1.Columns(3).Find(c.Value, LookAt:=xlWhole)
    If Not ncrng Is Nothing Then
      .Cells(c.Row, 1).Resize(, 2).Value = w1.Cells(ncrng.Row, 1).Resize(, 2).Value
      Application.CutCopyMode = False
      Set ncrng = Nothing
    End If
    Set ncrng = w2.Columns(1).Find(c.Value, LookAt:=xlWhole)
    If Not ncrng Is Nothing Then
      .Cells(c.Row, 4).Resize(, 9).Value = w2.Cells(ncrng.Row, 1).Resize(, 9).Value
      Application.CutCopyMode = False
      Set ncrng = Nothing
    End If
  Next c
  .Columns.AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub
Function WorksheetExists_V2(WSName As String) As Boolean
On Error Resume Next
WorksheetExists_V2 = Worksheets(WSName).Name = WSName
On Error GoTo 0
End Function

You may have to add the Microsoft Scripting Runtime to the References - VBA Project.

With your workbook that contains the above:

Press the keys ALT + F11 to open the Visual Basic Editor

In the VBA Editor, click on:
Tools
References...

Put a checkmark in the box marked
Microsoft Scripting Runtime

Then click on the OK button.

And, exit out of the VBA Editor.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgData_V2 macro.
 
Upvote 0
Enter header names of your choice on Sheet 1, row 1,columns I, J & K that match header names you'll enter on Sheet 2, row 1 columns F, G & H. The formula uses the header names in sheet 1 to find the appropriate column in sheet 2 to return to sheet 1, so header names can be in any order but must be spelled identically.
Now enter this formula on Sheet 1 cell D2 and copy down and across:
Code: 
=INDEX(INDIRECT("Sheet2!"&ADDRESS(2,MATCH(D$1,Sheet2!$A$1:$I$1,0))&":"&ADDRESS(15,MATCH(D$1,Sheet2!$A$1:$I$1,0))),MATCH($C2,Sheet2!$A$2:$A$15,0))
You'll need to adapt the range of sample rows 2 to 15 to reflect your actual data in sheet 2. (ie: change the 15 after ...ADDRESS(15... to your last row, as well as the 15 in ...Sheet2!$A$2:$A$15... You may want to make it a row well below the last row you think your actual data may grow to.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
compare two workbooks and add relevant data to one workbook
Replies
6
Views
527
Halley yenn
H
J
  • Question
Indirect Function Delivering Blank Cell Results
Replies
6
Views
525
jonholm619
J
M
  • Question
concatenate IF to control accounts receivables given Status of it.
Replies
1
Views
249
Andrew Poulsom
Andrew Poulsom
C
  • Question
Update a cell based on another lookup
Replies
1
Views
451
fairwinds
fairwinds
E
  • Question
Finding Days off?
Replies
2
Views
344
eblake
E

Forum statistics

Threads
1,215,059
Messages
6,122,918
Members
449,094
Latest member
teemeren

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top