Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 32
Default Vlookup for multiple row data

I need to create a lookup formula that will find all employees for a given
store location. My data is organized as follows:

Store # Employee name
10 Bob
10 Jane
10 Kelly
12 Ted
12 David
12 James
12 Carrie
16 Joe
16 Kristin
16 Al
16 Nick
16 John

The obstacle I've encountered with a VLookup is that if I look for Store
#12, my formula only returns the first employee for store #12. I need to
create a formula that will extract all the employees, then create a new table
with that data.

Help!
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,574
Default Vlookup for multiple row data

I would use a pivot table to summarize data by Store #.

For more info on pivot tables, see he
http://www.cpearson.com/excel/pivots.htm

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"David B" wrote:

I need to create a lookup formula that will find all employees for a given
store location. My data is organized as follows:

Store # Employee name
10 Bob
10 Jane
10 Kelly
12 Ted
12 David
12 James
12 Carrie
16 Joe
16 Kristin
16 Al
16 Nick
16 John

The obstacle I've encountered with a VLookup is that if I look for Store
#12, my formula only returns the first employee for store #12. I need to
create a formula that will extract all the employees, then create a new table
with that data.

Help!

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 32
Default Vlookup for multiple row data

Dave,

Thanks for your quick reply and excellent suggestion...unfortunately, my
ultimate use for this spreadsheet for for implimentation in a Crystal
Xcelsius dashboard, and Xcelsius will not work with a pivot table (at least
not that I can determine)...

I'm trying to work with some combination of Match and Index
functions..."Offset" is also not compatible with Xcelsius...

"Dave F" wrote:

I would use a pivot table to summarize data by Store #.

For more info on pivot tables, see he
http://www.cpearson.com/excel/pivots.htm

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"David B" wrote:

I need to create a lookup formula that will find all employees for a given
store location. My data is organized as follows:

Store # Employee name
10 Bob
10 Jane
10 Kelly
12 Ted
12 David
12 James
12 Carrie
16 Joe
16 Kristin
16 Al
16 Nick
16 John

The obstacle I've encountered with a VLookup is that if I look for Store
#12, my formula only returns the first employee for store #12. I need to
create a formula that will extract all the employees, then create a new table
with that data.

Help!

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,574
Default Vlookup for multiple row data

Can;t you just sort by Store #?
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"David B" wrote:

Dave,

Thanks for your quick reply and excellent suggestion...unfortunately, my
ultimate use for this spreadsheet for for implimentation in a Crystal
Xcelsius dashboard, and Xcelsius will not work with a pivot table (at least
not that I can determine)...

I'm trying to work with some combination of Match and Index
functions..."Offset" is also not compatible with Xcelsius...

"Dave F" wrote:

I would use a pivot table to summarize data by Store #.

For more info on pivot tables, see he
http://www.cpearson.com/excel/pivots.htm

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"David B" wrote:

I need to create a lookup formula that will find all employees for a given
store location. My data is organized as follows:

Store # Employee name
10 Bob
10 Jane
10 Kelly
12 Ted
12 David
12 James
12 Carrie
16 Joe
16 Kristin
16 Al
16 Nick
16 John

The obstacle I've encountered with a VLookup is that if I look for Store
#12, my formula only returns the first employee for store #12. I need to
create a formula that will extract all the employees, then create a new table
with that data.

Help!

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 32
Default Vlookup for multiple row data

The data I will be working with will be sorted by store #; But I am working
with 75 or so stores, with 10-15 employees per store. So, I need to create a
formula that will "extract" all employees for a given store, and place the
data in a new worksheet.

My expertise with Lookup formulas is limited. I understand how to use
"Vlookup" to select a store and return the requested data, but not when there
are multiple rows of data with the same store number.

"Dave F" wrote:

Can;t you just sort by Store #?
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"David B" wrote:

Dave,

Thanks for your quick reply and excellent suggestion...unfortunately, my
ultimate use for this spreadsheet for for implimentation in a Crystal
Xcelsius dashboard, and Xcelsius will not work with a pivot table (at least
not that I can determine)...

