Forum Statistics
- Forum Members:
- Total Threads:
- Total Posts: 13
There are 1 users currently browsing forums.
|
 |
01-08-2008, 01:06 PM
|
|
Registered User
|
|
Join Date: 08 Jan 2008
Posts: 10
|
|
|
VLOOKUP variation to yield multiple text values for same customer not just first text
Please Register to Remove these Ads
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.
|
01-08-2008, 02:33 PM
|
|
Forum Guru
|
|
Join Date: 26 Aug 2007
Location: London
Posts: 2,210
|
|
|
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?
|
01-08-2008, 02:40 PM
|
 |
Forum Guru
|
|
Join Date: 06 Dec 2006
Location: Mississauga, CANADA
MS Office Version:2003 & 2007
Posts: 18,517
|
|
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.
__________________
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to my reputation by clicking the icon next to the Post # in the bar above my avatar (picture) in this post.
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
|
01-08-2008, 02:59 PM
|
|
Registered User
|
|
Join Date: 08 Jan 2008
Posts: 10
|
|
|
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.
|
01-08-2008, 03:00 PM
|
|
Registered User
|
|
Join Date: 08 Jan 2008
Posts: 10
|
|
|
Stephen-
Thank you very much for looking. I think we have it.
Joe
|
01-08-2008, 03:05 PM
|
|
Forum Guru
|
|
Join Date: 26 Aug 2007
Location: London
Posts: 2,210
|
|
|
Joe - good news! I expect NBVC means if the files are not open but no doubt all will be revealed...
|
01-08-2008, 03:15 PM
|
|
Forum Guru
|
|
Join Date: 06 Dec 2006
Location: Mississauga, CANADA
MS Office Version:2003 & 2007
Posts: 18,517
|
|
Quote:
|
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!
__________________
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to my reputation by clicking the icon next to the Post # in the bar above my avatar (picture) in this post.
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
|
01-08-2008, 04:14 PM
|
|
Registered User
|
|
Join Date: 08 Jan 2008
Posts: 10
|
|
|
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?
|
01-08-2008, 04:55 PM
|
|
Forum Guru
|
|
Join Date: 06 Dec 2006
Location: Mississauga, CANADA
MS Office Version:2003 & 2007
Posts: 18,517
|
|
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.
__________________
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to my reputation by clicking the icon next to the Post # in the bar above my avatar (picture) in this post.
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
|
01-09-2008, 12:14 PM
|
|
Registered User
|
|
Join Date: 08 Jan 2008
Posts: 10
|
|
|
Thank you very, very much!!! It worked great.
|
01-09-2008, 01:36 PM
|
|
Registered User
|
|
Join Date: 08 Jan 2008
Posts: 10
|
|
|
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?
|
01-09-2008, 01:52 PM
|
|
Forum Guru
|
|
Join Date: 06 Dec 2006
Location: Mississauga, CANADA
MS Office Version:2003 & 2007
Posts: 18,517
|
|
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!
__________________
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to my reputation by clicking the icon next to the Post # in the bar above my avatar (picture) in this post.
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
|
01-10-2008, 08:34 AM
|
|
Registered User
|
|
Join Date: 08 Jan 2008
Posts: 10
|
|
|
Thanks! You are the best.
|
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
|
| Thread Tools |
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
