VBA Macro for Complex VLookup table.

Hi,

I am looking for a macro perform like a vlookup. The table has multiple instances of a number string (with dashes or can contain letters ex. 23764-122, or 30007647, or 311-2 or 12243-123A) which can be active or discontinued at numerous different sites. Vlookup falls short and only returns the first instance and not the other sites which are using the value looked up. I would like it to say if this is Active or Discontinued and then which sites use it x,y,z. Can anyone help!?

Thanks!
Rowdy

Look at Range.Find and .Findnext. There is a pretty good example/explanation here.

If you need more help thn that, please post an example worksheet showing want you want to happen.

I have included an example file.

Sorry, I still don't understand what you want. Are you trying to loop through all the values in column A, find all the equivalent values in column G, then append H and I to B and C for that found value?

If so, I don't understand columns B and C. Rows 8 and 9 look to be identical in column A, but have different B and C values and i don't understand why H11, H17, H24 and H12 are the referenced cells

Yes exactly what you said. Loop through all the values in column A, find ALL instances of that value in column G, and return the values in H and I in columns B and C. (None of those values in the example are correct, just did it myself as a fast example of what/how I want it to look like). Yes row 8 and 9 should indeed show the same values if they are exactly the same.

I want it to correspond like take row 15 for example and lets say it appears 3 times in column G used at 3 different sites and different statuses.
DEF - Discontinued
New york - Active
Los Angeles - Discontinued

Combine these values the way they correspond in a readable fashion. Like separated by commas and spaces. Have the same order correspond like:
DEF, New york, Los Angeles
|| || ||
Discontinued, Active, Discontinued

Column B Column C
DEF, New york, Los Angeles Discontinued, Active, Discontinued
1 2 3 1 2 3 (Just so it follows a flow - Like you read the 2nd Active and you will know it corresponds to New york.)

Please Login or Register  to view this content.

You're THE BEST!!!!!!

Thank you so much!!!!!!!

Glad to help. Please mark this thread as SOLVED, and I appreciate the reputation point.

Done and done!