•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
TRIMRANGE, dot operator, or other?
Our take
If you're looking for an efficient way to concatenate data from columns A and B in your spreadsheet while ensuring that your formulas automatically adjust to new data, consider using the dot operator with dynamic ranges. Instead of relying on lengthy formulas like =DROP(TRIMRANGE(A:A)&TRIMRANGE(B:B),1), a more streamlined approach is to utilize the formula =ARRAYFORMULA(A2:A & B2:B). This method not only simplifies your formula but also automatically expands as you add new rows, keeping your data management seamless and intuitive.
What is the best way to apply formulas in column C that simply concatenates all of the data in columns A and B, but expands if columns A and B go from 50 rows to say 60 rows with new data so you don’t have to drag down formulas? I have headers in row 1. I’ve been using in C2: =DROP((A:.A&B:.B),1). Now with TRIMRANGE, I can do =DROP(TRIMRANGE(A:A)&TRIMRANGE(B:B),1). However that seems no better with this newer function, and if anything a lengthier formula. What’s the best way to do this?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- 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]
- Generating a formula in a cell re-iteratively?Is there anyway using the newish functions (sorry I am an old version excel guy) to trim this down into one line using LET/SEQUENCE/ etc =VSTACK( IF(BA2-(BA2-1)>BA2,HSTACK("","","","","","","",""),VSTACK(HSTACK("","","","",INDEX(AZ:AZ,BA2-(BA2-1)),INDEX(AY:AY,BA2-(BA2-1)),"",""),FILTER(AP:AW,(AW:AW=INDEX(AY:AY,BA2-(BA2-1)))*(AW:AW<>"")))), IF(BA2-(BA2-2)>BA2,HSTACK("","","","","","","",""),VSTACK(HSTACK("","","","",INDEX(AZ:AZ,BA2-(BA2-2)),INDEX(AY:AY,BA2-(BA2-2)),"",""),FILTER(AP:AW,(AW:AW=INDEX(AY:AY,BA2-(BA2-2)))*(AW:AW<>"")))) ) it actually repeats 20 times I have just trimmed it to 2 iterations here I appreciate any insight submitted by /u/FFootyFFacts [link] [comments]