The challenge this week is as follows: given a range of arbitrary size in which each entry is either 0 or 1 and in which each row contains at least one occurrence of a 1, a single formula to return an array consisting of the relative column positions of the first occurrence of a 1 within each row.
For example, given the below in A1:E10:
the solution would be the array:
{2;1;1;2;1;5;1;4;1;3}
Readers may reference the range A1:E10 in their solution, though of course being aware that this choice is purely arbitrary and hence that any solution must also hold for a range of any size.
Readers should also note that the entries in the returned array are to be the relative column positions within the range (just as if we’d used MATCH on each of the rows within that range). As such, moving the above range to, for example, H1:L10 would have no impact on the output of any solution.
Also note that this is NOT a shortest formula challenge!
Solution next week. Good luck!
Nice challenge!
Taking advantage of your binary specifications, and assuming in this case a 10×10 grid (A1:J10):
-Alex
@Alex
Can you clarify what results that formula gives when the number of rows in the range is not equal to that for the number of columns?
Have you got your matrix multiplication correct?
Perhaps you should stick – as I specified – to referencing the range A1:E10, just so that your answer will be consistent with any others that may be posted.
Regards
Hi, XOR LX
Here’s a my first try.
=COLUMNS(A1:E10)-INT(LOG(MMULT(A1:E10,2^(COLUMNS(A1:E10)-ROW(INDIRECT("1:"&COLUMNS(A1:E10))))),2))@aMareis
Faultless technique. Well done!
@XOR LX
Fixed (and also shamelessly changing TRUNC to INT after aMareis’ posting):
@Alex
And a great fix too! 🙂
Another faultless – and subtly different – solution. Good stuff!
On my mind, aMareis made better solution, because you can refer to named range (for example, binary matrix is in range named “Matrx”). Alex solution is also really cool, but you have to refer to a single row in a range.
Anyway, both solutions a great! As for me there is too much maths to find appropriate way, specially LOG part 🙂
Hi – I am generally not so good in such challenges, however, below is my immature try…
@Asheesh
I take it you’ve tested your solution? Can you just confirm what result it gives for you?
Regards
Another aproach could be:
I’m looking for another solution. Blessings!
@John Jairo
Very original! Like it! Interesting use of FIND and TEXT and certainly not an approach which I’d considered.
Excellent stuff. Cheers!
My approach is similar John Jairo
=FIND(1,TEXT(MMULT(A1:E10,TRANSPOSE(10^(COUNT(A1:E1)-COLUMN(A1:E1)))),REPT("0",COUNT(A1:E1))))@sam
And I’m sure John won’t mind me saying that you’ve improved that set-up considerably.
Excellent work yet again. Very impressed with all contributions so far to this challenge.
Regards
@Maxim
Based on your feedback I’ve revised mine slightly (making it more similar to aMareis in the process):
-Alex
Oops I was too quick on the gun, needs to be revised to:
Not quite as pretty anymore…
@Sam, @JohnJairo
Fellow, I like your solutions, but I’m confused about the use of the COLUMN(A1:E1) construction in your formula…
I don’t understand how to make the formula work when I move the data to Columns H:L — as indicated in the original post.
Perhaps I’m missing something?
Correction — John’s worked in columns H:L; but I still can’t get Sam’s to work…
@Isaac
Good point. Thanks.
Although it would only appear to be Sam’s construction which fails if the range is changed. John Jairo’s seems sound to me. Can you clarify?
Regards
@XOR LX – Nice challenge and great ideas presented so far. One issue i have is that in the spec it says “any solution must also hold for a range of any size” so perhaps this could be more strictly adhered to.
For example the solution of aMareis and Alex with a range containing a row of 47 ones returns zero for that line. Also JJV and sam’s suggestions allow only up to 255 columns.
I haven’t given much thought to it but it should be possible to overcome some of these limitations by operating on ranges without converting to numbers or strings. I guess you may have had something like that in mind?
@John Jairo V – your challenge over at Chandoo is a good one – I hope you get some more takers…
@Lori
Agreed. I confess that I have been quite unrigorous in my checking of solutions presented so far, which does seem somewhat pointless given that, as you say, I laid out quite specific guidelines.
And you’re also correct when you intimate what I had in mind. The solution I myself derived is not thus restricted. However, I think I was overly-swayed by the ingenuity of some of the posts so far (which in my opinion have showed impressive creativity, if at a cost of rigour), and so “let my guard down” when it came to submitting those solutions to a thorough analysis.
In fact, as I have done several times in these formula challenges, I again shot myself in the foot somewhat. I had originally intended for this challenge to serve as an opener for the general problem of obtaining an “array of MATCHes”, i.e. given any values in the range. The fact that I then stipulated that this range would consist of only either 0s or 1s did not, at the time, seem like it should make a difference, though of course I had not then envisaged the ingenious solutions of Alex and aMareis. Had I done so, I would most certainly have set it up so that the non-unity entries were random integers (or perhaps, even better, all text).
Still, sometimes good things come out of such “accidents”, and from the solutions given thus far I have to say that I’m almost glad that I made a slight slip in the designing of this challenge.
Still, that doesn’t excuse the poor job of checking that I’ve done, so I’ll be sure to improve in that department for the next challenge.
Regards
Here’s an another try.
Thank you.
I also found that aMareis’ will fail for certain combinations of contiguous zeros and contiguous ones. eg 1 zero followed by 46 ones fails, as does 2 zeroes followed by 45 ones. 3 zeros followed by 44 ones works, as does other combinations with increasing leading zeroes.
Very clever, though.
To make Sams and JJ’s formulas truly dynamic – so that they could work on a range called Data wherever that range is located – I added -COLUMNS(Data)-1 to the end.
Well done, all.
My Bad … I completely misunerstood…please ignore my response
Hmmm…. based on solutions by @Sam and @JohnJairo, I got this one:
=FIND(1,TEXT(MMULT(A1:E10,10^(COLUMNS(A1:E10)-ROW(INDIRECT("1:"&COLUMNS(A1:E10))))),REPT(0,COLUMNS(A1:E10))))Tested on named range, which location on sheet is random. At least up to 100 columns in tested range it works fine for me. Hope I correctly translated formula to English version.
@Maxim
You say “up to 100 columns“. Are you aware of the precise limitation here? Have you read Lori’s contribution?
Although I’ve been a bit slack in terms of adhering to the specifications that I laid out, recall that I did say that a solution should work for any range.
Regards
@XOR LX, got it. Yes, my decision has limitations, at least on string length and max 10^308.
Ok, still thinking on this…
aMaris – you need to get rid of that hard-coded 5 in your 10^5 so that the formula handles a range of arbitrary size. Amending your formula accordingly – and using the named range Data – gives this:
Okay, I’ve got one:
Using the Redimensioning from the last post.
I think I can do away with the transpose at the end with some effort
@Jeff
Nice! Good application of the material in the previous post!
Cheers
Here it is, without the TRANSPOSE and oriented the correct way:
@Jeff Weir
I appreciate your comments. 😉
@Jeff wow… Wow!!! Excellent!
PS you can replace COLUMNS(Data)*ROWS(Data) with COUNT(Data) – a little bit easier… 🙂
Maxim: good point.
Oops… Jeff, test your solution on a range with a number of cells more than 16348. For example, 130×130. It returns an error because columns count exceeds limit on sheet.
Ah yes. I can change the formula around to increase that, but I’m still going to be limited by 1048576 cells.
@Jeff, would you please? I think it will be best solution for now, regarding array size
@Jeff Weir
You misunderstand my formula.
5 of 10^5 is not mean column’s count.
Thanks.
Couldn’t reists trying this one.
This formula is durable to any size range…(If you’ve got the patience to wait for REALLY large ranges to be calculated):
I tested it on D5:XES132 and it took several seconds to calculate, using this
Maybe I did something wrong, but I couldn’t get the other posted formulas to work on that range.
@Jeff Weir
Don’t change 10^5 to 10^COLUMNS(Data).
Refer to next article and read XORLX’s kindly explanation, please. 😉
https://excelxor.com/2014/08/13/single-column-from-many-containing-blanks-1-rows-first/
@aMareis: My apologies…I misunderstood the intent.
@Maxim:
Here’s my revised formula, which should handle tables with up to 1048576 cells. Not that I’ve tested it!
@Ron: that’s awfully clever. Here’s a generalised version:
…where Data is a named range pointing at the 2d block.
@Ron: give the range the name Data, then try the following:
aMaris:
Maxim:
=FIND(1,TEXT(MMULT(Data,10^(COLUMNS(Data)-ROW(INDIRECT("1:"&COLUMNS(Data))))),REPT(0,COLUMNS(Data))))Alex:
Sam:
=FIND(1,TEXT(MMULT(Data,TRANSPOSE(10^(COLUMNS(Data)-COLUMN(Data)))),REPT("0",COLUMNS(Data))))John:
Jeff:
@XOR: Might I suggest that for future challenges you stipulate that a named range called d must be used, and any other ranges must be constructed from that? That way it makes it easy for all to not only test these, but reuse them.
A sample spreadsheet wouldn’t go amiss either…was a bit of a pain to type out all those ones and zeros to match your screenshot.
Restating the formula I posted, utilizing a referenced range named “Data”:
Data is the name range
@Jeff, thanks!!!
@Ron, I still do not understand how your solution works, but tested it on 3489366 cells (213 rows and 16382 columns)… and as far as I can see, it works on this really big amount of data. Even if 1 is in last column only. Excellent!
@Ron
Excellent stuff!
Ingenious use of LARGE means that this set-up is indeed applicable to very large ranges.
Thanks a lot.
@chrisham
Thanks, but I couldn’t get your solution to work. In fact, it appears that it will only ever return a single value, not an array as required.
Can you clarify?
Regards
@Jeff
Completely agree re your point about Named Ranges – will do so for any future challenges.
I usually add the attachment, though didn’t feel it was necessary here, since in any case the actual values that I gave were of course arbitrary, and so I presumed that readers would set up their own sheets with some random-generating functions for testing purposes.
However, I shall endeavour to post the attachment in future, even if I feel it is not necessarily required.
Regards
I timed these on 10 columns * 2000 rows = 20,000 cells. Here’s the recalc times:
Ron Coderre: 24 seconds
aMaris: 17 seconds
Jeff: 0.7 seconds
Sam: 0.014 seconds
John Jairo V: 0.016 seconds
Maxim Zelensky: 0.01 seconds
Alex Groberman: 0.006 seconds
For some reason though, it took nearly 5 minutes of times from when I array-entered my formula to when it showed the result in the spreadsheet. It didn’t show ‘calculating’ in the status bar during this time. I tried several times: Excel appears to go unresponsive, and then finally returns the numbers. But when I time the ‘entered’ formulas, I get the fast time shown above.
Weird.
My friend from other forum, where asked for solution of this task, suggested this formula with limit up to 1022 columns:
Really formula works fine with 16384 columns, but 1022 is a maximum relative position for 1s.
For a range started in 1st column, he suggested shorter solution with the same limits:
@Maxim
Then tell your friend to visit this site more often! With elegant constructions such as those it would be nice indeed to have their input on a regular basis.
Regards
@Maxim
I really like that approach. It’s moderately easy to understand and it’s very flexible.
Here are examples of how I altered it to return the first position of various values for each referenced row.
First Positive value (if no value is below zero):
First Positive value (ignoring negative values, if they exist)
First Non-Zero value
First occurrence of a particular word (allowing for the word to be missing)
Hmmm…the website didn’t allow the Not-Equal sign in the First Non-Zero value. It simply removed it and left Data0.
Interesting.
@Ron
Yes – sorry about that. Have corrected now.
Regards
@XOR LX, already done! ))
Hope he will visit your site soon.
BTW, another man suggested this solution:
which, being entered CSE in range of cells on sheet, returns correct results. But it is impossible (for me) return as a result an array in any form. How it could be enveloped in other function to get an array? Or this is impossible?
and another one, this time mine 🙂
@Maxim
Ah no! Of course that multi-cell array solution is disallowed! We must create an array containing the results, not a range of worksheet cells.
And I do not believe that that set-up can be translated as such.
P.S. Re your latest offering, is NEXT a new 2013 function that I don’t know about? 🙂
Cheers
@MichaelCH
Not sure about that one. Have you tested thoroughly?
Regards
=-INT(LOG(MMULT(A1:E10,10^-(ROW(INDIRECT("1:"&COLUMNS(A1:E10)))))))a better Version from MichaelCH function (if the range ist moving)
@XOR LX – Maxim meant TEXT not NEXT
MichaelCH is the author of this fine LOG formula I have posted before. He does not speak English (almost), so he, may be, missing some things.
Formula he posted is, as far as I can see, for data range started in 1st column, and has a limit of 308 columns. It is shortest formula solution (yes, here it is Advanced challenge, but on that our forum we used to shortest formula challenge, that’s why 🙂 )
Ah, and yes, NEXT ought to be TEXT – misspelled while translated formula ))
Constructing a general formula from this fruitful discussion with the possible occurrence of errors in the data array.
Where Data is the array and Check is the comparison.
Some general comparisons:
First empty cell: (Data=””)
First non-empty cell: (Data””)
First number: ISNUMBER(Data)
First text: ISTEXT(Data)
First logical value: ISLOGICAL(Data)
First error: ISERROR(Data)
Hi Mike. Nice generalisations. Note that you don’t need that IFERROR in there.
Sorry, meant to address that to XLarium.
This construction from aMareis seems incredible to me:
I’ve not come across this before. It doesn’t evaluate correctly using either the F9 trick or the Evaluate Formula functionality.
If I’m understanding it correctly, it is producing multiple 1D arrays on the fly form Data that SUBTOTAL is then processing.
How the heck does that work? How on earth did you come across it, aMareis?
Ah. Good reading here:
http://dailydoseofexcel.com/archives/2005/05/11/arrays-with-offset/
The OFFSET function returns an “array of ranges”, …and when you apply SUBTOTAL function to that array, using 9 in SUBTOTAL you get an array of the sums of those ranges.
Un. Freakin. Believable.
@Jeff Weir
As I stated the data array could contain error values. Thus IFERROR is needed.
Ah. Good point.
@Ron: Your formula can be further simplified by ditching a pair of brackets as well as a +1 – i.e. the bits in bold below:
…which leaves this:
…although for the sake of explaining it, I find it easier to add this:
@Ron: The outer ROUND seems to be superfluous too.
This seems to work just fine:
Just for fun…
Felt simpler with…
…than where =INT(-1,9) –> -2
-INT(-A1) = ROUNDUP(A1,0)
-INT(-A1) INT(A1)+1
-INT(-1.9) = 2
-INT(-2) = 2
Hi Ola.S. Nice formulas. Note that to handle the data being located elsewhere, you’d need to amend them to:
…and
Try this array formula
I found this challenge a today. I guess I’m late to the party, but this is what I came up with:
Nice Daniel. Needs a slight modification to handle the data being located elsewhere:
Following up on Siva’s suggestion, i think this would work for any size data range:
or (rounded to nearest integer):
Excel Hero’s applies to a range with up to 18 columns and the variation of Ola.S and others around 300 columns or so.
Only two years behind!
where the index k is given as a named formula
Strictly speaking, this result is not an array because of the use of INDEX. It can be array-output to a range but cannot be aggregated further in memory.