Formula to be used =SORTBY(A2:A13, –(1&A2:A13)) Sample File – T&T_210
Category: Tips and Tricks
Tips & Tricks 209 – Not Common in A and B both
Formula to be used =UNIQUE(TOCOL(A2:B9),,1) Sample File – T&T_209
Tips & Tricks 208 – Present in A but not in B
To find if an entry is present in one column but not in another =UNIQUE(VSTACK(A2:A9,B2:B9,B2:B9),,1) =FILTER(A2:A9, ISERROR(XMATCH(A2:A9,B2:B9))) =FILTER(A2:A9, COUNTIF(B2:B9,A2:A9)=0) Sample File – T&T_208
Tips & Tricks 207 – Common Across Two Columns
Following formulas can be used to find common entries across 2 columns =TOCOL(XLOOKUP(A2:A9,B2:B9,B2:B9), 2) =FILTER(A2:A9, COUNTIF(B2:B9, A2:A9)) =FILTER(A2:A9, IFNA(XMATCH(A2:A9,B2:B9),)) Sample File – T&T_207
Tips & Tricks 206 – Running Total Using Dynamic Arrays
Running Total for a Range = SCAN(, A2:A10, SUM) Running Total for a Filtered Range = SUBTOTAL(9, TAKE(A2:A10, SEQUENCE(ROWS(A2:A10))))
Tips & Tricks 205 – Generate random numbers without duplicates
Say, you want 10 random numbers between 1 to 100. Then you can use following formula =INDEX(SORTBY(SEQUENCE(100), RANDARRAY(100)), SEQUENCE(10)) If rather than 1 to 100, then you can tweak SEQUENCE(100) accordingly. Say you need between 101 to 500. Then replace SEQUENCE(100) with SEQUENCE(500-101+1, , 101) => Note here, 101 is start number and 500 is…
Tips & Tricks 204 – Return Columns in Different Order in XLOOKUP
Use following formula for this =XLOOKUP(M2,C:C, CHOOSE({2,1}, D:D, F:F)) =XLOOKUP(M2,C:C, CHOOSECOLS(D:F,3, 1)) Sample File – T&T_204
Tips & Tricks 203 – Return non-adjacent columns from XLOOKUP
You can use following formula to return non-adjacent columns through XLOOKUP =XLOOKUP(M2,C:C, FILTER(D:F, {1,0,1})) =XLOOKUP(M2,C:C, CHOOSECOLS(D:F, 1, 3)) Sample File – T&T_203
Tips & Tricks 202 – Remove Alphabets from a String
To remove alphabets from a string, for a single cell =CONCAT(TEXTSPLIT(A2,CHAR(SEQUENCE(26,,97)),,,1)) Using REGEX =REGEXREPLACE(A2, "[a-zA-Z]", "") To remove alphabets from entire range =BYROW(A2:A10, LAMBDA(a, CONCAT(TEXTSPLIT(a,CHAR(SEQUENCE(26,,97)),,,1)))) using REGEX =REGEXREPLACE(A2:A10, "[a-zA-Z]", "") The Excel file for this tips can be downloaded from Tips & Tricks 202 – Remove Text from a String
Tips & Tricks 201 – Remove Numbers from Strings
To remove numbers from a string, for a single cell =CONCAT(TEXTSPLIT(A2, SEQUENCE(10,,0))) Using REGEX =REGEXREPLACE(A2, "[0-9]", "") To remove numbers from entire range =BYROW(A2:A10, LAMBDA(a, CONCAT(TEXTSPLIT(a, SEQUENCE(10,,0))))) using REGEX =REGEXREPLACE(A2:A10, "[0-9]", "") The Excel file for this tip can be downloaded from Tips & Tricks 201 – Remove Numbers from a String