I just want to convey that the content of this site is completely copyright free. You can use the content the way you want without seeking any permission. Whether you are using it commercially or non-commercially, personally or within your organization, it doesn't matter. There is no need to give any acknowledgement either that you…
Tips & Tricks 210 – Sort Month Name
Formula to be used =SORTBY(A2:A13, –(1&A2:A13)) Sample File – T&T_210
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
Challenges have a new Home
I have been posting daily challenges on Linkedin since last 3 years. You can access more than 1000 Excel + Power Query Challenges here. The best part if learning by reading through the solutions posted by other leading experts. https://www.linkedin.com/in/excelbi/
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