multiple returns for vlookup/match/etc

purelogic · Apr 26th, 2002, 12:39 PM

i Have a range, A2:D250 on a sheet named "value". In this range, column A lists client names, and column D lists items (ie: Apples). On a sheet named "data" (in the same workbook),I need to lookup all clients who bought apples. But i don't neeed a count, i need the actual client names copied over and into "data". So if 6 people bought apples, I need to those names to copy over. please help!

croweld89 · Apr 26th, 2002, 12:46 PM

Could you not just use an autofilter in the other workbook, and then if needed copy and paste the results to another page?

For Example, products purchased on the second sheet is in column A with client's info in suceeding columns on the same row. Run Autofilter for column A and select apples in the drop down box.

I'm not sure if you're looking for a specific formula, but I hope it might help.

-Dave

purelogic · Apr 26th, 2002, 12:49 PM

not, that i can see... the Data page actually contains a lot more, and does a mail merge based off of other data. So the return of Clients buying Apples is supposed to be a self-updating paragraph on a form letter...

Mark W. · Apr 26th, 2002, 01:18 PM

See my 2002-04-25 15:38 posting at http://www.mrexcel.com/board/viewtop...c=6280&forum=2. You could employ this same approach.

purelogic · Apr 26th, 2002, 02:07 PM

thanks for your reply Mark, but the problem is how could the approach you suggest be automated to work with the mail merge? It's not that the Data... solution was not a viable solution to the one off, but that i couldn't see a way to make it work with an excel to excel mail merge. ....

Mark W. · Apr 26th, 2002, 02:10 PM

Quote:

On 2002-04-26 13:07, purelogic wrote:
thanks for your reply Mark, but the problem is how could the approach you suggest be automated to work with the mail merge? It's not that the Data... solution was not a viable solution to the one off, but that i couldn't see a way to make it work with an excel to excel mail merge. ....

I don't see the problem... the Get External Data approach would produce your list of 6 names (as requested). What more did you need?

purelogic · Apr 26th, 2002, 02:36 PM

Sorry, was not clear there... The Clients and Apples is just one example. The mail merge will be going through (with no user intervention) and spin off 278 letters, all customized with data from the spreadsheet. Clients who purchased Apples, IBMs, Toshibas, HPs, Compaqs, Sonys, etc-- all these will have to do the same thing requested for Clients-Apples. The solution you suggested was good to get the list for Clients-Apples, but I cannot get it to fit in with the automated mail merge aspect, eg: too much user intervention. Hope that clarifies?

Mark W. · Apr 26th, 2002, 02:39 PM

Quote:

On 2002-04-26 13:36, purelogic wrote:
Sorry, was not clear there... The Clients and Apples is just one example. The mail merge will be going through (with no user intervention) and spin off 278 letters, all customized with data from the spreadsheet. Clients who purchased Apples, IBMs, Toshibas, HPs, Compaqs, Sonys, etc-- all these will have to do the same thing requested for Clients-Apples. The solution you suggested was good to get the list for Clients-Apples, but I cannot get it to fit in with the automated mail merge aspect, eg: too much user intervention. Hope that clarifies?

Ahh, you must have switched horses in the middle of the stream. I can't find any reference to mail merge in your original inquiry. Perhaps you need to consult the Excel Help Index topic for "Use Microsoft Excel data as a mailing list in Word".

[ This Message was edited by: Mark W. on 2002-04-26 13:40 ]

Aladin Akyurek · Apr 26th, 2002, 03:15 PM

For a formula-based retrieval of multiple lookup values, see my contrib at:

http://www.mrexcel.com/board/viewtop...c=3146&forum=2

purelogic · Apr 26th, 2002, 03:28 PM

Hi Mark. No, in the first reply (made before you posted), i had specified mail merge.

It's an excel-to-excel merge letter, not via word -- there are too many calculations to use word. (actually, not true, it's just that why bother to use word when all the work is already done in excel?)
Thank you for your suggestions though-- they have given me food for thought on another project under construction.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 26th, 2002, 12:39 PM	#1
purelogic New Member Join Date: Apr 2002 Posts: 5	i Have a range, A2:D250 on a sheet named "value". In this range, column A lists client names, and column D lists items (ie: Apples). On a sheet named "data" (in the same workbook),I need to lookup all clients who bought apples. But i don't neeed a count, i need the actual client names copied over and into "data". So if 6 people bought apples, I need to those names to copy over. please help!

Apr 26th, 2002, 12:46 PM	#2
croweld89 New Member Join Date: Mar 2002 Posts: 34	Could you not just use an autofilter in the other workbook, and then if needed copy and paste the results to another page? For Example, products purchased on the second sheet is in column A with client's info in suceeding columns on the same row. Run Autofilter for column A and select apples in the drop down box. I'm not sure if you're looking for a specific formula, but I hope it might help. -Dave

Apr 26th, 2002, 12:49 PM	#3
purelogic New Member Join Date: Apr 2002 Posts: 5	not, that i can see... the Data page actually contains a lot more, and does a mail merge based off of other data. So the return of Clients buying Apples is supposed to be a self-updating paragraph on a form letter...

Apr 26th, 2002, 01:18 PM	#4
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	See my 2002-04-25 15:38 posting at http://www.mrexcel.com/board/viewtop...c=6280&forum=2. You could employ this same approach.

Apr 26th, 2002, 02:07 PM	#5
purelogic New Member Join Date: Apr 2002 Posts: 5	thanks for your reply Mark, but the problem is how could the approach you suggest be automated to work with the mail merge? It's not that the Data... solution was not a viable solution to the one off, but that i couldn't see a way to make it work with an excel to excel mail merge. ....

Apr 26th, 2002, 02:36 PM	#7
purelogic New Member Join Date: Apr 2002 Posts: 5	Sorry, was not clear there... The Clients and Apples is just one example. The mail merge will be going through (with no user intervention) and spin off 278 letters, all customized with data from the spreadsheet. Clients who purchased Apples, IBMs, Toshibas, HPs, Compaqs, Sonys, etc-- all these will have to do the same thing requested for Clients-Apples. The solution you suggested was good to get the list for Clients-Apples, but I cannot get it to fit in with the automated mail merge aspect, eg: too much user intervention. Hope that clarifies?

Apr 26th, 2002, 03:15 PM	#9
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,319	For a formula-based retrieval of multiple lookup values, see my contrib at: http://www.mrexcel.com/board/viewtop...c=3146&forum=2

Apr 26th, 2002, 03:28 PM	#10
purelogic New Member Join Date: Apr 2002 Posts: 5	Hi Mark. No, in the first reply (made before you posted), i had specified mail merge. It's an excel-to-excel merge letter, not via word -- there are too many calculations to use word. (actually, not true, it's just that why bother to use word when all the work is already done in excel?) Thank you for your suggestions though-- they have given me food for thought on another project under construction.