Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 788
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 846
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 788
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 788
Default 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

  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 622
Default 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.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
urgent and desperate max power Excel Discussion (Misc queries) 2 December 29th 06 07:14 AM
Desperate max power Excel Discussion (Misc queries) 0 December 29th 06 02:52 AM
Desperate...please help! sas Excel Worksheet Functions 3 January 22nd 06 09:09 PM
In desperate need of help.... mrskitz Excel Discussion (Misc queries) 3 January 13th 06 03:48 PM
Desperate NEED!!!! huntr357 Excel Worksheet Functions 3 April 21st 05 04:47 PM


All times are GMT +1. The time now is 11:25 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"