I'm trying to work with some combination of Match and Index
functions..."Offset" is also not compatible with Xcelsius...

"Dave F" wrote:

I would use a pivot table to summarize data by Store #.

For more info on pivot tables, see he
http://www.cpearson.com/excel/pivots.htm

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"David B" wrote:

I need to create a lookup formula that will find all employees for a given
store location. My data is organized as follows:

Store # Employee name
10 Bob
10 Jane
10 Kelly
12 Ted
12 David
12 James
12 Carrie
16 Joe
16 Kristin
16 Al
16 Nick
16 John

The obstacle I've encountered with a VLookup is that if I look for Store
#12, my formula only returns the first employee for store #12. I need to
create a formula that will extract all the employees, then create a new table
with that data.

Help!



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Vlookup for multiple row data

Perhaps this could be an option ..

Source data in Sheet1's cols A and B, from row2 down

In Sheet2,
Assume Store # will be input in B1

In A2:
=IF(Sheet1!A2="","",IF(Sheet1!A2=$B$1,ROW(),""))
Leave A1 empty

In B2:
=IF(ROW(A1)COUNT(A:A),"",INDEX(Sheet1!B:B,SMALL(A :A,ROW(A1))))
Select A2:B2, copy down to cover the max expected extent of data in Sheet1.
Hide away col A. Col B returns the required results all neatly bunched at the
top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"David B" wrote:
Dave,

Thanks for your quick reply and excellent suggestion...unfortunately, my
ultimate use for this spreadsheet for for implimentation in a Crystal
Xcelsius dashboard, and Xcelsius will not work with a pivot table (at least
not that I can determine)...

I'm trying to work with some combination of Match and Index
functions..."Offset" is also not compatible with Xcelsius...

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 32
Default Vlookup for multiple row data

Max,

Thanks for your reply; again an excellent suggestion. But, Xcelsius is not
able to use "row" functions. I had previously run into this limitation with
another spreadsheet.

"Max" wrote:

Perhaps this could be an option ..

Source data in Sheet1's cols A and B, from row2 down

In Sheet2,
Assume Store # will be input in B1

In A2:
=IF(Sheet1!A2="","",IF(Sheet1!A2=$B$1,ROW(),""))
Leave A1 empty

In B2:
=IF(ROW(A1)COUNT(A:A),"",INDEX(Sheet1!B:B,SMALL(A :A,ROW(A1))))
Select A2:B2, copy down to cover the max expected extent of data in Sheet1.
Hide away col A. Col B returns the required results all neatly bunched at the
top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"David B" wrote:
Dave,

Thanks for your quick reply and excellent suggestion...unfortunately, my
ultimate use for this spreadsheet for for implimentation in a Crystal
Xcelsius dashboard, and Xcelsius will not work with a pivot table (at least
not that I can determine)...

I'm trying to work with some combination of Match and Index
functions..."Offset" is also not compatible with Xcelsius...

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Vlookup for multiple row data

Sad to hear it's not an option, David. Probably you would have earlier ruled
out using an autofilter on the store# col as well? Just a thought.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"David B" wrote:
Max,
Thanks for your reply; again an excellent suggestion. But, Xcelsius is not
able to use "row" functions. I had previously run into this limitation with
another spreadsheet.

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,574
Default Vlookup for multiple row data

Why can;t you use Max's suggestion and paste the values? Do you need to
retain the formulas?
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Max" wrote:

Sad to hear it's not an option, David. Probably you would have earlier ruled
out using an autofilter on the store# col as well? Just a thought.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"David B" wrote:
Max,
Thanks for your reply; again an excellent suggestion. But, Xcelsius is not
able to use "row" functions. I had previously run into this limitation with
another spreadsheet.

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 32
Default Vlookup for multiple row data

Crystal Xcelsius is a dynamic graphing/dashboard application that takes the
underlying excel data (and formulas) and uses it to produce really nice
interactive displays....so I actually have to build the underlying formulas
into excel, which ultimately gets embedded in Xcelsius and provides the
dynamic content.

