Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple criteria with multiple results in one cell
Problem: How can I display multiple results, based on multiple criteria
in one cell, kind of like a dependent drop-down list of results, without sorting the source data located in a different file or using any filters. I cant have multiple results spread out over more than 1 cell (because this will result in many listings with blank cells, thus changing the format of my sheet) nor do I want to have the results concatenated into 1 long cell. Background: After combing through many postings in the Excel forums, I adapted one of the formulas to allow me to find companies that match multiple criteria on one of my worksheets and then insert the pay rate for these companies in a separate column on that worksheet based on a pay rate table located, currently, in a different workbook (a separate Excel file). While trying to find a solution to this problem, Ive created a smaller test version of the spreadsheet. The formula Im using is an array-entered forumula: =INDEX('[Rates.xls]Sheet1'!O$3:O$261,MATCH(1,('[Rates.xls]Sheet1'!$S$3:$S$261=$E8)*('[Rates.xls]Sheet1'!$K$3:$K$261=CONCATENATE($B8,"/",$C8,"/",$D8)),0)) where from the Rates.xls file, column O contains the pay rates to be found and S contains the Company names to be matched against based on values in my test sheet. From my test sheet, row 1 has headers in it. Column B is €śType€ť, column C €śProgram€ť, column D €śModel€ť, column E €śCompany€ť, and column G is where I want the rates to appear. In the formula, columns, B, C, & D are concatenated with /s to match the values found in column K of the Rates sheet (Example of column K: Networks/Res/Home; following the format $B8/$C8/$D8). Now I know that Debra Dalgleish has described how to create dependent drop-down lists on her site, but in those cases, it requires either having a sorted list or defining names for each list. Since I have almost 260 companies in the Rates file with other data listed in other columns, I would rather not sort this list or try and define names for all these companies. For the most part, the current formula works fine, but 14 of the companies have more than 1 pay rate available. For these companies, the formula only finds the first instance. Maybe the solution to this would to have some sort of combination of this formula and vba or data validation that would provide a list for these 14 companies. Maybe something that would say, use the formula, but if any of these 14 companies with their multiple rates are found, use a data-validated list or vba to display the results in a list. Would this be possible? All help is greatly apprecitated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple criteria with multiple results in one cell
I think you need an extra column for the pay rate for your spreadsheet ot be
fully automated. Even though there are only 14 companies with multiple pay rates, it is still an important factor. for the companies that have one rate either leave blank or put in something like Normal Rate. "RS" wrote: Problem: How can I display multiple results, based on multiple criteria in one cell, kind of like a dependent drop-down list of results, without sorting the source data located in a different file or using any filters. I cant have multiple results spread out over more than 1 cell (because this will result in many listings with blank cells, thus changing the format of my sheet) nor do I want to have the results concatenated into 1 long cell. Background: After combing through many postings in the Excel forums, I adapted one of the formulas to allow me to find companies that match multiple criteria on one of my worksheets and then insert the pay rate for these companies in a separate column on that worksheet based on a pay rate table located, currently, in a different workbook (a separate Excel file). While trying to find a solution to this problem, Ive created a smaller test version of the spreadsheet. The formula Im using is an array-entered forumula: =INDEX('[Rates.xls]Sheet1'!O$3:O$261,MATCH(1,('[Rates.xls]Sheet1'!$S$3:$S$261=$E8)*('[Rates.xls]Sheet1'!$K$3:$K$261=CONCATENATE($B8,"/",$C8,"/",$D8)),0)) where from the Rates.xls file, column O contains the pay rates to be found and S contains the Company names to be matched against based on values in my test sheet. From my test sheet, row 1 has headers in it. Column B is €śType€ť, column C €śProgram€ť, column D €śModel€ť, column E €śCompany€ť, and column G is where I want the rates to appear. In the formula, columns, B, C, & D are concatenated with /s to match the values found in column K of the Rates sheet (Example of column K: Networks/Res/Home; following the format $B8/$C8/$D8). Now I know that Debra Dalgleish has described how to create dependent drop-down lists on her site, but in those cases, it requires either having a sorted list or defining names for each list. Since I have almost 260 companies in the Rates file with other data listed in other columns, I would rather not sort this list or try and define names for all these companies. For the most part, the current formula works fine, but 14 of the companies have more than 1 pay rate available. For these companies, the formula only finds the first instance. Maybe the solution to this would to have some sort of combination of this formula and vba or data validation that would provide a list for these 14 companies. Maybe something that would say, use the formula, but if any of these 14 companies with their multiple rates are found, use a data-validated list or vba to display the results in a list. Would this be possible? All help is greatly apprecitated. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple criteria with multiple results in one cell
Here is a general solution for retrieving multiple entries with
multiple criteria. Sorry, but I have no brains to adapt it to your specific problem right now: =INDEX(data,SMALL(IF(conditions,ROW(data)),ROW(dat a)-ROW(firstcell) +1)) The idea: data is your data primary column. A virtual array is built with IF(conditions, ROW(data)). This array will have values like 1, 2, FALSE, 3, FALSE etc. where only admissible records will contribute a number. SMALL(virtual_array,ROW()-ROW(firstcell)+1) This one finds the nth smallest index number from the virtual array. firstcell is the first cell of the output array, hence the last expression will generate numbers starting from 1 in the first cell of the output and increasing. This must be *array* entered (Shift+Ctrl+Enter) HTH Kostis Vezerides On Mar 2, 2:13 am, RS wrote: Problem: How can I display multiple results, based on multiple criteria in one cell, kind of like a dependent drop-down list of results, without sorting the source data located in a different file or using any filters. I can't have multiple results spread out over more than 1 cell (because this will result in many listings with blank cells, thus changing the format of my sheet) nor do I want to have the results concatenated into 1 long cell. Background: After combing through many postings in the Excel forums, I adapted one of the formulas to allow me to find companies that match multiple criteria on one of my worksheets and then insert the pay rate for these companies in a separate column on that worksheet based on a pay rate table located, currently, in a different workbook (a separate Excel file). While trying to find a solution to this problem, I've created a smaller test version of the spreadsheet. The formula I'm using is an array-entered forumula: =INDEX('[Rates.xls]Sheet1'!O$3:O$261,MATCH(1,('[Rates.xls]Sheet1'!$S$3:$S$261=$E8)*('[Rates.xls]Sheet1'!$K$3:$K$261=CONCATENATE($B8,"/",$C8,"/",$D8)),0)) where from the Rates.xls file, column O contains the pay rates to be found and S contains the Company names to be matched against based on values in my test sheet. From my test sheet, row 1 has headers in it. Column B is "Type", column C "Program", column D "Model", column E "Company", and column G is where I want the rates to appear. In the formula, columns, B, C, & D are concatenated with /'s to match the values found in column K of the Rates sheet (Example of column K: Networks/Res/Home; following the format $B8/$C8/$D8). Now I know that Debra Dalgleish has described how to create dependent drop-down lists on her site, but in those cases, it requires either having a sorted list or defining names for each list. Since I have almost 260 companies in the Rates file with other data listed in other columns, I would rather not sort this list or try and define names for all these companies. For the most part, the current formula works fine, but 14 of the companies have more than 1 pay rate available. For these companies, the formula only finds the first instance. Maybe the solution to this would to have some sort of combination of this formula and vba or data validation that would provide a list for these 14 companies. Maybe something that would say, use the formula, but if any of these 14 companies with their multiple rates are found, use a data-validated list or vba to display the results in a list. Would this be possible? All help is greatly apprecitated. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple criteria with multiple results in one cell
Dear Kostis,
Thanks for your response. Before I start with my questions regarding the equation you provided, does your solution display multiple results in one cell? If so, in what form? Is it a drop-down list or do all the values appear in a single cell? Now, onto all my questions€¦ Since Im not that familiar with this function or arrays, you might have to walk me through this a little more. Is €śdata€ť referring to the column where I want the pay rates inserted (column G) or does it refer to the other file where I have the pay rates stored (column O in the Rates.xls file)? For the virtual array built with IF(conditions, ROW(data)), I dont understand what €śIf€ť conditions I would use. Is ROW(data) referring to the current row Im in? For example, if I was looking up the pay rate in cell G8 on my test sheet, would this part of the formula look like IF(conditions, ROW(G8))? In the SMALL equation, what would I put in the ROW()-ROW(firstcell)+1) section? Is ROW () referring to the row Im currently in and does ROW(fistcell)+1 refer to column O in Rates.xls where the pay rates are kept? Once again, I apologize for all these questions even though you did a nice job explaining what the various functions do. Unfortunately, Im completely new to working with arrays. "vezerid" wrote: Here is a general solution for retrieving multiple entries with multiple criteria. Sorry, but I have no brains to adapt it to your specific problem right now: =INDEX(data,SMALL(IF(conditions,ROW(data)),ROW(dat a)-ROW(firstcell) +1)) The idea: data is your data primary column. A virtual array is built with IF(conditions, ROW(data)). This array will have values like 1, 2, FALSE, 3, FALSE etc. where only admissible records will contribute a number. SMALL(virtual_array,ROW()-ROW(firstcell)+1) This one finds the nth smallest index number from the virtual array. firstcell is the first cell of the output array, hence the last expression will generate numbers starting from 1 in the first cell of the output and increasing. This must be *array* entered (Shift+Ctrl+Enter) HTH Kostis Vezerides On Mar 2, 2:13 am, RS wrote: Problem: How can I display multiple results, based on multiple criteria in one cell, kind of like a dependent drop-down list of results, without sorting the source data located in a different file or using any filters. I can't have multiple results spread out over more than 1 cell (because this will result in many listings with blank cells, thus changing the format of my sheet) nor do I want to have the results concatenated into 1 long cell. Background: After combing through many postings in the Excel forums, I adapted one of the formulas to allow me to find companies that match multiple criteria on one of my worksheets and then insert the pay rate for these companies in a separate column on that worksheet based on a pay rate table located, currently, in a different workbook (a separate Excel file). While trying to find a solution to this problem, I've created a smaller test version of the spreadsheet. The formula I'm using is an array-entered forumula: =INDEX('[Rates.xls]Sheet1'!O$3:O$261,MATCH(1,('[Rates.xls]Sheet1'!$S$3:$S$261=$E8)*('[Rates.xls]Sheet1'!$K$3:$K$261=CONCATENATE($B8,"/",$C8,"/",$D8)),0)) where from the Rates.xls file, column O contains the pay rates to be found and S contains the Company names to be matched against based on values in my test sheet. From my test sheet, row 1 has headers in it. Column B is "Type", column C "Program", column D "Model", column E "Company", and column G is where I want the rates to appear. In the formula, columns, B, C, & D are concatenated with /'s to match the values found in column K of the Rates sheet (Example of column K: Networks/Res/Home; following the format $B8/$C8/$D8). Now I know that Debra Dalgleish has described how to create dependent drop-down lists on her site, but in those cases, it requires either having a sorted list or defining names for each list. Since I have almost 260 companies in the Rates file with other data listed in other columns, I would rather not sort this list or try and define names for all these companies. For the most part, the current formula works fine, but 14 of the companies have more than 1 pay rate available. For these companies, the formula only finds the first instance. Maybe the solution to this would to have some sort of combination of this formula and vba or data validation that would provide a list for these 14 companies. Maybe something that would say, use the formula, but if any of these 14 companies with their multiple rates are found, use a data-validated list or vba to display the results in a list. Would this be possible? All help is greatly apprecitated. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple criteria with multiple results in one cell
Dear Joel,
Column G is where the pay rates currently appear and I want the user to be able to choose the correct pay rate from a drop-down list for those instances where there is more than 1 choice. Because of this, I realize that the worksheet wont be fully automated. "Joel" wrote: I think you need an extra column for the pay rate for your spreadsheet ot be fully automated. Even though there are only 14 companies with multiple pay rates, it is still an important factor. for the companies that have one rate either leave blank or put in something like Normal Rate. "RS" wrote: Problem: How can I display multiple results, based on multiple criteria in one cell, kind of like a dependent drop-down list of results, without sorting the source data located in a different file or using any filters. I cant have multiple results spread out over more than 1 cell (because this will result in many listings with blank cells, thus changing the format of my sheet) nor do I want to have the results concatenated into 1 long cell. Background: After combing through many postings in the Excel forums, I adapted one of the formulas to allow me to find companies that match multiple criteria on one of my worksheets and then insert the pay rate for these companies in a separate column on that worksheet based on a pay rate table located, currently, in a different workbook (a separate Excel file). While trying to find a solution to this problem, Ive created a smaller test version of the spreadsheet. The formula Im using is an array-entered forumula: =INDEX('[Rates.xls]Sheet1'!O$3:O$261,MATCH(1,('[Rates.xls]Sheet1'!$S$3:$S$261=$E8)*('[Rates.xls]Sheet1'!$K$3:$K$261=CONCATENATE($B8,"/",$C8,"/",$D8)),0)) where from the Rates.xls file, column O contains the pay rates to be found and S contains the Company names to be matched against based on values in my test sheet. From my test sheet, row 1 has headers in it. Column B is €śType€ť, column C €śProgram€ť, column D €śModel€ť, column E €śCompany€ť, and column G is where I want the rates to appear. In the formula, columns, B, C, & D are concatenated with /s to match the values found in column K of the Rates sheet (Example of column K: Networks/Res/Home; following the format $B8/$C8/$D8). Now I know that Debra Dalgleish has described how to create dependent drop-down lists on her site, but in those cases, it requires either having a sorted list or defining names for each list. Since I have almost 260 companies in the Rates file with other data listed in other columns, I would rather not sort this list or try and define names for all these companies. For the most part, the current formula works fine, but 14 of the companies have more than 1 pay rate available. For these companies, the formula only finds the first instance. Maybe the solution to this would to have some sort of combination of this formula and vba or data validation that would provide a list for these 14 companies. Maybe something that would say, use the formula, but if any of these 14 companies with their multiple rates are found, use a data-validated list or vba to display the results in a list. Would this be possible? All help is greatly apprecitated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
retrieve multiple results with one criteria... | Excel Discussion (Misc queries) | |||
Multiple results from multiple criteria using IF function | Excel Discussion (Misc queries) | |||
MULTIPLE CRITERIA RETURNING SUM OF RESULTS | Excel Worksheet Functions | |||
MULTIPLE CRITERIA RETURNING SUM OF RESULTS | Excel Worksheet Functions | |||
Multi-criteria lookup with Multiple results | Excel Worksheet Functions |