Loop through rows and return matching columns

[ezzy85]

I have a worksheet with 2 columns. The first column contains names and the second column contains numbers associated with the name.

How can I specify a name, go through the sheet to find all the occurances of that name and then paste all the numbers associated with that name in another worksheet in a set position.

Thanks in advance

[Rahul Nagar]

Hello Ezzy85,

Try "VLOOKUP" formula.

In case you need any further assistance,please attach the file and i will do the need full.

[ezzy85]

Vlookup only returns the first occurance. How can I handle it if there are 3 occurances, for example?

[Rahul Nagar]

can you please attach a sample file for more details?

[ezzy85]

Here is a sample. The data is in the "data" sheet. I enter a name in "output" and it searches the data for that name and returns all the numbers associated with it in the "output" sheet.
Thanks

[Leith Ross]

Hello ezzy85,

I have added the macro below to the attached workbook. This will find all occurrences of the name in cell "B1" of the "Output" sheet and copy the data for that name back to the "Output" sheet starting at cell "A4". The macro runs automatically when the user enters a name into cell "B1" on the "Output" sheet. If the name isn't found, a message box alerts the user.

Macro Code

Please Login or Register  to view this content.

Worksheet Change Event Code

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[ezzy85]

thats great! thanks so much

[ezzy85]

hi again.
I just realised that the code above is for a static data range. I am trying to change this line:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

but that returns the value of the cell. How can I get it to return the range?
thanks

[Leith Ross]

Hello ezzy85,

Change that line of code to this...

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[ezzy85]

thanks again