|
Desperate for help with a VLOOKUP
ok here is the situation on one sheet i have a list of credit accounts
(always changes - not sure which card numbers or how many times different ones will be entered), and with these card numbers, customer enters what they plan to purchase and the amount..sort of looks like this: CARD NUMBER PURCHASE PRICE 12345 CAR 26,000 55599 BOAT 30,000 999122 TV 3,000 55599 CLOTHES 1,200 55599 STEREO 600 999122 DINNER 50 OK, now on the next sheet is a listing of the Card Numbers and I want it to grab all the expected purchases for each card number listed on first sheet, can you use a VLOOKUP to return multiple lines of data...so if my second sheet looks like this: CARD NUMBER PURCHASE 12345 VLOOKUP HERE 55599 VLOOKUP HERE 999122 VLOOKUP HERE as they enter more purchases on different lines on sheet one, how can i make these items appear on sheet two? I need it to go on seperate lines so that i end up with this on sheet 2: CARD NUMBER PURCHASE 12345 CAR 55599 BOAT CLOTHES STEREO 999122 TV DINNER IS THIS POSSIBLE? How can i get it to automatically put in additional lines as sheet one changes? I hope this makes sence. and thank you so much for any help |
Desperate for help with a VLOOKUP
I would solve this problem by performing a data sort and sort on the column
that Card number is in. -- Wag more, bark less "chris" wrote: ok here is the situation on one sheet i have a list of credit accounts (always changes - not sure which card numbers or how many times different ones will be entered), and with these card numbers, customer enters what they plan to purchase and the amount..sort of looks like this: CARD NUMBER PURCHASE PRICE 12345 CAR 26,000 55599 BOAT 30,000 999122 TV 3,000 55599 CLOTHES 1,200 55599 STEREO 600 999122 DINNER 50 OK, now on the next sheet is a listing of the Card Numbers and I want it to grab all the expected purchases for each card number listed on first sheet, can you use a VLOOKUP to return multiple lines of data...so if my second sheet looks like this: CARD NUMBER PURCHASE 12345 VLOOKUP HERE 55599 VLOOKUP HERE 999122 VLOOKUP HERE as they enter more purchases on different lines on sheet one, how can i make these items appear on sheet two? I need it to go on seperate lines so that i end up with this on sheet 2: CARD NUMBER PURCHASE 12345 CAR 55599 BOAT CLOTHES STEREO 999122 TV DINNER IS THIS POSSIBLE? How can i get it to automatically put in additional lines as sheet one changes? I hope this makes sence. and thank you so much for any help |
Desperate for help with a VLOOKUP
Brad, thanks for trying, but unfotunately I cant just use a sort for this,
this second sheet is in a shared area, and i cannot watch it that closely and the people using it are probably not familiar with sort either, i need this to update automatically, and was hoping some type of VLOOKUP or possiblye MATCH, INDEX, who knows what other function might be able to help me out...if you have any other suggestions please let me know..and thanks again "Brad" wrote: I would solve this problem by performing a data sort and sort on the column that Card number is in. -- Wag more, bark less "chris" wrote: ok here is the situation on one sheet i have a list of credit accounts (always changes - not sure which card numbers or how many times different ones will be entered), and with these card numbers, customer enters what they plan to purchase and the amount..sort of looks like this: CARD NUMBER PURCHASE PRICE 12345 CAR 26,000 55599 BOAT 30,000 999122 TV 3,000 55599 CLOTHES 1,200 55599 STEREO 600 999122 DINNER 50 OK, now on the next sheet is a listing of the Card Numbers and I want it to grab all the expected purchases for each card number listed on first sheet, can you use a VLOOKUP to return multiple lines of data...so if my second sheet looks like this: CARD NUMBER PURCHASE 12345 VLOOKUP HERE 55599 VLOOKUP HERE 999122 VLOOKUP HERE as they enter more purchases on different lines on sheet one, how can i make these items appear on sheet two? I need it to go on seperate lines so that i end up with this on sheet 2: CARD NUMBER PURCHASE 12345 CAR 55599 BOAT CLOTHES STEREO 999122 TV DINNER IS THIS POSSIBLE? How can i get it to automatically put in additional lines as sheet one changes? I hope this makes sence. and thank you so much for any help |
Desperate for help with a VLOOKUP
I guess my main question is how can i use a vlookup to return multiple lines
of value..ive used them in the past to search for a value in a range and return a value on the same row as the searched value, but what if that searched value appears on multiple lines in the range, how does one return all the specific values that belong to all instances of the searched value....lol..sorry ive confused myself with this one...I dont think this can be done...but i hope so. "chris" wrote: Brad, thanks for trying, but unfotunately I cant just use a sort for this, this second sheet is in a shared area, and i cannot watch it that closely and the people using it are probably not familiar with sort either, i need this to update automatically, and was hoping some type of VLOOKUP or possiblye MATCH, INDEX, who knows what other function might be able to help me out...if you have any other suggestions please let me know..and thanks again "Brad" wrote: I would solve this problem by performing a data sort and sort on the column that Card number is in. -- Wag more, bark less "chris" wrote: ok here is the situation on one sheet i have a list of credit accounts (always changes - not sure which card numbers or how many times different ones will be entered), and with these card numbers, customer enters what they plan to purchase and the amount..sort of looks like this: CARD NUMBER PURCHASE PRICE 12345 CAR 26,000 55599 BOAT 30,000 999122 TV 3,000 55599 CLOTHES 1,200 55599 STEREO 600 999122 DINNER 50 OK, now on the next sheet is a listing of the Card Numbers and I want it to grab all the expected purchases for each card number listed on first sheet, can you use a VLOOKUP to return multiple lines of data...so if my second sheet looks like this: CARD NUMBER PURCHASE 12345 VLOOKUP HERE 55599 VLOOKUP HERE 999122 VLOOKUP HERE as they enter more purchases on different lines on sheet one, how can i make these items appear on sheet two? I need it to go on seperate lines so that i end up with this on sheet 2: CARD NUMBER PURCHASE 12345 CAR 55599 BOAT CLOTHES STEREO 999122 TV DINNER IS THIS POSSIBLE? How can i get it to automatically put in additional lines as sheet one changes? I hope this makes sence. and thank you so much for any help |
Desperate for help with a VLOOKUP
|
Desperate for help with a VLOOKUP
IS THIS POSSIBLE? How can i get it to automatically put in additional lines as sheet one changes? I hope this makes sence. and thank you so much for any help I don't think vlookup can do this as it just finds the first matching item. And alternate methods such as Match and Index would need a large area of formulas defined to make sure none are missed. (and you would still need a sort afterwards, perhaps) If this is a constantly fluctuating number of rows in your data, how would you match the number of formulas needed on the 2nd page? I suppose a complicated macro could do it, but there is a simple way. I think what you need is a Filter, or several. Possibly run from an automatic macro for easy repeated use, per your 2nd post. You have to carefully create areas for criteria and output when using a filter. But once it is working, it should be just fine. And it can handle any amount of data. I'm not sure if you are trying to return only one card number at once, or many. That would affect how you design this. One at once would be easy. |
| All times are GMT +1. The time now is 03:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com