• 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.

Remake this Formula

John()

Member
Hi all i have attached a sheet with a formula i came accross in the forum the other day ... what i would like is for you to find another formula to do exactly the same thing and also to be shorter(formula length is 204 characters)
you can create a totally different formula or use part of the original formula (as maybe using part of the original is the only way to do this)
BUT YOU CAN NOT USE ... INDIRECT

Ok why do this ... well i have being working hard the last few months learning all i can about spreadsheets and my favourite part is formulas and understanding and trying to break them down to figure out how they work. One thing i learned is the better you understand individual functions the easier it is to break down formulas.
I found this to be a very interesting formula and works great ... but while trying to break it down i got confused over the INDIRECT function and it made things a bit tricky .... which led me to come up with anothe way of doing it which did the same job ... and today while revewing my formula i realised i used parts of the formula that i didnt need in my version so trimmed it down to 148 characters.
so i thought it would be nice to challange you to do the same and create or adjust this formula to do the same job without using indirect and see what you come up with. And see would anyones finished formula resemble what i ended up with.

I also figured out his use of indirect ... i just hadnt had much experience using this function and threfore didnt understand it enough at the time. so now i learned how to use this function better.
Anyway hope to see a few attempts at this ... will post my formula in a few days and hopefully will have some of your attempts to compare it too.
 

Attachments

  • JohnChandooChallange.xlsx
    10.2 KB · Views: 28
Ok on the supplied spreadsheet is a section C3:H6 which contains words/numbers and blanks ..... This has to be converted into a single column and ignore blanks ... it is done in column K .... click on any cell in K3:K22 and you will see the formula that was used
i would like you to convert same section C3:H6 and also ignore blanks into a column by creating your own formula or adjusting the formula used and see can you come up with another way of doing this.
your formula must be shorter than the one used on the spreadsheet and also may not use the function INDIRECT
 
Hi John -

Check this...but again Indirect function is used here..

http://excelxor.com/2014/08/15/adva...y-containing-blanks-2-columns-first/#more-239
lol must take a closer look at this as the challenge there is to create a formula to make a column from the section going row to row and then next column where as this one goes from column to column and then next row.

But i love to see what ye could come up with for the above.

Well had a look at his original post and he dose the above using a INDIRECT with 84 characters length formula

so can anyone come up with something that dose not use INDIRECT ?

Meanwhile im going to have a look at figuring out his challenge ...
 
Hello John,

How about this Array Formula...?

=IFERROR(INDEX($1:$1048576,SMALL(IF(rng<>"",ROW(rng)),ROW(A1)),RIGHT(SMALL(IF(rng<>"",10^7*ROW(rng)+COLUMN(rng)),ROW(A1)),7)),"")

Based on XOR LX method 10^5*ROW()+COLUMN().

So far Excel have 16384 columns (XFD). The MAX array would be 10485760016384, so should be fine RIGHT with 7 (unless I missed something...)
 
Hello John,

How about this Array Formula...?

=IFERROR(INDEX($1:$1048576,SMALL(IF(rng<>"",ROW(rng)),ROW(A1)),RIGHT(SMALL(IF(rng<>"",10^7*ROW(rng)+COLUMN(rng)),ROW(A1)),7)),"")

Based on XOR LX method 10^5*ROW()+COLUMN().

So far Excel have 16384 columns (XFD). The MAX array would be 10485760016384, so should be fine RIGHT with 7 (unless I missed something...)
Nice .... Dont think you missed anything ... works perfectly ... and its very similar to how i remade mine ... do prefer your use of 10^7 part as getting the column number with this makes the formula smaller ... i used the %% from the original and modified it but still makes for a longer formula .... yours is 20 characters shorter than mine.

Thanks for posting was wondering how others would do this and was also wondering would anybody come up with using INDEX as this is what i used.
 
lol must take a closer look at this as the challenge there is to create a formula to make a column from the section going row to row and then next column where as this one goes from column to column and then next row.

Hi.

The alternative, which appears to be what you require, is also given as a link in that post:

http://excelxor.com/2014/08/13/single-column-from-many-containing-blanks-1-rows-first/

I agree that volatility is, in general, something to be avoided, especially if the number of such constructions is large. Though here the use of INDIRECT with an R1C1 parameter offers us a nice alternative construction, so it would perhaps be a shame if it weren't even to be considered due to its being volatile. Still, it's your challenge!

By the way, 10^5 is perfectly sufficient (instead of 10^7, as I explain in that post).

Regards
 
Back
Top