Vlookup - finding a value in more than just the left hand column

[inrol]

Hope someone can help with this.

I have data arranged like this -
ID1 ID2 ID3 Data1 Data2
45 56 89 aa dd
67 bb ee
91 78 cc ff

I need to get the data arranged like this -
ID ADD1 ADD2
45 aa dd
56 aa dd
67 bb ee
78 cc ff
89 aa dd
91 cc ff

This is just an example, the dataset I have is much larger.

Thank you for any help.

 

[inrol]

Thank you again. I really appreciate the help. I'll try this and let you know how I get on.

yeah let me add notes to mine and maybe offthelip will with their code.
My brain doesnt think in that way offthelip's code is written so it'd be best if they tell you what to change in that lol.
If you aren't really sure how normal vlookups work even with my notes it will be confusing but i tried my best
You may want to look up R1C1 style formulas if you'll be working in VBA

Sub Transpose3()
    Dim lastRow As Long
    Dim R1 As Range
    Dim R2 As Range
    Dim R3 As Range
    Dim RowN As Integer
    Dim ws As Worksheet
    
    Set ws = ActiveSheet

    Sheets.Add After:=ActiveSheet
    ActiveSheet.Range("A1").Select
    ' ----------------- this is just naming the headers on the new sheet "Data Results" you can add more data columns in D1 and beyond by copying these and changing them to the appropriate cells -----------------------
    ActiveCell.FormulaR1C1 = "ID#"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Data1"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Data2"
    ActiveSheet.Name = "Data Results"
    
    ws.Select
    lastRow = Range("D" & Rows.Count).End(xlUp).Row
    
    ' ----------------- A2:C below here refers to the range of your IDs, you can change this to fit your needs --------------
    
    Set R1 = Range("A2:C" & lastRow)
    Set R2 = Sheets("Data Results").Range("A2")
    RowN = 0
    Application.ScreenUpdating = False
    For Each R3 In R1.Rows
        R3.Copy
        R2.Offset(RowN, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True
        RowN = RowN + R3.Columns.Count
    Next
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    
    
    Sheets("Data Results").Select
    Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    
' ---------------------- Everything below this point are the vlookups, Range B2 is Data 1 or the first cell of data. Change "Sheet1" to match the sheet name where the IDs and Data is wherever you see it. C1:C4 refers to columns 1-4 as the lookup range, and the ", 4, false" is telling us we want the 4th column returned as the value. Change these ranges based on your needs. Note that the first column you look up must contain the ID and the last column must be the Data ----------------------------------
     Range("B2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]="""","""",IFERROR(VLOOKUP(RC[-1],Sheet1!C1:C4,4,FALSE),IFERROR(VLOOKUP(RC[-1],Sheet1!C2:C5,3,FALSE),IFERROR(VLOOKUP(RC[-1],Sheet1!C3:C4,2,FALSE),""""))))"
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("B2").AutoFill Destination:=Range("B2:B" & lastRow)
    
    Columns("B:B").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 ' --------------------- Range C2 is like B2 but for Data2 --------------------------
    Range("C2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-2]="""","""",IFERROR(VLOOKUP(RC[-2],Sheet1!R1:R1048576,5,FALSE),IFERROR(VLOOKUP(RC[-2],Sheet1!C2:C5,4,FALSE),IFERROR(VLOOKUP(RC[-2],Sheet1!C3:C5,3,FALSE),""""""""))))"
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("C2").AutoFill Destination:=Range("C2:C" & lastRow)
    
    Columns("C:C").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
End Sub

Here are some helpful tools to understanding my vlookup that you can change to suit your needs. Especially if you'll be working with VBA and excel in the future
https://www.ablebits.com/office-addins-blog/2018/02/15/excel-iferror-vlookup/
https://excelribbon.tips.net/T008803_Understanding_R1C1_References.html

 

[DataBlake]

Thank you again. I really appreciate the help. I'll try this and let you know how I get on.

also if you give me another example with different columns i can better demonstrate what to change in mine

 

[offthelip]

I have annotated my code to show you what it is doing. I am using variant arrays which as Blakeskate says is a much faster way of doing things in VBa. Often 1000 times faster than looping through a worksheet. so learning how to code like this can make your vba much more efficient. It is also very easy to modify once you have understood how it works. each variant array I have defined is just a mirror image of the worksheet, you just have to address them using numbers for the columns rather letters. This makes uisng indices very easy. So to pick up more columns and move things about you just need to increase the size of the array when you pick it . e,g:

inarr = Range(Cells(1, 1), Cells(lastrow, 500))

will pick up 500 columns
you can then loop through finding multiple mathces of whatever you need , it is much more flexible than vlookup or any excel function
I never use vlookup in VBA.

Sub test()
' find last row of active sheet
lastrow = Range("D" & Rows.Count).End(xlUp).Row
'load all of the data in first 5  columnns of active sheet into a varant arry "inarr" (cols A to E)
inarr = Range(Cells(1, 1), Cells(lastrow, 5))


' Add a sheet and select it (automatically selected by adding it)
Sheets.Add After:=ActiveSheet
' load an output array which is totally blank ( new sheet) with 5 columns and 3 times as many rows as on the orginmal
' 3 times is used becaus if every row has 3 data items there will be 3 times as many rows.
outarr = Range(Cells(1, 1), Cells(lastrow * 3, 5))
'start output index at 2
indi = 2
' copy headers from inarr ( the origal sheet columns 1 4 and 5 (A D and E) to output array
 outarr(1, 1) = inarr(1, 1)
 outarr(1, 2) = inarr(1, 4)
 outarr(1, 3) = inarr(1, 5)
 ' loop through the data on the input sheet ( now in array inarr)
For i = 2 To lastrow
 ' loop through first 3 columns of input sheet
 For j = 1 To 3
  ' check if the cell is blank
  If inarr(i, j) <> "" Then
   ' if the cells is not blank copy the first column and column 4 and 5 ( D  and E) to the output array
   outarr(indi, 1) = inarr(i, j)
   outarr(indi, 2) = inarr(i, 4)
   outarr(indi, 3) = inarr(i, 5)
   ' increment the ouptput array to the next row
   indi = indi + 1
  End If
 Next j
Next i


' write the out put array back to the blank sheet we added earlier
Range(Cells(1, 1), Cells(lastrow * 3, 5)) = outarr






End Sub

One thing to note the array is indexed in the same way as the numerical option in excel. e.g.
inarr( row number, column number)

 

[DataBlake]

I never use vlookup in VBA.

@offthelip
I wish i was there, but i'm stuck in bad old habits.
perhaps you can look at an issue i'm having and get me out of using vlookups? I would really appreciate it.
arrays, matching, and anything thats not vlookup and iferror are foreign to me as well.
https://www.mrexcel.com/forum/excel-questions/1094465-setting-up-vlookup-array.html

 

[inrol]

Thank you again to both BlakeSkate & offthelip. First thoughts are that the version provided by offthelip looks easiest for me to amend. I hope to be able to try to day and will feed back. In the meantime, thanks for all you help and patience.

Inrol.

@offthelip
I wish i was there, but i'm stuck in bad old habits.
perhaps you can look at an issue i'm having and get me out of using vlookups? I would really appreciate it.
arrays, matching, and anything thats not vlookup and iferror are foreign to me as well.
https://www.mrexcel.com/forum/excel-questions/1094465-setting-up-vlookup-array.html

 

[inrol]

I've just plucked up courage to amend the code provided by offthelip to accommodate 39 ID columns and 18 Data columns and it worked perfectly. Thank you again, you've saved me so much time.

Inrol.

Thank you again to both BlakeSkate & offthelip. First thoughts are that the version provided by offthelip looks easiest for me to amend. I hope to be able to try to day and will feed back. In the meantime, thanks for all you help and patience.

Inrol.

 

[offthelip]

I am pleased I was able to help, and to teach somebody the advantages of using variant arrays