•4 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Is there a way to reference an array of arrays?
Our take
Navigating large datasets in Excel can be daunting, especially when formulas need to be dragged down unnecessarily, creating empty rows. You’re seeking a way to reference an array of arrays within your formulas, allowing for dynamic adjustment based on your data size. While traditional methods may fall short, exploring functions like BYROW, SCAN, and MAP could offer innovative solutions to streamline your approach.
Is there a way to get Excel to use an array of arrays in in a formula? Instead of writing a formula that references an array and dragging it down through a column, can I write the formula in a way that results in an output of SUM(O2#),SUM(O3#),SUM(O4#),SUM(O5#),...,SUM(O1000#)}?
I work with enormous data sets, but they vary in size. Since I need the workbooks to work with any amount of data, I end up dragging formulas down extra far, leaving rows that just output empyy values when the data doesn't reach that far. If I can make the formula reference an array of arrays then I could eliminate all those garbage rows. Thank you.
Unfortunately, because a security restrictions, I can't use macros or VBA at all. I need to do it everything through standard excel syntax.
EDIT:
Sorry, I didn't make it clear that I used SUM() as an example to try and simplify and generalize. Here's a more explicit example of what I'm doing:
| ID | Data | Col C | Col D |
|---|---|---|---|
| A | 14 | =UNIQUE(ID#) | =IF($C1="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C2,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) |
| A | 23 | =IF($C2="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C2)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) | |
| A | 42 | =IF($C3="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C3,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) | |
| B | 1 | =IF($C4="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C4,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) | |
| B | 2 | =IF($C5="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C5,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) | |
| B | =IF($C6="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C6,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) | ||
| B | =IF($C7="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C7,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) | ||
| B | 90 | =IF($C8="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C8,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) | |
| B | 94 | =IF($C9="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C9,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) | |
| =IF($C10="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C10)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) | |||
| ... | |||
| =IF($C1000="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C1000,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) |
Output:
| ID | Data | Col C | Col D | Col E | Col F | F |
|---|---|---|---|---|---|---|
| A | 14 | A | 14 | 23 | 42 | |
| A | 23 | B | 1 | 2 | 90 | 94 |
| A | 42 | "" | ||||
| B | 1 | "" | ||||
| B | 2 | "" | ||||
| B | "" | |||||
| B | "" | |||||
| B | 90 | "" | ||||
| B | 94 | "" | ||||
| "" | ||||||
| ... | ||||||
| "" |
There are a lot of data manipulations omitted, so if something seems weird (like the empty cells in Data Col) please ignore it. The questions is how to eliminate the unneeded rows in Col D. Thank you.
EDIT2: Studying the BYROW, SCAN, and MAP suggestions, led to this page on Exceljet, https://exceljet.net/glossary/array-of-arrays , that suggests my question is a known problem in Excel's engine and there is no work-around. Thank you to everyone who tried to help.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Cell merging / formatting formulasThis might be an odd one. I'm not that skilled with excel as my use of in within my job is pretty limited. However, I tend to use this template my predecessor made to summarize data from our program. Works well, just a simple ='SHEET 1'!A1 for all cells. The first two images give an example. After the data is ported, I have to get rid of the zeros between the data and write system names. When it comes to pasting it on letters, the names are bolded, upped a font size, and two of the cells are merged (3rd image) This gets a bit tedious as the lists can get pretty long so I've been trying to figure out how to streamline it on my own. My idea has so far has been to have a separate cell detect when I'm finished adding my data and then format the aforementioned cells (4th image). For the life of me, just can't figure out how to write a formula to do it. What I would need is for the formula to detect a 1 (could be anything) in cell G10. It would then check for any blank cells in columns A and B. Once found, it would merge & center, bold the text, increase the font size, and align right. Is this only possible with a macro? I've been unable to find any formulas that could accomplish this. https://preview.redd.it/853mpr4jspog1.png?width=788&format=png&auto=webp&s=943a34a57f1d7c7f88256dd89b81b9c6fc301e34 https://preview.redd.it/e3w6ms4jspog1.png?width=453&format=png&auto=webp&s=9b189fa77d6638b627f8676dad60bc148753617c https://preview.redd.it/msh6ys4jspog1.png?width=411&format=png&auto=webp&s=d5f5025b315f24fd5f3a64c6e07e019abd3a77ab https://preview.redd.it/7j66tt4jspog1.png?width=936&format=png&auto=webp&s=1faa4df12ee74892f5fa9dc27ac95621280cf3c5 submitted by /u/Extension_Train9093 [link] [comments]
- How to deal with a bulky spreadsheet that is starting to hit the limits of Excel?Hello all, I have been venturing on quite the Excel journey the past year or so. I made a corporate spreadsheet that is approaching 500k formulas and that is starting to get serious speed issues at this point. It is 2026, so I conversed with ChatGPT several times regarding the speed issue, but realized I am way better off asking the experts here anyways. What is the problem So, my spreadsheet imports flat databases with specific information regarding objects that need further analysing. The imported flat databases run from say A tot CC or something, from which I probably draw about 12-15 datafields that are used for further analysis. It 'may' be more in the future. Afterwards, said data gets 'enriched' (manually) by things that aren't in the database, also because said data needs a human eye that cannot be automated. So far, so good. Right now, each object gets analysed from several different angles. As it stands, my spreadsheet runs from A until NA or something on the Formula Page. Many columns receive data from preceding columns, that are in the turn the result of many (slightly complex) logical IF or IFS tests, many of which are nested 3 or 4 deep. Often, they work in conjunction with X.LOOKUP to retrieve values, as the columns on the formula page are not equal. For example: A until BC on the Formula Page may analyze 150 objects, BD until DD may analyse 100 objects (from the same dataset, so narrower), and so forths. Thus a lot of X.LOOKUP is required, also because the first 'block' comes up with values that need to be found with X.LOOKUP. Also, values need to be retrieved from the flat database 'import' page with X.LOOKUP. Finally, X.LOOKUP is an insurance compared to FILTER, as I am not fully convinced that empty values in the flat database always contain a space (" "). To get to the point I use many IF, IFS, AND, and if need be, OR, formulas. Thinks: tens of thousands, probably in excess of 100k. These are compounded with X.LOOKUP, or X.LOOKUP gets used copiously without those. Here too, think tens of thousands. These formulas are - as much as possible - in array format, even though I find it controversial to do that as I consider how it can create a chain of updates throughout the spreadsheet. 'Dependencies' is the name of the game, with one object receiving many possible alterations / adjustments due to manual input data, for which the spreadsheet needs to provide. Right now, when I update a value, it may take up to 4 seconds to update the spreadsheet, which is already beyond the annoyance point for me. This leads me to these (hopefully) simple questions: Is it smart to use array formulas, knowing that each thing I change should only impact that one object line (for example, row 488) and none other? It is important to mention that object 1 does not influence object 488, or any other. Any manual data field only effects the object in the row it is in. In my mind, array formulas do not make sense in that regard, as it can result in a cascade of updates, but apparantly array formulas are 'way more efficient'. Is use of a VBA library the way to go to reduce lag and create more of an instant spreadsheet again? I am not able to code in VBA yet, but I am in the slow process of learning it regardless. Alternatively: should I use LET whenever a repeated lookup is needed in the same formula? Really looking for to your answers! submitted by /u/EvolvedRevolution [link] [comments]
- How to sum a conditional formula across multiple cells?Hi, I'm looking for an elegant solution to this problem. Mostly just a curiosity because I currently do have it working, but I know there is a cleaner way to do this. Basically I am trying to sum the number values from a row of 5 cells, where the cell may or may not start with a letter (i.e., the row could be "8, 9, S6.5, 8, A6" and I want the result of 8+9+6.5+8+6=37.5). I have many rows of this, and the summed value ends up in the column to the right of the 5 values. I've managed to do this with the following formula, where the result is in cell J5: =IF(ISTEXT(E5), RIGHT(E5, LEN(E5)-1), E5) + IF(ISTEXT(F5), RIGHT(F5, LEN(F5)-1), F5) + IF(ISTEXT(G5), RIGHT(G5, LEN(G5)-1), G5) + IF(ISTEXT(H5), RIGHT(H5, LEN(H5)-1), H5) + IF(ISTEXT(I5), RIGHT(I5, LEN(I5)-1), I5) But, if I recall from years ago, there is a way to make the formula act on an array of cells using ctrl+shift+enter so the formula would essentially look like this, and do the same thing: ={SUM(IF(ISTEXT(E5:I5), RIGHT(E5:I5, LEN(E5:I5)-1), E5:I5))} Thing is, it doesn't seem to cooperate, and I have gotten pretty rusty with excel. This array formula will sum values but not if there is text in the cell. Example data: M T W Th F SUM T8 T8 9 9 5 39 9 8 8 A8 8 41 8 8 8 8 8 40 S8 9.5 8 8 6 39.5 submitted by /u/ganduvo [link] [comments]
- Is there a way to Sumifs up to a certain column with multiple criteria?Hi everyone, I’m looking for a formula to sum all of the columns starting from a particular column up to a particular column. How do I best do that? On the data tab, Row 1 I have the following columns: A1 = date B1 = serial number C1 = customer name D1 = date 1, with the data being $ E1 = date 2, with the data being $ F1 = date 3, with the data being $ In a separate tab I have the same thing mirroring the data tab, but without the $, for the calculation what I intend to do is on E2, I want to say if $A2 = E$ 1, then sum all of D up to E column on the data tab provided that the value on B column of the data tab match B2 of the calc tab, and C column of the data tab match C2 of the calc tab. For F2, it would be the same thing, but sum column D up to column F instead of column E. I hope it makes sense. Thanks for your help! submitted by /u/botng [link] [comments]
Tagged with
#Excel alternatives for data analysis#big data management in spreadsheets#generative AI for data analysis#conversational data analysis#real-time data collaboration#intelligent data visualization#data visualization tools#enterprise data management#big data performance#data analysis tools#data cleaning solutions#rows.com#AI formula generation techniques#Excel compatibility#formula generator#Excel alternatives#financial modeling with spreadsheets#array of arrays#Excel#formula