Lookup numbers and concatenate them in a different cell

OK, this might not be what you were looking for or expecting ... but maybe just use a Pivot Table. You can use the ConCatAll UDF (by TigerAvatar) to get the comma separated list.

And you can add buttons to switch between Table 2 and Table 3.

Relatively easy to implement:

Option Explicit

Private Sub btnRefresh_Click()

ActiveSheet.PivotTables("PivotTable2"). _
            PivotCache. _
                Refresh

End Sub

Private Sub btnTable2_Click()
    
ActiveSheet.PivotTables("PivotTable2"). _
            PivotFields("Master Product ID"). _
                ClearAllFilters

End Sub

Private Sub btnTable3_Click()
    
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Master Product ID")
    .PivotItems("1").Visible = False
    .PivotItems("2").Visible = False
    .PivotItems("3").Visible = False
    .PivotItems("4").Visible = False
    .PivotItems("16").Visible = False
    .PivotItems("43").Visible = False
    .PivotItems("85").Visible = False
    .PivotItems("89").Visible = False
End With

End Sub

Regards, TMS

Thanks for the rep.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

Hi TMS,

Thank you so much for your help! I got an error message while creating a pivot table for 25,000 rows, which doesn't let enter "Linked SKUs" into "Column labels". Can you suggest anything to fix this error?

The error reads: "Excel cannot complete this task with available resources. choose less data or close other applications."

I would really be appreciative if you help to resolve the error. I'll mark the thread as solved when you or someone, I hope, will help me to fix the error, so I can apply your code.

thank you!

Not sure if it's the same error but there are limits on the size of Pivot Tables which I suspect that you have breached. See the MS Article: http://support.microsoft.com/kb/211517

Looks like we are back to square one.

I would suggest that you start a new thread, providing a link back to this thread. That way you can generate new interest from the community and provide background information. It will also not be considered a duplicate thread.

It's a shame but you might need a bespoke VBA solution after all.


Regards, TMS