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…
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 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
Tips & Trick 200 – Reverse A String
To reverse a string in a cell, following formula can be used =CONCAT(LEFT(RIGHT(A2,SEQUENCE(LEN(A2))))) For many cells in a range, following formula can be used =BYROW(A2:A11,LAMBDA(a,CONCAT(LEFT(RIGHT(a,SEQUENCE(LEN(a))))))) The Excel file related to this tips can be downloaded from Tips & Tricks 200 – Reverse Text
Tips & Tricks 199 – Generate Fibonacci Numbers
You can generate Fibonacci numbers through following formula =REDUCE({0;1}, SEQUENCE(20-2), LAMBDA(a,b, VSTACK(a, SUM(TAKE(a, -2))))) Here, 20 in SEQUENCE(20-2) means 20 numbers need to be generated. So, if you need to generate 100 Fibonacci numbers, just replace 20 with 100 in SEQUENCE. =REDUCE({0;1}, SEQUENCE(100-2), LAMBDA(a,b, VSTACK(a, SUM(TAKE(a, -2))))) The Excel file of this tips can be…
Tips & Tricks 198 – Reverse Multiple Ranges
In last tip, I covered how to reverse a single range. Here, I am going to cover how to reverse multiple ranges. The formula which can be used here to reverse multiple columns (here range is A2:C12) =LET(r, A2:C12, IFERROR(DROP(REDUCE("", SEQUENCE(COLUMNS(r)), LAMBDA(a,b, HSTACK(a, LET(nr, INDEX(r,,b), sr, SORTBY(nr,ROW(nr),-1), FILTER(sr, sr<>0))))), , 1), "")) The formula can…
Tips & Tricks 197 – Reverse a range
To reverse a range in Excel, it is fairly an easy job with SORTBY if you have dynamic array functions =SORTBY(A2:A10, ROW(A2:A10), -1) If you are still on drag and drop versions, here is the formula for drag and drop (Put this formula in a cell and drag it down) =INDEX($A$2:$A$10, MAX(ROW($A$2:$A$10)-ROW($A2)+1)) The Excel file…