(I have attached a sample table that can generate the error)
I have a formula for creating a stacked combined column based off dynamic arrays. This formula that was posted here by the thread author, tm23 seems to work, but only returns results if the source stays below a certain row limit. In the sample, you can fix the #CALC by deleting a majority of the rows. I'm thinking about a different way of writing a formula for this might work, since the solution was posted in 2020 there have been updates to Excel working with dynamic arrays.
The formulas are:
A2:
B2:
D2:
F2:
Here is tblFlatten. (reduce the number of rows to fix the error)
I have a formula for creating a stacked combined column based off dynamic arrays. This formula that was posted here by the thread author, tm23 seems to work, but only returns results if the source stays below a certain row limit. In the sample, you can fix the #CALC by deleting a majority of the rows. I'm thinking about a different way of writing a formula for this might work, since the solution was posted in 2020 there have been updates to Excel working with dynamic arrays.
The formulas are:
A2:
Excel Formula:
=NUMBERVALUE(LEFT(tblFlatten[IDx],LEN(tblFlatten[IDx])-1))
Excel Formula:
=IFNA(RIGHT(tblFlatten[IDx],1),"")&"~"&NUMBERVALUE(LEFT(tblFlatten[IDx],LEN(tblFlatten[IDx])-1))&"~"
Excel Formula:
=UNIQUE(NUMBERVALUE(LEFT(tblFlatten[IDx],LEN(tblFlatten[IDx])-1)))
Excel Formula:
=LET(
lookup_values,D2#,
lookup_array,A2#,
return_array_to_concat,B2#,
col_range_to_concat,B:B,
separator_row,"\",
separator_col,"|",
if_not_found,"No result",
array_before_split,IFERROR(INDEX(col_range_to_concat,IF((lookup_values=TRANSPOSE(lookup_array)),TRANSPOSE(ROW(return_array_to_concat)),"")),separator_col),
CountCol_array_before_split,COLUMNS(array_before_split),
array_to_split,TEXTJOIN(IF(ROUNDDOWN(SEQUENCE(CountCol_array_before_split,1,1,1/(IF(CountCol_array_before_split=1,1,CountCol_array_before_split-1))),0)>1,separator_row,separator_col),0,array_before_split),
pre_final_result,SUBSTITUTE(TRANSPOSE(INDEX((FILTERXML("<b><a>"&SUBSTITUTE(TEXTJOIN(separator_row,,array_to_split),separator_row,"</a><a>")&"</a></b>","//a")),SEQUENCE(ROWS(array_to_split),LEN(INDEX(array_to_split,1,1))-LEN(SUBSTITUTE(INDEX(array_to_split,1,1),separator_row,))+1))),separator_col&separator_col,""),
IF(pre_final_result=separator_col,if_not_found,pre_final_result))Here is tblFlatten. (reduce the number of rows to fix the error)
| IDx |
| 1a |
| 1b |
| 1c |
| 1d |
| 1e |
| 1f |
| 1g |
| 1h |
| 2a |
| 2b |
| 2c |
| 2d |
| 2e |
| 2f |
| 2g |
| 2h |
| 3a |
| 3b |
| 3c |
| 3d |
| 3e |
| 3f |
| 3g |
| 3h |
| 4a |
| 4b |
| 4c |
| 4d |
| 4e |
| 4f |
| 4g |
| 4h |
| 5a |
| 5b |
| 5c |
| 5d |
| 5e |
| 5f |
| 5g |
| 5h |
| 6a |
| 6b |
| 6c |
| 6d |
| 6e |
| 6f |
| 6g |
| 6h |
| 7a |
| 7b |
| 7c |
| 7d |
| 7e |
| 7f |
| 7g |
| 7h |
| 8a |
| 8b |
| 8c |
| 8d |
| 8e |
| 8f |
| 8g |
| 8h |
| 9a |
| 9b |
| 9c |
| 9d |
| 9e |
| 9f |
| 9g |
| 9h |
| 10a |
| 10b |
| 10c |
| 10d |
| 10e |
| 10f |
| 10g |
| 10h |
| 11a |
| 11b |
| 11c |
| 11d |
| 11e |
| 11f |
| 11g |
| 11h |
| 12a |
| 12b |
| 12c |
| 12d |
| 12e |
| 12f |
| 12g |
| 12h |
| 13a |
| 13b |
| 13c |
| 13d |
| 13e |
| 13f |
| 13g |
| 13h |
| 14a |
| 14b |
| 14c |
| 14d |
| 14e |
| 14f |
| 14g |
| 14h |
| 15a |
| 15b |
| 15c |
| 15d |
| 15e |
| 15f |
| 15g |
| 15h |
| 16a |
| 16b |
| 16c |
| 16d |
| 16e |
| 16f |
| 16g |
| 16h |
| 17a |
| 17b |
| 17c |
| 17d |
| 17e |
| 17f |
| 17g |
| 17h |
| 18a |
| 18b |
| 18c |
| 18d |
| 18e |
| 18f |
| 18g |
| 18h |
| 19a |
| 19b |
| 19c |
| 19d |
| 19e |
| 19f |
| 19g |
| 19h |
| 20a |
| 20b |
| 20c |
| 20d |
| 20e |
| 20f |
| 20g |
| 20h |
| 21a |
| 21b |
| 21c |
| 21d |
| 21e |
| 21f |
| 21g |
| 21h |
| 22a |
| 22b |
| 22c |
| 22d |
| 22e |
| 22f |
| 22g |
| 22h |
| 23a |
| 23b |
| 23c |
| 23d |
| 23e |
| 23f |
| 23g |
| 23h |
| 24a |
| 24b |
| 24c |
| 24d |
| 24e |
| 24f |
| 24g |
| 24h |
| 25a |
| 25b |
| 25c |
| 25d |
| 25e |
| 25f |
| 25g |
| 25h |
| 26a |
| 26b |
| 26c |
| 26d |
| 26e |
| 26f |
| 26g |
| 26h |
| 27a |
| 27b |
| 27c |
| 27d |
| 27e |
| 27f |
| 27g |
| 27h |
| 28a |
| 28b |
| 28c |
| 28d |
| 28e |
| 28f |
| 28g |
| 28h |
| 29a |
| 29b |
| 29c |
| 29d |
| 29e |
| 29f |
| 29g |
| 29h |
| 30a |
| 30b |
| 30c |
| 30d |
| 30e |
| 30f |
| 30g |
| 30h |
| 31a |
| 31b |
| 31c |
| 31d |
| 31e |
| 31f |
| 31g |
| 31h |
| 32a |
| 32b |
| 32c |
| 32d |
| 32e |
| 32f |
| 32g |
| 32h |
| 33a |
| 33b |
| 33c |
| 33d |
| 33e |
| 33f |
| 33g |
| 33h |
| 34a |
| 34b |
| 34c |
| 34d |
| 34e |
| 34f |
| 34g |
| 34h |
| 35a |
| 35b |
| 35c |
| 35d |
| 35e |
| 35f |
| 35g |
| 35h |
| 36a |
| 36b |
| 36c |
| 36d |
| 36e |
| 36f |
| 36g |
| 36h |
| 37a |
| 37b |
| 37c |
| 37d |
| 37e |
| 37f |
| 37g |
| 37h |
| 38a |
| 38b |
| 38c |
| 38d |
| 38e |
| 38f |
| 38g |
| 38h |
| 39a |
| 39b |
| 39c |
| 39d |
| 39e |
| 39f |
| 39g |
| 39h |
| 40a |
| 40b |
| 40c |
| 40d |
| 40e |
| 40f |
| 40g |
| 40h |
| 41a |
| 41b |
| 41c |
| 41d |
| 41e |
| 41f |
| 41g |
| 41h |
| 42a |
| 42b |
| 42c |
| 42d |
| 42e |
| 42f |
| 42g |
| 42h |
| 43a |
| 43b |
| 43c |
| 43d |
| 43e |
| 43f |
| 43g |
| 43h |
| 44a |
| 44b |
| 44c |
| 44d |
| 44e |
| 44f |
| 44g |
| 44h |
| 45a |
| 45b |
| 45c |
| 45d |
| 45e |
| 45f |
| 45g |
| 45h |
| 46a |
| 46b |
| 46c |
| 46d |
| 46e |
| 46f |
| 46g |
| 46h |
| 47a |
| 47b |
| 47c |
| 47d |
| 47e |
| 47f |
| 47g |
| 47h |
| 48a |
| 48b |
| 48c |
| 48d |
| 48e |
| 48f |
| 48g |
| 48h |
| 49a |
| 49b |
| 49c |
| 49d |
| 49e |
| 49f |
| 49g |
| 49h |
| 50a |
| 50b |
| 50c |
| 50d |
| 50e |
| 50f |
| 50g |
| 50h |
| 51a |
| 51b |
| 51c |
| 51d |
| 51e |
| 51f |
| 51g |
| 51h |
| 52a |
| 52b |
| 52c |
| 52d |
| 52e |
| 52f |
| 52g |
| 52h |
| 53a |
| 53b |
| 53c |
| 53d |
| 53e |
| 53f |
| 53g |
| 53h |
| 54a |
| 54b |
| 54c |
| 54d |
| 54e |
| 54f |
| 54g |
| 54h |
| 55a |
| 55b |
| 55c |
| 55d |
| 55e |
| 55f |
| 55g |
| 55h |
| 56a |
| 56b |
| 56c |
| 56d |
| 56e |
| 56f |
| 56g |
| 56h |
| 57a |
| 57b |
| 57c |
| 57d |
| 57e |
| 57f |
| 57g |
| 57h |
| 58a |
| 58b |
| 58c |
| 58d |
| 58e |
| 58f |
| 58g |
| 58h |
| 59a |
| 59b |
| 59c |
| 59d |
| 59e |
| 59f |
| 59g |
| 59h |
| 60a |
| 60b |
| 60c |
| 60d |
| 60e |
| 60f |
| 60g |
| 60h |
| 61a |
| 61b |
| 61c |
| 61d |
| 61e |
| 61f |
| 61g |
| 61h |
| 62a |
| 62b |
| 62c |
| 62d |
| 62e |
| 62f |
| 62g |
| 62h |
| 63a |
| 63b |
| 63c |
| 63d |
| 63e |
| 63f |
| 63g |
| 63h |
| 64a |
| 64b |
| 64c |
| 64d |
| 64e |
| 64f |
| 64g |
| 64h |
| 65a |
| 65b |
| 65c |
| 65d |
| 65e |
| 65f |
| 65g |
| 65h |
| 66a |
| 66b |
| 66c |
| 66d |
| 66e |
| 66f |
| 66g |
| 66h |
| 67a |
| 67b |
| 67c |
| 67d |
| 67e |
| 67f |
| 67g |
| 67h |
| 68a |
| 68b |
| 68c |
| 68d |
| 68e |
| 68f |
| 68g |
| 68h |
| 69a |
| 69b |
| 69c |
| 69d |
| 69e |
| 69f |
| 69g |
| 69h |
| 70a |
| 70b |
| 70c |
| 70d |
| 70e |
| 70f |
| 70g |
| 70h |
| 71a |
| 71b |
| 71c |
| 71d |
| 71e |
| 71f |
| 71g |
| 71h |
| 72a |
| 72b |
| 72c |
| 72d |
| 72e |
| 72f |
| 72g |
| 72h |
| 73a |
| 73b |
| 73c |
| 73d |
| 73e |
| 73f |
| 73g |
| 73h |
| 74a |
| 74b |
| 74c |
| 74d |
| 74e |
| 74f |
| 74g |
| 74h |
| 75a |
| 75b |
| 75c |
| 75d |
| 75e |
| 75f |
| 75g |
| 75h |
| 76a |
| 76b |
| 76c |
| 76d |
| 76e |
| 76f |
| 76g |
| 76h |
| 77a |
| 77b |
| 77c |
| 77d |
| 77e |
| 77f |
| 77g |
| 77h |
| 78a |
| 78b |
| 78c |
| 78d |
| 78e |
| 78f |