Usually, all of the suggestions here would work very well, but I'm having to
try to build the excel sheet to ultimately work with Xcelsius....and Xcelsius
support is pretty poor (unless you own their really expensive "enterprise"
product!)

"Dave F" wrote:

Why can;t you use Max's suggestion and paste the values? Do you need to
retain the formulas?
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Max" wrote:

Sad to hear it's not an option, David. Probably you would have earlier ruled
out using an autofilter on the store# col as well? Just a thought.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"David B" wrote:
Max,
Thanks for your reply; again an excellent suggestion. But, Xcelsius is not
able to use "row" functions. I had previously run into this limitation with
another spreadsheet.



  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,574
Default Vlookup for multiple row data

Sounds like you need a different tool than Xcelsius then.
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"David B" wrote:

Crystal Xcelsius is a dynamic graphing/dashboard application that takes the
underlying excel data (and formulas) and uses it to produce really nice
interactive displays....so I actually have to build the underlying formulas
into excel, which ultimately gets embedded in Xcelsius and provides the
dynamic content.

Usually, all of the suggestions here would work very well, but I'm having to
try to build the excel sheet to ultimately work with Xcelsius....and Xcelsius
support is pretty poor (unless you own their really expensive "enterprise"
product!)

"Dave F" wrote:

Why can;t you use Max's suggestion and paste the values? Do you need to
retain the formulas?
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Max" wrote:

Sad to hear it's not an option, David. Probably you would have earlier ruled
out using an autofilter on the store# col as well? Just a thought.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"David B" wrote:
Max,
Thanks for your reply; again an excellent suggestion. But, Xcelsius is not
able to use "row" functions. I had previously run into this limitation with
another spreadsheet.

  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,081
Default Vlookup for multiple row data

Treat your data as a database, then on the sheet where you want the filtered
data to appear, go to Data-Import External Data-New Database Query

If you walk through the steps there, you'll see how you can identify the
Excel file as the database, and the range of data as a table. Use the help
file in Query to learn how to create a parameter query linked to a cell, and
plan for that cell to hold a store number. Once you've created your query
and identified the cell, you can change the cell value and run the query.
It'll bring in all the pertinent data



"David B" wrote:

I need to create a lookup formula that will find all employees for a given
store location. My data is organized as follows:

Store # Employee name
10 Bob
10 Jane
10 Kelly
12 Ted
12 David
12 James
12 Carrie
16 Joe
16 Kristin
16 Al
16 Nick
16 John

The obstacle I've encountered with a VLookup is that if I look for Store
#12, my formula only returns the first employee for store #12. I need to
create a formula that will extract all the employees, then create a new table
with that data.

Help!

  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 32
Default Vlookup for multiple row data

Duke,

Thank you for your input....unfortunately, because of some limitations of
Crystal Xcelsius (the application this excel spreadsheet "feeds") I have to
limit my data manipulation to formulas I can embed in the actual file;
Vlookup and Hlookup formulas are generally very helpful, but the data I need
to manipulate for this application is throwing me a curve ball....

"Duke Carey" wrote:

Treat your data as a database, then on the sheet where you want the filtered
data to appear, go to Data-Import External Data-New Database Query

If you walk through the steps there, you'll see how you can identify the
Excel file as the database, and the range of data as a table. Use the help
file in Query to learn how to create a parameter query linked to a cell, and
plan for that cell to hold a store number. Once you've created your query
and identified the cell, you can change the cell value and run the query.
It'll bring in all the pertinent data



"David B" wrote:

I need to create a lookup formula that will find all employees for a given
store location. My data is organized as follows:

Store # Employee name
10 Bob
10 Jane
10 Kelly
12 Ted
12 David
12 James
12 Carrie
16 Joe
16 Kristin
16 Al
16 Nick
16 John

The obstacle I've encountered with a VLookup is that if I look for Store
#12, my formula only returns the first employee for store #12. I need to
create a formula that will extract all the employees, then create a new table
with that data.

