VBA or Function

Microsoft Office excel 2007 - upgrade
June 14, 2010 at 11:16:58
Specs: Windows XP
I have an excel sheet that looks like this:
65023 John
64101 Alex
65023 Robert

I need to find a function or VBA language for a macro to have all of the values for one column to return. Vlookup will only give me John for 65023 and I need both names to return. How can I do this?

See More: VBA or Function

Report •

June 14, 2010 at 11:37:20
Look at Response #7 of this thread.

The first part explains how to add a column that will "number" each value in the first column to make it unique so you'll get:

       A          B          C
1  65023 1      65023      John
2  64101 1      64101      Alex
3  65023 2      65023      Robert

You can then construct your VLOOKUP to find the unique values in Column A.

I hope that helps.

Report •

June 14, 2010 at 11:59:24
I'm not sure if that will work for what i am trying to do. Basically I have 700 item numbers and multiple customers own some items and I am trying to find out who those customers are for a list of items. I have a seperate tab that has 70 item numbers and the vlookup is referring to tab one to return the names of the people who own the items. I need to know all the names attached to my item number but vlookup stops the first time it comes to my item number. How do I get it to keep going? Basically on Tab 2 I have:
and on Tab 1 I have every single item number with the owner name next to it. I have 3 people who have 0087 but vlookup will only return the first person.

Report •

June 14, 2010 at 12:39:52
re: "I'm not sure if that will work for what i am trying to do"

What? You don't trust me? ;-)

re: but vlookup will only return the first person

That's why you need to create a unique entry for each item number.

I started with your original example of:

     A          B
1  65023      John
2  64101      Alex
3  65023      Robert

I inserted a new column A, entered this in A1 and dragged it down to A3:

=B1&" "&COUNTIF($B$1:$B1,B1)

That gave me this:

       A          B          C
1  65023 1      65023      John
2  64101 1      64101      Alex
3  65023 2      65023      Robert

You'll notice that each Item Number now has a number appended to it, making it unique so that VLOOKUP can find it.

On Sheet2 I set up this table:

1  65023
2  64101 

In Sheet2!B1 I put this formula:

=VLOOKUP(INDIRECT("A"&ROW()) & " " &COLUMN()-1,Sheet1!$A$1:$C$3,3,0)

I dragged it down and across to C2.

This formula will use the Row and Column functions to append a number to each Item Number before it looks it up.

Therefore it will lookup 65023 1, not 65023. As you drag it across it will lookup 65023 2 and so on.

The table now looks like this:

       A        B          C       
1  65023      John      Robert
2  64101      Alex       #N/A

The #N/A is there because there is no 64101 2.

To eliminate the #N/A, I changed the formula to check for the #N/A error and return "" if a value isn't found.

(VLOOKUP(INDIRECT("A"&ROW()) & " " &COLUMN()-1,Sheet1!$A$1:$C$3,3,0)),"",
VLOOKUP(INDIRECT("A"&ROW()) & " " &COLUMN()-1,Sheet1!$A$1:$C$3,3,0))

This is exactly the same solution as I posted in the other thread, except that I changed the ranges so that it works in Sheet2 to pull data from Sheet1.

You just need to make sure you drag to formula far enough across each Row to make sure you grab all names associated with each item.

If you read the rest of that thread you'll also see a VBA solution that should work for you also. It too will need to be modified to match your ranges.

Report •

Related Solutions

June 14, 2010 at 12:53:37
Thanks, ha no, I apparently can't read today and read my result incorrectly when following the directions. Thanks, I think this should work. :)

Report •

June 14, 2010 at 13:05:39
Let us know if you need any more help.

Report •

Ask Question