• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula Challenge 012 - Turn a mixed 2-d array into a 1-d array.

jeffreyweir

Active Member
Formula Challenge 012 - Turn a mixed 2-d array into a 1-d array.


Okay, people. We’ve got the following dataset:

[pre]
Code:
1	2	3	a	b	c
4	5	6	d	e	f
[/pre]
…which you could also represent like this:

{1,2,3,"a","b","c";4,5,6,"d","e","f"}


Give this data the named range a (a for array).


Your challenge is to dynamically turn this 2D array to a 1D array that can then be used directly by other formulas, without being entered over a range.

So your formula will evaluate to this:

{1,2,3,"a","b","c",4,5,6,"d","e","f"}


As per usual, there are some rules
:

This is a one-formula challenge. No helper cells, no VBA, no performance enhancing steroids/addins.


Your time starts……NOW()
 

sameer bhide

New Member
Jeff

Dont tell me you managed to shorten this further !


=CHOOSE({1;2},INDEX(a,1,),INDEX(a,2,)) - Will maintain the Datatype of the original data

or

=REPT(List1,{1;0})&REPT(List2,{0;1}) - But will return the numbers as strings


sam
 

jeffreyweir

Active Member
Correct, Sam...this 2x6 array needs to be changed into a 1 x 12 array. And the formula should handle any conceivable x by y array.
 

Sajan

Excel Ninja
Excellent challenge Jeff!

I am going to see if there is a way to produce the desired 1-d array without resorting to a construct such as IF(T(...),N(...),T(...)).


-Sajan.
 

jeffreyweir

Active Member
I find these challenges enormous fun, and some of the stuff we've covered so far I have not seen published anywhere else on the net.


My formula is 108 characters of non IF-ness.
 

sameer bhide

New Member
[pre]
Code:
@Jeff

A more Generic Formula for any X, Y grid

=OFFSET(a,TRUNC((COLUMN(OFFSET(A1,,,,COLUMNS(a)*2))-1)/COLUMNS(a)),MOD(COLUMN(OFFSET(A1,,,,COLUMNS(a)*2))-1,COLUMNS(a)),1,1)

Returns
={1,2,3,"a","b","c",4,5,6,"d","e","f"}

How I wish Data Validation would accept this in the list option to return the list
[/pre]
 

sameer bhide

New Member
Oops...Cant Hard code the 2


=OFFSET(a,TRUNC((COLUMN(OFFSET(A1,,,,COLUMNS(a)*ROWS(a)))-1)/COLUMNS(a)),MOD(COLUMN(OFFSET(A1,,,,COLUMNS(a)*ROWS(a)))-1,COLUMNS(a)),1,1)
 

Sam Mathai Chacko

Active Member
I was just waiting for JW to green signal this. So here's my question - is =COUNT(OFFSET(A,TRUNC((COLUMN(OFFSET(A1,,,,COLUMNS(A)*ROWS(A)))-1)/COLUMNS(A)),MOD(COLUMN(OFFSET(A1,,,,COLUMNS(A)*ROWS(A)))-1,COLUMNS(A)),1,1)) supposed to return 1?


Jeff/Sameer can you post a file with Sameer's formula. I'd like to understand what I'm doing wrong (could be something really stupid).
 

NARAYANK991

Excel Ninja
Hi Sam ,


Replace the 1s at the end by ROWS(a) and COLUMNS(a).


=OFFSET(a,TRUNC((COLUMN(OFFSET(A1,,,,COLUMNS(a)*ROWS(a)))-1)/COLUMNS(a)),MOD(COLUMN(OFFSET(A1,,,,COLUMNS(a)*ROWS(a)))-1,COLUMNS(a)),ROWS(a),COLUMNS(a))


Narayan
 

Sam Mathai Chacko

Active Member
Thanks Narayan. Tried that also. The direction I am looking for is that since Jeff is looking for a 1D array, if I use a match function like this for example,


=MATCH(4,OFFSET(A,TRUNC((COLUMN(OFFSET(A1,,,,COLUMNS(A)*ROWS(A)))-1)/COLUMNS(A)),MOD(COLUMN(OFFSET(A1,,,,COLUMNS(A)*ROWS(A)))-1,COLUMNS(A)),ROWS(A),COLUMNS(A)),0)


based on what Jeff was looking for, this should equate to

=MATCH(4,{1,2,3,"a","b","c",4,5,6,"d","e","f"},0) [yes Jeff?]


I should get..what.. 7?? But I can't seem to get it. Either I'm confusing myself, or I'm missing something.
 

NARAYANK991

Excel Ninja
Hi Sam ,


Your earlier post mentioned COUNT ; the use of ROWS(a) and COLUMNS(a) works with COUNT. Now , if you say the issue is the MATCH function , you are right , the formula does not work ; apparently , pressing F9 shows a 1-d array but when evaluated , it remains a 2-d range , and MATCH does not work.


Of course , if you are matching a numeric value , wrapping the formula within N( ... ) works ; if you are matching a text value , wrapping it in T( ... ) also works.


Narayan
 

Sam Mathai Chacko

