Alternative for lookup_concat required

Hello guys,

Here I am looking for help again

Please see my attached example file. I need to input a unique ID number in a cell (F3 in my example), and have a set of values which relate to (possibly multiple instances) of the unique ID returned - in my example, H3 should return all entries from column C where the entries in column A match what has been entered in F3. I can get it to return from one row at a time no problem, using INDEX/MATCH, but I need to show all entries in a single cell.

I have tried the wonderful lookup_concat function, which requires the following:

Please Login or Register  to view this content.

to be inserted as a VBA module; however I find that after eg using the data filter, or saving, my workbook takes 30/40 seconds to finish with all "processes", as Excel describes them at the bottom of the page. I'm using a 2.5GH quad-core pc with 4GB ram, and have also tried this on an i3 machine at work, with the same problem, so I'm hoping that someone can help me out here with an alternative.

Many thanks,

Brendan.

Adding code tags around code makes it easier to read and copy

Hey Jacc,

Thanks for the reply. We are talking about approx 1200 rows, by 75 columns.

When you say you used MATCH, do you mean you substituted MATCH for IF in the UDF? I'll give that a shot now and see if it's faster...

And while it would be awesome if the real smart guys came up with an answer, it'll be just as awesome if we can figure it out ourselves


EDIT: I see your attachment and code now - thanks very much. I will report back!

Jacc,

That is fantastic! There is ZERO discernible slow-down. Thank you very much for your help. Looks like you're one of the really smart guys after all

Well this is not the post I wanted to post. Despite the positive reception the above function does not work. Well it seem to work in a sorted list but not in general, which is what I aimed for obviously. This little toxic piece of code is the culprit:

Please Login or Register  to view this content.

.
In a range with scattered cells it will not give the next cell in the range, just the next cell on the sheet. Hence it seems to work in a sorted list.
The correct way of doing it is ofcourse to use the solid For Each loop.

Please Login or Register  to view this content.

I would really appreciate if someone could confirm that I'm on the right track again. I assume it worked so far for you Brendan, otherwise you would have posted, right?
I discovered this as I was recycling this code for another post. I've recycled it before so I have to track down those posts as well and correct them.

Much better code in this version. I'll just store it here so it won't get lost.