VLOOKUP variation to yield multiple text values for same customer not just first text

**joewood** · 01-08-2008, 02:06 PM

I have seen similar Q&A, but they do not work with this particular instance.

I have customers with the same order number repeated if they order several things (text). Each "thing" is a different text.

Traditional VLOOKUP from one spreadsheet to another will only give the first text and some customers ordered up to 5 different texts.

I need the result to show the order number plus the text for each separate text instead of just the first.

As a simple version, for this sheet:

1632 Palguta
1633 JUDY
1634 The Brady's
1634 The Brown's
1634 The Black's
1634 The Lyle's

Currently, the VLOOKUP yields:

1632 Palguta
1633 Judy
1634 The Brady's
1634 The Brady's
1634 The Brady's
1634 The Brady's

And it needs to yield:

1632 Palguta
1633 Judy
1634 The Brady's
1634 The Brown's
1634 The Black's
1634 The Lyle's

I have attached the actual sheets, with the needed result area highlighted in blue.

Thanks very much in advance! It is very much appreciated.

**StephenR** · 01-08-2008, 03:33 PM

You've attached four separate documents and I'm struggling to follow how they all link together.

Where do the order numbers and customers originate, and where should the information end up?

Could you perhaps attached one simplified file summarising this?

**NBVC** · 01-08-2008, 03:40 PM

Try, in F2:

Code:

=IF(ISERR(INDEX(aaaOptions.csv!$F$1:$F$100,SMALL(IF(A2=aaaOptions.csv!$A$1:$A$100,ROW(aaaOptions.csv!$A$1:$A$100)),COUNTIF($A$2:$A2,A2)))),F1,SUBSTITUTE(PROPER(INDEX(aaaOptions.csv!$F$1:$F$100,SMALL(IF(A2=aaaOptions.csv!$A$1:$A$100,ROW(aaaOptions.csv!$A$1:$A$100)),COUNTIF($A$2:$A2,A2)))),"'S","s"))

Note: this formula must be confirmed with CTRL+SHIFT+ENTER keys, not just ENTER...you will see { } brackets appear. Then you can copy down

Also, note that you will have to reinsert the paths in front of each range reference in the formula...as I do not know your path. Then reconfirm with CSE keys.

The ranges in the formula only cover to 100, you may need to revise if you have more data in your lookup sheet.

**joewood** · 01-08-2008, 03:59 PM

WOW! You are a genius!

Thank you so much. It took me an entire day to get it wrong!

When you say

"note that you will have to reinsert the paths in front of each range reference in the formula"

what does this mean? It seems to work fine because I have all sheets opened.

**joewood** · 01-08-2008, 04:00 PM

Stephen-

Thank you very much for looking. I think we have it.

Joe

**StephenR** · 01-08-2008, 04:05 PM

Joe - good news! I expect NBVC means if the files are not open but no doubt all will be revealed...

**NBVC** · 01-08-2008, 04:15 PM

I expect NBVC means if the files are not open but no doubt all will be revealed..

Yeah, I was assuming the other files could be closed.... but if they are all open, then the path will add itself as you close the other files.

Glad it worked!

**joewood** · 01-08-2008, 05:14 PM

You guys are the best! Thanks.

Could you help me with another issue?

I want to create another sheet that pulls over rows from orders on the "!!!New Combined.." sheet. I want to lay them down in the same order, but want to skip all of the rows that say "Tax" or "Shipping" in column B.

Can I do this with a formula and not have to do something like a sort - delete?

**NBVC** · 01-08-2008, 05:55 PM

Try this formula in A2 of your new sheet:

Code:

=IF(ROWS($A$1:$A1)>SUMPRODUCT((Sheet1!$B$2:$B$100<>"Tax")*(Sheet1!$B$2:$B$100<>"Shipping")*(Sheet1!$B$2:$B$100<>"")),"",INDEX(Sheet1!A$2:A$100,SMALL(IF((Sheet1!$B$2:$B$100<>"Tax")*(Sheet1!$B$2:$B$100<>"Shipping")*(Sheet1!$B$2:$B$100<>""),ROW(Sheet1!A$2:A$100)-ROW(Sheet1!A$2)+1),ROWS($A$1:$A1))))

again you must adjust ranges to ensure all data is captured and you must confirm formula with CTRL+SHIFT+ENTER before copying to the right and down as far as necessary.

**joewood** · 01-09-2008, 01:14 PM

Thank you very, very much!!! It worked great.

**joewood** · 01-09-2008, 02:36 PM

NBVC-

Could I ask your name so I could address you properly?

I ran into a small snag. In the "Options" sheet, some people do not have to fill in options. So, it may look something like:

123 Smith
124 Jones
125 Stevens
125 Jason
126 Tom
128 Ralph
129 May
129 Jerry

On the combo sheet, everything is beautiful with what you have helped with. But, for #127, even though he has not selected an option, so his name does not appear, he is automatically being assigned "Tom".

I would like, instead, for this filed to be left blank. Can you help?

**NBVC** · 01-09-2008, 02:52 PM

NBVC is fine..

Try replacing previous formula in F2 with this one:

Code:

=IF(ISNA(MATCH(A2,aaaOptions.csv!$A$1:$A$100,0)),"",IF(ISERR(INDEX(aaaOptions.csv!$F$1:$F$99,SMALL(IF(A2=aaaOptions.csv!$A$1:$A$99,ROW(aaaOptions.csv!$A$1:$A$99)),COUNTIF($A$2:$A2,A2)))),F1,SUBSTITUTE(PROPER(INDEX(aaaOptions.csv!$F$1:$F$99,SMALL(IF(A2=aaaOptions.csv!$A$1:$A$99,ROW(aaaOptions.csv!$A$1:$A$99)),COUNTIF($A$2:$A2,A2)))),"'S","s")))

reconfirm with CSE keys and copy down... adjust ranges if needed too!

**joewood** · 01-10-2008, 09:34 AM

Thanks! You are the best.

Thread: VLOOKUP variation to yield multiple text values for same customer not just first text

Thread Tools

Display

VLOOKUP variation to yield multiple text values for same customer not just first text

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions