Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multi-Cell Array Formula
Hi,
I posted this question in the General Discussion forum, without realizing it, so I reposted it here thinking it might get the right eyes in this forum perhaps. I am trying to show an analysis of data in 1 worksheet(analysis worksheet), from another worksheet(data worksheet). The key data in which I am trying to reference has multiple values, and varying quantities of duplicates among those multiple values, and in the formula it is named "UDP". In some cases I have 15 instances of the same value, and in other instances, just 1. The desired dataset which I'm deriving from the key data, is a column of unique text values...I dont want to manipulate them in any way, just display them, so that I can perform some additional VLOOKUPS, and calculate those results, in the analysis worksheet...so therefore, it is desired to not repeat the same result, for more reasons than aesthetics. Currently, using the OFFSET/MATCH function, I must keep the data worksheet sorted by this key range(or at least keep the values contiguous), in order to display the results correctly in the multi-cell array I've created to display the multiple results(I could live with no sorting in the data worksheet, but not preferred). I want to have the user select a data validated selection in the analysis worksheet, which in turn creates the keyed criteria for the multi-cell array(so far, this works well, except when the amount of resultant values is 1), and displays the multiple results(51 columns to the left) of every instance of this key data. To clarify again, the key data has multiple values & duplicates, so the amount of resultant data will vary, but will remain unique text. It works acceptably with the OFFSET/MATCH combo for displaying in a multi-cell array, but repeats the same value when the results(or height in rows) is 1...it functions great for more than 1 result. I have a possible workaround, which involves an IF statement for my analysis worksheet, but I'd rather not have to do that, if at all possible. Here is the formula I've tried to use...where "UDP" is a named range in the analysis worksheet...and of course the {} are in the actual multi-cell array. =OFFSET(UDP,MATCH(O41,UDP,0)-1,-51,COUNTIF(UDP,O41),1) Regards, Steve |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multi-Cell Array Formula
So you want to extract the unique values. Are there any empty cells within
the source range? Biff "SteveMax" wrote in message ... Hi, I posted this question in the General Discussion forum, without realizing it, so I reposted it here thinking it might get the right eyes in this forum perhaps. I am trying to show an analysis of data in 1 worksheet(analysis worksheet), from another worksheet(data worksheet). The key data in which I am trying to reference has multiple values, and varying quantities of duplicates among those multiple values, and in the formula it is named "UDP". In some cases I have 15 instances of the same value, and in other instances, just 1. The desired dataset which I'm deriving from the key data, is a column of unique text values...I dont want to manipulate them in any way, just display them, so that I can perform some additional VLOOKUPS, and calculate those results, in the analysis worksheet...so therefore, it is desired to not repeat the same result, for more reasons than aesthetics. Currently, using the OFFSET/MATCH function, I must keep the data worksheet sorted by this key range(or at least keep the values contiguous), in order to display the results correctly in the multi-cell array I've created to display the multiple results(I could live with no sorting in the data worksheet, but not preferred). I want to have the user select a data validated selection in the analysis worksheet, which in turn creates the keyed criteria for the multi-cell array(so far, this works well, except when the amount of resultant values is 1), and displays the multiple results(51 columns to the left) of every instance of this key data. To clarify again, the key data has multiple values & duplicates, so the amount of resultant data will vary, but will remain unique text. It works acceptably with the OFFSET/MATCH combo for displaying in a multi-cell array, but repeats the same value when the results(or height in rows) is 1...it functions great for more than 1 result. I have a possible workaround, which involves an IF statement for my analysis worksheet, but I'd rather not have to do that, if at all possible. Here is the formula I've tried to use...where "UDP" is a named range in the analysis worksheet...and of course the {} are in the actual multi-cell array. =OFFSET(UDP,MATCH(O41,UDP,0)-1,-51,COUNTIF(UDP,O41),1) Regards, Steve |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multi-Cell Array Formula
The intention is to not have blank cells in the source range(and there werent
any when i originally implemented the formula)...but as of this moment there is 1 blank value(I made it blank temporarily, until I have more data for other purposes in the workbook). If that is a requirement for a possible solution, I can make sure there no blanks, though. Regards, Steve "T. Valko" wrote: So you want to extract the unique values. Are there any empty cells within the source range? Biff "SteveMax" wrote in message ... Hi, I posted this question in the General Discussion forum, without realizing it, so I reposted it here thinking it might get the right eyes in this forum perhaps. I am trying to show an analysis of data in 1 worksheet(analysis worksheet), from another worksheet(data worksheet). The key data in which I am trying to reference has multiple values, and varying quantities of duplicates among those multiple values, and in the formula it is named "UDP". In some cases I have 15 instances of the same value, and in other instances, just 1. The desired dataset which I'm deriving from the key data, is a column of unique text values...I dont want to manipulate them in any way, just display them, so that I can perform some additional VLOOKUPS, and calculate those results, in the analysis worksheet...so therefore, it is desired to not repeat the same result, for more reasons than aesthetics. Currently, using the OFFSET/MATCH function, I must keep the data worksheet sorted by this key range(or at least keep the values contiguous), in order to display the results correctly in the multi-cell array I've created to display the multiple results(I could live with no sorting in the data worksheet, but not preferred). I want to have the user select a data validated selection in the analysis worksheet, which in turn creates the keyed criteria for the multi-cell array(so far, this works well, except when the amount of resultant values is 1), and displays the multiple results(51 columns to the left) of every instance of this key data. To clarify again, the key data has multiple values & duplicates, so the amount of resultant data will vary, but will remain unique text. It works acceptably with the OFFSET/MATCH combo for displaying in a multi-cell array, but repeats the same value when the results(or height in rows) is 1...it functions great for more than 1 result. I have a possible workaround, which involves an IF statement for my analysis worksheet, but I'd rather not have to do that, if at all possible. Here is the formula I've tried to use...where "UDP" is a named range in the analysis worksheet...and of course the {} are in the actual multi-cell array. =OFFSET(UDP,MATCH(O41,UDP,0)-1,-51,COUNTIF(UDP,O41),1) Regards, Steve |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multi-Cell Array Formula
1 thing I noticed in my post & felt I should clarify...
UDP is a named range in the DATA worksheet, not the analysis worksheet...although I doubt it matters much. Regards, Steve "SteveMax" wrote: Hi, I posted this question in the General Discussion forum, without realizing it, so I reposted it here thinking it might get the right eyes in this forum perhaps. I am trying to show an analysis of data in 1 worksheet(analysis worksheet), from another worksheet(data worksheet). The key data in which I am trying to reference has multiple values, and varying quantities of duplicates among those multiple values, and in the formula it is named "UDP". In some cases I have 15 instances of the same value, and in other instances, just 1. The desired dataset which I'm deriving from the key data, is a column of unique text values...I dont want to manipulate them in any way, just display them, so that I can perform some additional VLOOKUPS, and calculate those results, in the analysis worksheet...so therefore, it is desired to not repeat the same result, for more reasons than aesthetics. Currently, using the OFFSET/MATCH function, I must keep the data worksheet sorted by this key range(or at least keep the values contiguous), in order to display the results correctly in the multi-cell array I've created to display the multiple results(I could live with no sorting in the data worksheet, but not preferred). I want to have the user select a data validated selection in the analysis worksheet, which in turn creates the keyed criteria for the multi-cell array(so far, this works well, except when the amount of resultant values is 1), and displays the multiple results(51 columns to the left) of every instance of this key data. To clarify again, the key data has multiple values & duplicates, so the amount of resultant data will vary, but will remain unique text. It works acceptably with the OFFSET/MATCH combo for displaying in a multi-cell array, but repeats the same value when the results(or height in rows) is 1...it functions great for more than 1 result. I have a possible workaround, which involves an IF statement for my analysis worksheet, but I'd rather not have to do that, if at all possible. Here is the formula I've tried to use...where "UDP" is a named range in the analysis worksheet...and of course the {} are in the actual multi-cell array. =OFFSET(UDP,MATCH(O41,UDP,0)-1,-51,COUNTIF(UDP,O41),1) Regards, Steve |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multi-Cell Array Formula
I may not have completely answered your question the first time.
The values I want to retrieve are unique, by nature(names, but not people names)...but basically I want to find 1 value in the source range(multiple numeric values, with varying duplicate quantities), and display a value 51 columns to the left of it(which happens to have unique text values) for every instance of the selected value in the source range. I do not need to manipulate it, once retrieved. Hope that explains what I'm trying to accomplish a little better. Regards, Steve "T. Valko" wrote: So you want to extract the unique values. Are there any empty cells within the source range? Biff "SteveMax" wrote in message ... Hi, I posted this question in the General Discussion forum, without realizing it, so I reposted it here thinking it might get the right eyes in this forum perhaps. I am trying to show an analysis of data in 1 worksheet(analysis worksheet), from another worksheet(data worksheet). The key data in which I am trying to reference has multiple values, and varying quantities of duplicates among those multiple values, and in the formula it is named "UDP". In some cases I have 15 instances of the same value, and in other instances, just 1. The desired dataset which I'm deriving from the key data, is a column of unique text values...I dont want to manipulate them in any way, just display them, so that I can perform some additional VLOOKUPS, and calculate those results, in the analysis worksheet...so therefore, it is desired to not repeat the same result, for more reasons than aesthetics. Currently, using the OFFSET/MATCH function, I must keep the data worksheet sorted by this key range(or at least keep the values contiguous), in order to display the results correctly in the multi-cell array I've created to display the multiple results(I could live with no sorting in the data worksheet, but not preferred). I want to have the user select a data validated selection in the analysis worksheet, which in turn creates the keyed criteria for the multi-cell array(so far, this works well, except when the amount of resultant values is 1), and displays the multiple results(51 columns to the left) of every instance of this key data. To clarify again, the key data has multiple values & duplicates, so the amount of resultant data will vary, but will remain unique text. It works acceptably with the OFFSET/MATCH combo for displaying in a multi-cell array, but repeats the same value when the results(or height in rows) is 1...it functions great for more than 1 result. I have a possible workaround, which involves an IF statement for my analysis worksheet, but I'd rather not have to do that, if at all possible. Here is the formula I've tried to use...where "UDP" is a named range in the analysis worksheet...and of course the {} are in the actual multi-cell array. =OFFSET(UDP,MATCH(O41,UDP,0)-1,-51,COUNTIF(UDP,O41),1) Regards, Steve |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multi-Cell Array Formula
I'm getting confused by this:
I want to find 1 value in the source range(multiple numeric values, with varying duplicate quantities) Can you post an example? Or, better yet, if you could send a copy of the file to me so that I could actually see what you're talking about. If you can send a copy I'm at: xl can help at comcast period net Remove "can" and change the obvious. Biff "SteveMax" wrote in message ... I may not have completely answered your question the first time. The values I want to retrieve are unique, by nature(names, but not people names)...but basically I want to find 1 value in the source range(multiple numeric values, with varying duplicate quantities), and display a value 51 columns to the left of it(which happens to have unique text values) for every instance of the selected value in the source range. I do not need to manipulate it, once retrieved. Hope that explains what I'm trying to accomplish a little better. Regards, Steve "T. Valko" wrote: So you want to extract the unique values. Are there any empty cells within the source range? Biff "SteveMax" wrote in message ... Hi, I posted this question in the General Discussion forum, without realizing it, so I reposted it here thinking it might get the right eyes in this forum perhaps. I am trying to show an analysis of data in 1 worksheet(analysis worksheet), from another worksheet(data worksheet). The key data in which I am trying to reference has multiple values, and varying quantities of duplicates among those multiple values, and in the formula it is named "UDP". In some cases I have 15 instances of the same value, and in other instances, just 1. The desired dataset which I'm deriving from the key data, is a column of unique text values...I dont want to manipulate them in any way, just display them, so that I can perform some additional VLOOKUPS, and calculate those results, in the analysis worksheet...so therefore, it is desired to not repeat the same result, for more reasons than aesthetics. Currently, using the OFFSET/MATCH function, I must keep the data worksheet sorted by this key range(or at least keep the values contiguous), in order to display the results correctly in the multi-cell array I've created to display the multiple results(I could live with no sorting in the data worksheet, but not preferred). I want to have the user select a data validated selection in the analysis worksheet, which in turn creates the keyed criteria for the multi-cell array(so far, this works well, except when the amount of resultant values is 1), and displays the multiple results(51 columns to the left) of every instance of this key data. To clarify again, the key data has multiple values & duplicates, so the amount of resultant data will vary, but will remain unique text. It works acceptably with the OFFSET/MATCH combo for displaying in a multi-cell array, but repeats the same value when the results(or height in rows) is 1...it functions great for more than 1 result. I have a possible workaround, which involves an IF statement for my analysis worksheet, but I'd rather not have to do that, if at all possible. Here is the formula I've tried to use...where "UDP" is a named range in the analysis worksheet...and of course the {} are in the actual multi-cell array. =OFFSET(UDP,MATCH(O41,UDP,0)-1,-51,COUNTIF(UDP,O41),1) Regards, Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
match in multi-column and multi-row array | Excel Discussion (Misc queries) | |||
newbie question on multi-dimensional array | New Users to Excel | |||
referencing the value of a cell containing an array formula | Excel Worksheet Functions | |||
how can I paste multi-line/multi-paragraph data into ONE cell? | Excel Discussion (Misc queries) | |||
How can I use a cell reference in Sumproduct array formula? | Excel Discussion (Misc queries) |