Help!

  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Vlookup for multiple row data

On Feb 7, 7:04 pm, David B wrote:
Duke,

Thank you for your input....unfortunately, because of some limitations of
CrystalXcelsius(the application this excel spreadsheet "feeds") I have to
limit my data manipulation to formulas I can embed in the actual file;
Vlookup and Hlookup formulas are generally very helpful, but the data I need
to manipulate for this application is throwing me a curve ball....

"Duke Carey" wrote:
Treat your data as a database, then on the sheet where you want the filtered
data to appear, go to Data-Import External Data-New Database Query


If you walk through the steps there, you'll see how you can identify the
Excel file as the database, and the range of data as a table. Use the help
file in Query to learn how to create a parameter query linked to a cell, and
plan for that cell to hold a store number. Once you've created your query
and identified the cell, you can change the cell value and run the query.
It'll bring in all the pertinent data


"David B" wrote:


I need to create a lookup formula that will find all employees for a given
store location. My data is organized as follows:


Store # Employee name
10 Bob
10 Jane
10 Kelly
12 Ted
12 David
12 James
12 Carrie
16 Joe
16 Kristin
16 Al
16 Nick
16 John


The obstacle I've encountered with a VLookup is that if I look for Store
#12, my formula only returns the first employee for store #12. I need to
create a formula that will extract all the employees, then create a new table
with that data.


Help!


Hi,
Xcelcius does allow you to have a "filter selector" that can solve
your problem. Or creating an unique id based on store#&Employee name
and then use that to find information. Hope this helps
Sujith


  #15   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default Vlookup for multiple row data

I understand the limitations of Xcelsius and am new to Office Online.
The idea here is to create a serial number by store that a vlookup formula
can find.
Column A concatenates C&B
Columna B use an If formula that re-sets the serialization to 1 as the store
number changes B3=IF(D3=D2,1+C2,1)

1 A B C D
2 Concatenate.Serial Store # Employee name
3 101 1 10 Bob
4 102 2 10 Jane
5 103 3 10 Kelly
6 121 1 12 Ted
7 122 2 12 David
8 123 3 12 James
9 124 4 12 Carrie
10 161 1 16 Joe
11 162 2 16 Kristin
12 163 3 16 Al
13 164 4 16 Nick
14 165 5 16 John
15
16 Input Store number 12
17 Concatenate.Serial Employee name
18 121 1 Ted
19 122 2 David
20 123 3 James
21 124 4 Carrie
22 125 5
23 126 6
24 127 7
A18=$D$16&A18 and copied down

Formula
D17=IF(B17COUNTIF(C2:$C$13,$C$15),"",VLOOKUP(A17, $A$2:$D$13,4,FALSE))
The countif determines how many employee's per store.
I hope this helps.
Charlie_Brown


"David B" wrote:

I need to create a lookup formula that will find all employees for a given
store location. My data is organized as follows:

Store # Employee name
10 Bob
10 Jane
10 Kelly
12 Ted
12 David
12 James
12 Carrie
16 Joe
16 Kristin
16 Al
16 Nick
16 John

The obstacle I've encountered with a VLookup is that if I look for Store
#12, my formula only returns the first employee for store #12. I need to
create a formula that will extract all the employees, then create a new table
with that data.

Help!

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
Manual control of link updating for downloaded quotes? dk_ Excel Discussion (Misc queries) 9 November 15th 06 02:04 PM
sorting 2 colums of numbers and incremening them down blk&wht Excel Discussion (Misc queries) 10 October 9th 06 10:12 PM
Inputting data to one worksheet for it effect another daedalus1 Excel Discussion (Misc queries) 1 June 25th 06 04:39 PM
Vlookup for data contained in a cell Garbunkel Excel Worksheet Functions 5 September 14th 05 06:47 PM
Pulling data from 1 sheet to another Dave1155 Excel Worksheet Functions 1 January 12th 05 06:55 PM


All times are GMT +1. The time now is 04:29 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"