•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
"ArrayFormula" syntax translation from Google Sheets -> Excel
Our take
Are you feeling the limitations of traditional spreadsheet formulas? Transitioning from Google Sheets to Excel can be challenging, especially when it comes to using the versatile ArrayFormula syntax. In Sheets, you can effortlessly populate multiple cells with a single function, allowing for dynamic data management. However, Excel requires a different approach to achieve similar results. Discover how to utilize Excel's features to automate data population while maintaining flexibility for manual adjustments.
Hi there.
Im trying to understand some differences between Sheets / Excel.
In Google Sheets I will use the alternate array formula: (Ex. {cellvalueA, cellvalueB} as a way to populate two cells with one function. I use this to autofill information from separate tabs across ranges from TabA to TabB.
I will minimize "Column A", and have the information auto-populated in "B' for 'default info' (from TabA to TabB), but can then be manually overwritten (in Tab B) without destroying the function.
How do I do the same thing in excel? Is there a different syntax or formula I can use to achieve this? Because the "{}" doesn't work the same as far as I can tell.
Specifically what I need is the ability to use a formula to populate into a cell without the formula residing in that destination cell.
Let me know if additional context is need to answer my inquiry.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Converting Nested Array formula from Sheets to ExcelIve been working on a project for my work that originally started in Sheets but now has to be converted to Excel to be integrated. When i imported the sheet into Excel i discovered all the big formula arrays I had been provided and edited myself to add the XLOOKUP to it were broken in Excel due to using nested arrays. Is there a way to convert my Sheets formula into a Excel compatible formula? Edit for context: When the current formula is in Excel it is just showing a hover popup with "Nested Arrays are not supported" and the cell text shows #### This is the formula used at the moment which has this formula into the left most cell of the timeline sheet and it automatically puts the "x" into the related cells it correlates too =let( prior, XLOOKUP($B4,'ReferenceSheet'!A:A,'ReferenceSheet'!F:F), duration, XLOOKUP($B4,'ReferenceSheet'!A:A,'ReferenceSheet'!E:E), endTimes, XLOOKUP($B4,'ReferenceSheet'!A:A,'ReferenceSheet'!B:B), timeline, $D$1:$DZ$1, map(endTimes, lambda(end, if(end="",, map(timeline, lambda(t, if(isbetween(t, end-prior, end-prior+duration, true, false), "x",))))))) The whole thing creates a timeline of operation periods within a 24 hour period by referencing some start and duration times within the ReferenceSheet and checks what the target name is, Currently this line is from the B4 row but its designed so that whatever row I have and the name of the operation is changed, the formula adjusts the periods automatically. Its really the part about making it a non nested array formula which is what is causing the issues with the converting from Sheets to Excel This is an example of it working within Sheets. Just with some names and other related information removed https://preview.redd.it/jk2mmq4exnxg1.png?width=2120&format=png&auto=webp&s=87ae396cb5a2e52c91c16d23a4da6b40cccf7358 Any advice or suggested changes to the formula would be greatly appreciated submitted by /u/BlueThunder796 [link] [comments]
- Is there a way to reference an array of arrays?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. submitted by /u/TwitchyDingo [link] [comments]
- My company is getting rid of Excel. Is it possible to replicate this formula in GSheets?For context I have a huge report I run every month with about 13,000 columns of data and this data is consolidated from 7 other reports. So, rather than sorting and filtering the data each time I need to organize everything, I set up this formula. This way I can just paste the consolidated data into one sheet and it’ll automatically sort and filter appropriately into 4 other sheets This is one of the formulas I use for 1 of the 4 sheets: =SORT(SORT(FILTER(FILTER('Compiled Data'!A:AB,(('Compiled Data'!E:E="variable1")+('Compiled Data'!E:E="variable2")+('Compiled Data'!E:E="variable3")+('Compiled Data'!E:E="variable4"))*(('Compiled Data'!F:F="variable5")+('Compiled Data'!F:F="variable6")+('Compiled Data'!F:F="variable7"))),{1,1,1,1,1,1,0,0,1,1,1,0,0,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0}),1,1),4,1) Is this possible to replicate in Google Sheets? submitted by /u/KaptMelch [link] [comments]
- Formulas Across Worksheets that are Dynamic and can Accomodate new sheets/conditionsNot sure if title makes sense but here is a problem I'd like to solve (as simply as possible). Lets just say I want to sum the same cell on a number of worksheets but I want to be able to add worksheets. That can be accomplished by putting sheets between two sheets called First:LastCell... at least that's what google told me and it worked. So if I add sheets between First and Last it will get captured in the formula. So what if I want to do the same thing but based on a condition in each sheet? As in I have a cell reference call out to either exclude or include for each sheet in the formula? Apologies if this is confusing. submitted by /u/NegativeActivity3184 [link] [comments]
Tagged with
#Excel alternatives for data analysis#AI formula generation techniques#google sheets#Excel compatibility#rows.com#natural language processing for spreadsheets#generative AI for data analysis#financial modeling with spreadsheets#ArrayFormula#Google Sheets#Excel#autofill#formula#populate#syntax#TabA#TabB#column#manual overwrite#cellvalueA