VLookUp To Return Multiple Values

I need to do a VLookUp and return multiple results into 1 cell. For example, I have the following information. (assume the number are stock codes or something)

Red	1234
Red	2345
Red	4321
Red	5432
Blue	7589
Blue	8598
Blue	7858
Green	4659
Green	9587
Green	6859

The above would be on one sheet, then on another sheet, in Column A, I'd have Red, Blue & Green in their own cells. Then in Column B, I would do a VLookUp using the adjacent cell as it's "target" and would want to result all possible results, not just the first

IE, Cell A1 would say Red, and I'd want cell B1 to return "1234, 2345, 4321, 5432" as its value

Is this possible?

TIA,

Mat

I know how a VLookUp works (I don't mean that condescendingly BTW), usually a VLookUp only return the first value. Or at least a modification of it that I have used in the past. I need something to return multiple cells into one cell....

May be pivot is the answer.

Chk the attached file.Pivot Solution.xlsx

Thanks,

I'll look into that. My "problem" is that (on this project) I have 238 rows of data - all with unique references (ie, red, blue, green) and I have another sheet to filter through containing 44,238 rows of data! I'd like to have it as automated as possible :D

MAy be if you put ur actual sheet without confidential data, then probably we can assist u.

Rgds
AT

will do. I'll post it in a mo

Ok,

I've attached some sample data.

Sheet 1 contains approximately 1,000 rows of data.

Sheet 2 contains a handful of part numbers, of which I need to return all "Part Codes" that apply to that number. This needs to be returned on 1 single cell.

Thank you

Originally Posted by TMShucks

I never, for one moment, interpreted that as condescending

VLOOKUP doesn't just usually return the first matching value, it always returns the first matching value, and only the first matching value. Shame really, because a lot of people, just like you, would like it to return all the matching values.

The Pivot Table option maybe sufficient for your needs.

I have updated the example provided by brandedadnan with some alternative views.

Regards, TMS

Thanks for this example.

The data I'm currently working on is a very small amount. 238 product numbers is just clearance stock. I usually deal with data containing anywhere from 2,000 to 15,000 rows of unique Product Numbers, and have to look through a file containing anywhere around 20,000 rows of data to get Product Codes. The pivot table idea looks nice and simple, but really not suitable or efficient unfortunately

Maybe I'm asking too much.

I think in that case, u need vba.., cause vlook up will not work here...

hhhmmm........... is this something this Forum can provide support with?

Thanks

It looks like it's time to speak to my Director and get Access or VBA and a training course booked. We're growing rapidly, and just taken some stuff in-house that we used to contract out.

We would normally provide the data in separate rows (ie, as shown in my first example above with Red in 4 rows). I think that our 3rd party company must have consolidated all info into one cell as that is how it's required for upload purposes.

Anyway......

Hi TMS -

This would give an error due to large strings I believe.
this one should work I think

Please Login or Register  to view this content.

event

Thank you very much.

Originally Posted by TMShucks

@event21: thank you. It does indeed generate an error. I've tried (and, again, slightly modified your code) and it works perfectly. Interestingly, or not so, you can't resize the column to fit the collated data ... obviously, again, due to the size of the text string.

Please Login or Register  to view this content.

You Sir, are a star

Thank you to all that have helped.

Sorry to bump such an old thread.

I thought I had this working, but still getting no success. When I run a Macro, it comes up with an error. When I open the debug window, this is the line that is highlighted

Please Login or Register  to view this content.

I really really do need to get this working now. I had a file the other week with over 600,000 rows of data. I split it up into sections of around 20,000 and did some copying and pasting until I had condensed down. The result - what I believe to be RSI. My fingers are killing from keyboard shortcuts etc.

If it helps, I can start a new thread, and start "afresh" with some more sample data and how exactly I work with it at the moment.

Thanks. Having gone through some of my workload, 1 catalogue alone has 840,000 rows of data. I've estimated I can tidy up 30,000 rows a day without any other distractions, so that would take 28 days to just tidy up. I then need to do other things before making it live etc...

New thread coming.

You originally asked for a formula solution, so I've taken your attachment from Post#8 and put this formula in C2 of Sheet1:

=IF(A2="","-",A2&"_"&COUNTIF(A$2:A2,A2))

Copy this down to the bottom of your data (or beyond - the hyphens indicate where the formula is active)

Then in Sheet2 you can have this formula in B2:

=IFERROR(INDEX(Sheet1!$B:$B,MATCH(Sheet2!$A2&"_"&COLUMNS($B:B),Sheet1!$C:$C,0)),"")

This will give you the Part code in one cell - copy the formula across to get the multiple part codes in adjacent cells. Copy the formula down for the other part numbers.

Hope this helps.

Pete