Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Manual control of link updating for downloaded quotes? | Excel Discussion (Misc queries) | |||
sorting 2 colums of numbers and incremening them down | Excel Discussion (Misc queries) | |||
Inputting data to one worksheet for it effect another | Excel Discussion (Misc queries) | |||
Vlookup for data contained in a cell | Excel Worksheet Functions | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |