VBA help to Vlookup a single value in a list and return results while ignoring blank cells

M

MMC78

New Member
Joined
May 26, 2010
Messages
12
Hi,

I've been messing about with this and can't quite seem to get it right (I also think I'm making things much more complicated than they need to be!).

Below is an example of the data I have.....(the real data has many more ref and ID numbers). I want to macro this as this is a task I want to run daily, and the data will change on a day to day basis.

[TABLE="class: grid, width: 550"]
<tbody>[TR]
[TD="width: 64"]Ref[/TD]
[TD="width: 64"]ID1[/TD]
[TD="width: 64"]ID2[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]Ref2[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]305[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]add data here[/TD]
[TD]add data here[/TD]
[TD]add data here[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]324[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]434, 543[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD]569[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]305[/TD]
[TD]126[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]377[/TD]
[TD]432[/TD]
[TD]588[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]126[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]427[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]512[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]512[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]569[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]312[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]324[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]453, 478[/TD]
[TD]126[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]569[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]569[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]377[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]432[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]432[/TD]
[TD]588[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Take the list of Ref numbers from column (Ref) and remove duplicates in column (Ref2) as above.

Then for each Ref in Ref2 column run a lookup for that Ref in column (Ref) and return all IDs from columns (ID1 and ID2), while ignoring blanks and duplicates, and to paste them in the row for each ref (see above). I'm not fussed if the data is pasted in individual cells or is a string in a single cell - see column (Ref2) ref 4 and 5 above for a simple example of how this should look.
 
Last edited:

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
MMC78 said:
Hi,

I've been messing about with this and can't quite seem to get it right (I also think I'm making things much more complicated than they need to be!).

Below is an example of the data I have.....(the real data has many more ref and ID numbers). I want to macro this as this is a task I want to run daily, and the data will change on a day to day basis.

[TABLE="class: grid, width: 550"]
<tbody>[TR]
[TD="width: 64"]Ref[/TD]
[TD="width: 64"]ID1[/TD]
[TD="width: 64"]ID2[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]Ref2[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]305[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]add data here[/TD]
[TD]add data here[/TD]
[TD]add data here[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]324[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]434, 543[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD]569[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]305[/TD]
[TD]126[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]377[/TD]
[TD]432[/TD]
[TD]588[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]126[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]427[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]512[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]512[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]569[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]312[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]324[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]453, 478[/TD]
[TD]126[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]569[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]569[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]377[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]432[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]432[/TD]
[TD]588[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Take the list of Ref numbers from column (Ref) and remove duplicates in column (Ref2) as above.

Then for each Ref in Ref2 column run a lookup for that Ref in column (Ref) and return all IDs from columns (ID1 and ID2), while ignoring blanks and duplicates, and to paste them in the row for each ref (see above). I'm not fussed if the data is pasted in individual cells or is a string in a single cell - see column (Ref2) ref 4 and 5 above for a simple example of how this should look.
Click to expand...

Anyone??/
 
Upvote 0
MMC78,

Not sure if the below code will deal with all eventualities but I believe it deals with your example data.

Code: 
Sub RefIDs()


Range("G1") = "Ref2"
R2r = 1
lr = Cells(Rows.Count, "A").End(xlUp).Row
For Each Cell In Range("A2:A" & lr)
    If Cell <> PrevCell Then
        R2r = R2r + 1
        Range("G" & R2r) = Cell
        PrevCell = Cell
        NewRef = "Y"
        IDOset = 1
    End If
    
      For CellOset = 1 To 2
            If Not Cell.Offset(0, CellOset) = "" Then
             If NewRef = "Y" Then
                    Range("G" & R2r).Offset(0, IDOset) = Cell.Offset(0, CellOset)
                    PrevID = Cell.Offset(0, CellOset)
                     IDOset = IDOset + 1
                     NewRef = "N"
                     Else
              
                      If Not Cell.Offset(0, CellOset) = PrevID Then
                        Range("G" & R2r).Offset(0, IDOset) = Cell.Offset(0, CellOset)
                        PrevID = Cell.Offset(0, CellOset)
                        IDOset = IDOset + 1
                     End If
                End If
            End If
        
      Next CellOset
        
Next Cell


End Sub

Hope that helps.
 
Upvote 0
Snakehips said:
MMC78,

Not sure if the below code will deal with all eventualities but I believe it deals with your example data.

Code: 
Sub RefIDs()


Range("G1") = "Ref2"
R2r = 1
lr = Cells(Rows.Count, "A").End(xlUp).Row
For Each Cell In Range("A2:A" & lr)
    If Cell <> PrevCell Then
        R2r = R2r + 1
        Range("G" & R2r) = Cell
        PrevCell = Cell
        NewRef = "Y"
        IDOset = 1
    End If
    
      For CellOset = 1 To 2
            If Not Cell.Offset(0, CellOset) = "" Then
             If NewRef = "Y" Then
                    Range("G" & R2r).Offset(0, IDOset) = Cell.Offset(0, CellOset)
                    PrevID = Cell.Offset(0, CellOset)
                     IDOset = IDOset + 1
                     NewRef = "N"
                     Else
              
                      If Not Cell.Offset(0, CellOset) = PrevID Then
                        Range("G" & R2r).Offset(0, IDOset) = Cell.Offset(0, CellOset)
                        PrevID = Cell.Offset(0, CellOset)
                        IDOset = IDOset + 1
                     End If
                End If
            End If
        
      Next CellOset
        
Next Cell


End Sub

Hope that helps.
Click to expand...

Hi Snakehips,

Yes, that works great on my example data. I've tried it on the live data and it is working for most records, but no issue there as this is a great starting point for me so I can tidy up from here.

Thanks for you assistance!
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Counting formula results and text data while ignoring blank cells
2
Replies
16
Views
756
Darren_workforce
D
F
  • Solved
Listing All corresponding dates for a specific value
Replies
3
Views
336
Fluff
Fluff
R
  • Solved
VBA: Merging rows after a particular row, based off of matching entries in column
2
Replies
19
Views
931
Georgiboy
Georgiboy
S
  • Question Question
VBA: Count the number of times a value appears in a column
Replies
0
Views
839
sr1111
S
R
  • Question Question
Help! VBA to loop through a list containing data references to update forecast sheets automatically
Replies
3
Views
469
Domenic
D

Forum statistics

Threads
1,223,270
Messages
6,171,103
Members
452,379
Latest member
IainTru

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