Active Member
Since I failed to come up with any other solution, I'll go with a cheeky solution, claiming that it is within the realms of the rules - the escape route being that Jeff didn't explicitly mention that there could be blanks cells. Jeff, hope you'll forgive me for that.


So that said, I think this can be considered viable. Again, not much other than a tweak to Sameer's formula.


=OFFSET(a,(COLUMN(OFFSET(A1,,,,COUNTA(a)))-1)/COLUMNS(a),MOD(COLUMN(OFFSET(A1,,,,COUNTA(a)))-1,COLUMNS(a)))
 

Sajan

Excel Ninja
Given that OFFSET and INDIRECT require de-referencing (using T() or N(), for example), my thought is that functions such as LOOKUP are probably the ones to explore further.


Reviewing the list of available functions that can return an array:

CELL

CHOOSE

CONCATENATE

HLOOKUP

VLOOKUP

LOOKUP

T

N

TEXT

MID, LEFT, RIGHT

INDEX

OFFSET

INDIRECT

ADDRESS

REPT

SUBSTITUTE

REPLACE

TRIM

VALUE


Only LOOKUP, VLOOKUP, HLOOKUP seem to be able to provide a value array. i.e. something that can fit the construct =FUNCTION(...) & "ABC"


CELL returns a value from a reference, but only provides the subsequent values when array-entered over multiple cells.


Given the multi-dimensional nature of the source array, VLOOKUP and HLOOKUP seem to be out as well.


That leaves LOOKUP(). However, since LOOKUP expects a sorted array, when used with a 2-d array, it returns values based on first the column, and then the row where the value was found. This is evident if you do LOOKUP(a,a)


Oh well.... back to the proverbial drawing board!
 

jeffreyweir

Active Member
Hi Gents. Good discussion, and good skills all. Sam - I see what you mean about the MATCH function. I didn't try that particular function, and my approach fails with MATCH and a whole bunch of other functions too.


My first approach was this: =OFFSET(a,INT(COLUMN(OFFSET(A1,,,,(COLUMNS(a))*2))/(COLUMNS(a)+1)),MOD(COLUMN(OFFSET(A1,,,,COLUMNS(a)*2))-1,COLUMNS(a)),1,1)


And also I found a post with a formula by Harlan Grove, that used a very clever way of counting the number of cells in the reference. He divides everything in the reference by zero, because COUNTA counts errors:

COUNTA(a/0)


So I changed my formula to this:

=OFFSET(a,INT(COLUMN(OFFSET(A1,,,,COUNTA(a/0)))/(COLUMNS(a)+1)),MOD(COLUMN(OFFSET(A1,,,,COUNTA(a/0)))-1,COLUMNS(a)),1,1)


...which you can dereference with a plus sign in some instances. For instance, these return the correct value:

=MAX(+OFFSET(a,INT(COLUMN(OFFSET(A1,,,,COUNTA(a/0)))/(COLUMNS(a)+1)),MOD(COLUMN(OFFSET(A1,,,,COUNTA(a/0)))-1,COLUMNS(a)),1,1))


=COUNTA(+OFFSET(a,INT(COLUMN(OFFSET(A1,,,,COUNTA(a/0)))/(COLUMNS(a)+1)),MOD(COLUMN(OFFSET(A1,,,,COUNTA(a/0)))-1,COLUMNS(a)),1,1))


=SUM(+OFFSET(a,INT(COLUMN(OFFSET(A1,,,,COUNTA(a/0)))/(COLUMNS(a)+1)),MOD(COLUMN(OFFSET(A1,,,,COUNTA(a/0)))-1,COLUMNS(a)),1,1))


But it doesn't always work
. For instance, these do not work:

=+OFFSET(a,INT(COLUMN(OFFSET(A1,,,,COUNTA(a/0)))/(COLUMNS(a)+1)),MOD(COLUMN(OFFSET(A1,,,,COUNTA(a/0)))-1,COLUMNS(a)),1,1)="a"


=MATCH(2,+OFFSET(a,INT(COLUMN(OFFSET(A1,,,,COUNTA(a/0)))/(COLUMNS(a)+1)),MOD(COLUMN(OFFSET(A1,,,,COUNTA(a/0)))-1,COLUMNS(a)),1,1),0)


Damn!


But it works fine if you want to return cells to the worksheet.


So looks like I've set a challenge that is only part achievable.


Sorry, lads.
 

jeffreyweir

Active Member
All that said and done, if you want to use MATCH et cetera then you can use the IF construct that Sajan mentioned above:


=IF(T(OFFSET(a,INT(COLUMN(OFFSET(A1,,,,COUNTA(a/0)))/(COLUMNS(a)+1)),MOD(COLUMN(OFFSET(A1,,,,COUNTA(a/0)))-1,COLUMNS(a)),1,1))="",N(OFFSET(a,INT(COLUMN(OFFSET(A1,,,,COUNTA(a/0)))/(COLUMNS(a)+1)),MOD(COLUMN(OFFSET(A1,,,,COUNTA(a/0)))-1,COLUMNS(a)),1,1)),T(OFFSET(a,INT(COLUMN(OFFSET(A1,,,,COUNTA(a/0)))/(COLUMNS(a)+1)),MOD(COLUMN(OFFSET(A1,,,,COUNTA(a/0)))-1,COLUMNS(a)),1,1)))


