Formula to be used =SORTBY(A2:A13, –(1&A2:A13)) Sample File – T&T_210
Author: Vijay Verma
Excel Quiz 62
Tips & Tricks 209 – Not Common in A and B both
Formula to be used =UNIQUE(TOCOL(A2:B9),,1) Sample File – T&T_209
Excel Quiz 61
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
Excel Quiz 60
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))))