Dynamically extracting a list of unique values from non adjacdent column ranges

Hi,

Is there a possibility that the columns in between each of the non-contiguous ranges you specify could contain values which are also valid Job Numbers?

I only ask as interrogating a single two-dimensional array is generally far more straightforward than interrogating one comprising non-contiguous ranges.

Regards

Ok, I understand, but is there any feature shared by the Job Numbers which at the same time will never be shared by the hours figures?

For example, it would seem to me that the Job Numbers are all text entries (despite their appearance, you have them all formatted as 'number stored as text'), whereas the hours figures are all formatted as proper numerics. If you confirm that this is always the case then this criterion could be used to extract your list via interrogation of the single, contiguous range M25:X27.

As I mentioned, it would be far preferable to use some such condition to interrogate a single, contiguous range than to have to work with multiple, non-contiguous ranges. I'm not saying that the latter is impossible (it isn't); it's just disproportionately more complex!

Regards

Administrative Note:

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. No help to be offered until the link is provided.)

Ah, so the data in the book you posted was just mocked up (job numbers are 4 digits)?

In any case, that might suffice as a criterion, thanks. Can you just confirm the number format for those job numbers in your real workbook (right-click, Format Cells will tell you)? Even if they're in actuality proper numerics, I imagine we could distinguish them from the hours, assuming that there will never be a 5-figure hours entry, correct?

Regards

Ok, thanks. Let's assume, then, that the only text entries within the range M25:X27 are your desired extractions.

Try the following array formula** in A32:

=IFERROR(INDIRECT(TEXT(MIN(IF(ISTEXT(M$25:X$27),IF(COUNTIF(A$31:A31,M$25:X$27)=0,10^5*ROW(M$25:X$27)+COLUMN(M$25:X$27)))),"R0C00000"),0),"")

and copy down until you start to get blanks for the results.

N.B. If you have any null strings ("") within the range M25:X27, e.g. as a result of formulas within those cells, then these will equally be considered 'text' and so be returned by the formula. If this is the case let me know and I will post a modified version.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

FYI - If you enter XOR LX's formula as array in Office 365, it will cause error mentioned by torachan (i.e. you can't enter array formula over merged range)
In those case, just enter the formula as is, new array behavior will take over and will evaluate to single value.

N.B. I forgot that you might not be using an English language-version of Excel, in which case it's possible that, as well as translating the function names, you may require an alternative version of the part "R0C00000". "R" and "C" represent "row" and "column" here, though I have a strong feeling they are locale-dependent.

Regards

Originally Posted by CK76

FYI - If you enter XOR LX's formula as array in Office 365, it will cause error mentioned by torachan (i.e. you can't enter array formula over merged range)
In those case, just enter the formula as is, new array behavior will take over and will evaluate to single value.

Good point. I tested in 365 though unmerged the cells (out of habit) prior to entering the array formula.

Cheers

Originally Posted by Giulio Severini

Hi XOR LX,

well, I am not sure whether I properly understood your reply but Job numbers are all in the form 19980 - five numerical digits.

Does this help?

Thanks,
Alberto

Yes, Please try at A32

=AGGREGATE(15,6,$M$25:$W$27/ISTEXT($M$25:$W$27)/(--$M$25:$W$27>MAX(A$31:A31)),1)

Bo_Ry's solution is in any case much better: shorter and, more importantly, non-volatile.

Regards

Hi guys,

once again I have to thank you for your great contribution!
I have tried Bo_Ry's solution and it works perfectly.
XOR LX, I have tried your solution also - with and without blanks in the range - but the results are not sorted in ascending order (from smaller to bigger) but random.
I have a question, now. Bo_Ry's solution works for all kind of content format or only for 5-digits numerical content?
I have other cells where the jobs are in the 4-digits format.

Also, just to understand: what is the problem with the merged cells, as pointed out by torachan?
The formula I was using was returning the right results although the reference was in a merged cell.
So, what am I missing in his answer?

Many thanks to all, I will be posting the solution to the other forum as well.

Alberto

Hi Alberto

The formula work on any number stored as text and with less than 15 digits as it is a Number precision limit.


Merge cells won't accept Ctrl+Shift+Enter.
Some array formula require Ctrl+Shift+Enter Eg: Small(IF , Transpose
Some like Aggregate doesn't need

But Office 365 has dynamic array, no more Ctrl+Shift+Enter. so no problem.
But for best, Please avoid merge cells.


Regard

Please try
=IFerror(AGGREGATE(15,6,$M$25:$W$27/ISTEXT($M$25:$W$27)/(--$M$25:$W$27>MAX(A$31:A31)),1),"")