For example, here's a MATCH on that:

=MATCH("f",IF(T(OFFSET(a,INT(COLUMN(OFFSET(A1,,,,COUNTA(a/0)))/(COLUMNS(a)+1)),MOD(COLUMN(OFFSET(A1,,,,COUNTA(a/0)))-1,COLUMNS(a)),1,1))="",N(OFFSET(a,INT(COLUMN(OFFSET(A1,,,,COUNTA(a/0)))/(COLUMNS(a)+1)),MOD(COLUMN(OFFSET(A1,,,,COUNTA(a/0)))-1,COLUMNS(a)),1,1)),T(OFFSET(a,INT(COLUMN(OFFSET(A1,,,,COUNTA(a/0)))/(COLUMNS(a)+1)),MOD(COLUMN(OFFSET(A1,,,,COUNTA(a/0)))-1,COLUMNS(a)),1,1))),0)


That works exactly as expected. And the result is that we now all have a foolproof 376-character way to flatten a 2d array to 1d. Yipee!
 

Sam Mathai Chacko

Active Member
Why not this then


=IFERROR(MATCH("f",N(OFFSET(a,(ROW(OFFSET(A1,,,ROWS(a)*COLUMNS(a),))-1)/COLUMNS(a),MOD(ROW(OFFSET(A1,,,ROWS(a)*COLUMNS(a),))-1,COLUMNS(a)))),0),MATCH("f",T(OFFSET(a,(ROW(OFFSET(A1,,,ROWS(a)*COLUMNS(a),))-1)/COLUMNS(a),MOD(ROW(OFFSET(A1,,,ROWS(a)*COLUMNS(a),))-1,COLUMNS(a)))),0))
 

NARAYANK991

Excel Ninja
Hi Jeff ,


The first part of your formula is checking to see if there is at least one text item in the range ; can this not be done by a simple :


OR(ISTEXT(a))


Narayan
 

jeffreyweir

Active Member
Sam - good approach. I was concentrating too hard on finding the one formula that you could wrap any other formula around. But your approach is better.


Narayan...everything in the first part of my formula is checking for numbers in the reconstituted 1D array.

Because of the way T works, T("2") returns blank. So IF T("2")="" tells me that "2" is a number stored as text.


Your OR(ISTEXT(a)) checks the original 2d
array. I need to check the reconstituted 1D
array.
 

NARAYANK991

Excel Ninja
Hi Jeff ,


Correct me if I am wrong.


Your challenge is the formula needed to convert a 2-d range ( containing both text and numeric values ) into a 1-d array , where the text items will remain as text items , and the numeric values will remain as numeric values.


A digit 2 which is entered as text is still text , and should not return a match with the numeric value 2.


The formula itself needs to have two distinct variants , since from what I have read of the several posts in the challenge , there is no single formula which caters to both text and numeric values.


As I understand it , when we are trying to match any value , whether it be text or numeric , we need to check for whether there is at least one numeric value or text value in the input range ; I do not see the difference between checking the input 2-d range and the reconstituted 1-d array , since the aim of the challenge is to reproduce the data within the 2-d input range faithfully while converting it to a 1-d array.


So , if we are trying to match a text value , we need to check whether there is at least one text value in the input range ; similarly , when we are trying to match a numeric value , we need to check whether there is at least one numeric value in the input range.


Secondly , the N function returns numeric values for every data item in the input range ; text items will return 0 , as will the numeric value 0 itself , should it be present anywhere in the input range.


Against this , the T function returns blanks where the data item in the input range is a numeric value , and returns the data item where it is text.


Thus , it is always preferable to check for whether there is a text item in the input range ; if there is , we can use the T version , otherwise , using the T version is meaningless.


Checking whether what we are trying to match is text or not is the better option , since if we are trying to match a numeric value , there is no need to use the T version at all.


Narayan
 

jeffreyweir

Active Member
I'm using IF in conjunction with T and N to selectively 'dereference' each element of the array individually.


As you've outlined, dereferencing the entire array with T turns numbers to "" and dereferencing the entire array with N turn text to 0. So to dereference a mixed array I need to check whether each individual item is text or numerical, and use T or N accordingly.


So that's what that IF(N(OFFSET...))="",N(OFFSET...),T(OFFSET...)) does.


I'm not sure how I could use your suggested OR(ISTEXT(a)) into my formula =OFFSET(a,INT((ROW(INDIRECT("1:"&COUNTA(a/0)))-1)/COLUMNS(a)),MOD(ROW(INDIRECT("1:"&COUNTA(a/0)))-1,COLUMNS(a)),1,1) in a way that does the same thing.


If the array was either entirely
numbers or entirely
text, then I could use OR(ISTEXT(a)) to work out which case it is, and then dereference the entire
array with either N or T.


But in this challenge, the array will always
be mixed. So checking the entire array for text with OR(ISTEXT(a)) isn't any help as far as I can see.
 
Top