Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array Formula Duplicating Results
Hi,
I have an array formula that gives me the results I am looking for...with the exception of one type. Anytime the result has only 1 value, it duplicates that same value infinitely to the end of my array. As I said, 2 or more values populate perfectly, without duplication...but I can't seem to figure out why a result of 1 repeats. I thought perhaps I had actually been trimming the resultant row values off by 1 initially, and thus whenever the result was 1 it was actually using 0, which would default me to the "UDP" range length I have...but every other value results perfectly. Any help or advice would be appreciated, as I'm lost on this one. Here is my array formula...naturally it does have the {} when it is entered. Also...the range UDP is quite long, contains duplicate values, but the OFFSET cell range does not contain duplicates. =OFFSET(UDP,MATCH(U41,UDP,0)-1,-51,COUNTIF(UDP,U41),1) Let me know if any other info would be helpful. Thanks, Steve |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array Formula Duplicating Results
Just a note on my question...it seems like I may need a different function than the OFFSET/MATCH combination as the OFFSET function, used in a multi-cell array formula where row height is "1", will just repeat the same result in each cell of the multi-cell array. Does this sound like a bug? Or perhaps by design, with a different use for OFFSET in mind? I cant seem to picture a situation where this might be desired, and where you couldnt substitute this functionality with mere repeating the same OFFSET formula in as many cells as desired, as opposed to applying it to the entire multi-cell array. In any event, if anybody has a suggestion for an alternative function(s), or maybe something that I've missed...it would be appreciated. Regards, Steve |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array Formula Duplicating Results
What are you trying to do in the first place, maybe there is a non volatile
method -- Regards, Peo Sjoblom "SteveMax" wrote in message ... Just a note on my question...it seems like I may need a different function than the OFFSET/MATCH combination as the OFFSET function, used in a multi-cell array formula where row height is "1", will just repeat the same result in each cell of the multi-cell array. Does this sound like a bug? Or perhaps by design, with a different use for OFFSET in mind? I cant seem to picture a situation where this might be desired, and where you couldnt substitute this functionality with mere repeating the same OFFSET formula in as many cells as desired, as opposed to applying it to the entire multi-cell array. In any event, if anybody has a suggestion for an alternative function(s), or maybe something that I've missed...it would be appreciated. Regards, Steve |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Array Formula Duplicating Results
Hi Peo & thanks for you response,
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 I posted is named "UDP". In some cases I have 15 instances of the same value, and in other instances, just 1. Currently, using the OFFSET/MATCH function, I must keep the data worksheet sorted by this key field(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). 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. 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 2), 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. Regards, Steve "Peo Sjoblom" wrote: What are you trying to do in the first place, maybe there is a non volatile method -- Regards, Peo Sjoblom "SteveMax" wrote in message ... Just a note on my question...it seems like I may need a different function than the OFFSET/MATCH combination as the OFFSET function, used in a multi-cell array formula where row height is "1", will just repeat the same result in each cell of the multi-cell array. Does this sound like a bug? Or perhaps by design, with a different use for OFFSET in mind? I cant seem to picture a situation where this might be desired, and where you couldnt substitute this functionality with mere repeating the same OFFSET formula in as many cells as desired, as opposed to applying it to the entire multi-cell array. In any event, if anybody has a suggestion for an alternative function(s), or maybe something that I've missed...it would be appreciated. Regards, Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exclude 0 from MIN array results | Excel Discussion (Misc queries) | |||
array formula count results of two tests | Excel Worksheet Functions | |||
Issues with formula not duplicating | Excel Worksheet Functions | |||
Formula for Duplicating Rows!! | Excel Discussion (Misc queries) | |||
Array formula returning wrong results | Excel Discussion (Misc queries) |