Advanced Formula Challenge #13: Single Array Containing All Entries From a Given Range in Multiple Worksheets 22

The challenge this week is as follows: a single formula to return an array which consists of all entries (of potentially mixed datatype) from a given range in multiple worksheets.

No restrictions are placed on the dimensions of the returned array.

For example, using the attached file (download here), the formula should return an array such as:

{18,"",19,63,"","",67;"",46,"","","L","",7;"N","Z","","F",70,19,"";"","","","","","",73;"","","T","","",88,"Y";"U","","B","F","Q",71,"";"","H","U","R",86,84,"Q";11,"R",84,"","",43,72;39,53,"","","R",60,80;"","V","B",33,"",46,39;"","B","","","V","","";"P","","M","","","",85;"O","",9,"","","","";"","I",9,"","S","S",59;"","X","J","","","","X";46,72,"",22,"",67,"P";35,32,69,"O","","","V";24,"","","","R","","";"",5,"B",69,"C","","";"P","A",19,"","","K","B"}

or a dimensional variation thereof.

For the sake of consistency, the specified range (A1:G4 in the attached example) should be referred to as the Defined Name Rng in any formulas. Likewise for any references which refer to a collection of worksheets, e.g.:

={"Sheet1","Sheet2","Sheet3","Sheet4","Sheet5"}

which should be given the Defined Name Sheets.

Also please note that this is not a Shortest Formula Challenge! Hence rigour should be preferred over brevity in any solution.

Solution next week. Good luck!

22 comments

  1. Great challenge!

    Here goes:

    =INDIRECT(SUBSTITUTE(T(INDEX(Sheets&"!R"&MODE.MULT(ROW(Rng)+("."&COLUMN(Rng)),ROW(Rng)+("."&COLUMN(Rng))),)),".","C"),)

    The “evaluate formula” may show errors but it will return the correct results if wrapped around SUM or COUNTA as examples.

  2. On second thought my formula won’t work with any column number ending in zero (e.g. column 10). Here’s another shot at it:

    =INDIRECT(T(INDEX(Sheets&"!R"&MOD(ROW(INDIRECT("A1:A"&ROWS(Rng)*COLUMNS(Rng)))-1,ROWS(Rng))+1&"C"&INT((ROW(INDIRECT("A1:A"&ROWS(Rng)*COLUMNS(Rng)))-1)/ROWS(Rng))+1,)),)
  3. i like Alex’s approach, but think it might need wrapping in IF(1,CELL(“contents”,…)) for it to work inside functions like COUNT and MEDIAN and also to be able to array enter over a range of cells.

    A 2013 variation that i believe will also work in different language versions is:

    =CELL("contents",IF(1,+INDIRECT("'"&Sheets&"'!"&BASE(MODE.MULT(DECIMAL(ADDRESS(ROW(Rng),COLUMN(Rng),4),36),DECIMAL(ADDRESS(ROW(Rng),COLUMN(Rng),4),36)),36))))
  4. Wow lori, that is fantastic! It never occurred to me that any cell reference could be identified numerically using base 36. This opens up a ton of possibilities!

    -Alex

  5. Also of note, this DECIMAL technique will work for all rows up to column ZZ, or for all columns up to row 999,999.

    For AAA1000000 to XFD1048576, Excel’s precision isn’t high enough. Still, a very good working domain overall!

    -Alex

  6. alex – according to MS help on the DECIMAL function it supports all numbers up to 2^53 (which is consistent with the standard floating point double representation). Since this is greater than 36^10 this should support any cell reference up to ten digits including XFD1048756 I believe but I haven’t had a chance conclusively test this.

  7. Adapting lori’s formula to a pre-2013 solution:

    =CELL("contents",IF(1,+INDIRECT("'"&Sheets&"'!"&ADDRESS(INT((ROW(INDIRECT("A1:A"&ROWS(Rng)*COLUMNS(Rng)))-1)/COLUMNS(Rng))+MIN(ROW(Rng)),MOD(ROW(INDIRECT("A1:A"&ROWS(Rng)*COLUMNS(Rng)))-1,COLUMNS(Rng))+MIN(COLUMN(Rng))))))

    This should return identical results to the solution using DECIMAL.

  8. You’re right (as always!) lori, I was fooled because for example the formula

    =DECIMAL("XFD1048576",36)

    Shows a result in the cell of 3394816136683600, and I misinterpreted the rounding of the visible result to mean that Excel was losing precision. But having then translated that result back into base 36, I can confirm it returned the correct answer.

  9. Yeah, applying BASE(…,10) to the formula result shows it actually ends in “02”. This display rounding can be a source of much confusion for users, for example entering 1 in A1 and =1+1E-15 in B1 we see that both return the same display result, also putting =A1-B1 in C1 returns zero, it is only by placing parentheses around the formula =(A1-B1) that we can see the underlying numbers are different.

    And perhaps it might be worth adapting the pre-2013 formula to use OFFSET which i understand is a little more efficient:

    =CELL("contents",IF(1,+OFFSET(INDIRECT(ADDRESS(MIN(ROW(Rng)),MIN(COLUMN(Rng)),,,Sheets)),INT((ROW(INDIRECT("1:"&COUNTA(+Rng)))-1)/COLUMNS(Rng)),MOD(ROW(INDIRECT("1:"&COUNTA(+Rng)))-1,COLUMNS(Rng)))))

    (though still less readable!) Maybe xor lx had something like this in mind – we shall see…

  10. =INDIRECT(INDEX(Sheets,N(INDEX(MOD(ROW(OFFSET(A1,,,ROWS(Rng)*COUNTA(Sheets)))-1,COUNTA(Sheets))+1,)))&"!"&ADDRESS(INDEX(ROW(Rng),N(INDEX((ROW(OFFSET(A1,,,ROWS(Rng)*COUNTA(Sheets)))-1)/COUNTA(Sheets)+1,))),COLUMN(Rng)))
  11. =INDIRECT(T(INDEX(Sheets&TEXT(MODE.MULT(ROW(Rng)*10^5+COLUMN(Rng),ROW(Rng)*10^5+COLUMN(Rng)),"!R0C00000"),)),)
  12. Wow MichaelCH, that formula is tight! I think you still need to wrap it in CELL(“contents”,…) though for the reasons Lori outlined above. But outrageous nonetheless!

  13. With CELL():

    =CELL("contents",IF(1,+INDIRECT(Sheets&TEXT(MODE.MULT(ROW(Rng)*10^5+COLUMN(Rng),ROW(Rng)*10^5+COLUMN(Rng)),"!R0C00000"),)))
  14. XOR LX, May we take some “liberties” with a rule. Specifically may

    ={"Sheet1","Sheet2","Sheet3","Sheet4","Sheet5"}

    be defined as a column?

    ={"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5"}

    Dave

  15. Lori, Alex, and/or Michael,

    Assume each tab had an additional column whose values should serve as criteria for a count, sum, average, min, max, etc. Can you show how your formulas might be adapted to incorporate that logic?

    I am thinking along the lines of the XOR LX post from Jan 8, 2016 — Incrementing Indirect Column References Within SUMIF(S)/COUNTIF(S) — but of course needing to account for this case involving the data range and criteria range being of different sizes.

  16. Pingback: Advanced Formula Challenge #13: Results and Discussion « EXCELXOR

  17. And, you can use these solutions in conjunction with the Excel 2016 TEXTJOIN function to make delimited strings (among other things)for multi-sheet ranges.

  18. Pingback: Using the Excel TEXTJOIN Function To Return Unique Items In A One-Cell Delimited String From A 2D and 3D Range By David Hager | Excel For You

  19. Probably worth mentioning for reference that in Office 365 one can convert the 3D range directly to a vertical array without even needing the list of sheet names:

    =IFERROR(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",0,Sheet1:Sheet5!A1:G4)&"</b></a>","//b"),"")

    (Credit to D.Hager & B.Jelen in follow up post to link above)

  20. Pingback: #Excel Super Links #55 – shared by David Hager | Excel For You

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s