Forum Discussion
Finding Unique Values
Hi Magicians!
Column B of Sheet 1 in the attached file contains the data exported from an accounting software. The Ledger Account Names are separated by Carriage Return (Alt+Enter) character. The last line is Narration, which is also separated using Carriage Return.
What I am trying to do is to create a single dynamic formula which derives a list of Unique Ledger Account Names used in the entire range. Narration line is to be ignored.
I am using Excel 365.
Regards
Kanwaljit
I'm not a magician, but how about
=LET(v, UNIQUE(TEXTSPLIT(TEXTJOIN(CHAR(10), TRUE, VSTACK(B10:B13)), , CHAR(10))), FILTER(v, ISERROR(SEARCH("Bill No", v))))
As variant
=DROP( REDUCE("", Particulars, LAMBDA(a,v, UNIQUE( VSTACK(a, DROP( TEXTSPLIT(v,,CHAR(10)), -1) ) ) ) ), 1)but with poorer performance compare to djclements variant
PeterBartholomew1
djclements
SergeiBaklan
This is exactly what makes you guys magicians. You provided 3 solutions, when I was struggling to work out one. Heartfelt thanks for being here!I had to close the question by marking one of above as best response. But that does not, in any way, diminish my appreciation for the effort you guys put it! Thanks a Lot! Sincerely!
KanwalNo1 , you are welcome, thank you for the feedback
KanwalNo1 Here's my take, using a "text to columns" approach:
=LET( delim, CHAR(10), arr, delim & TEXTBEFORE(B10:B13, delim, -1) & delim, cols, SEQUENCE(, MAX(LEN(arr) - LEN(SUBSTITUTE(arr, delim, ))) - 1), UNIQUE(TOCOL(TEXTBEFORE(TEXTAFTER(arr, delim, cols), delim), 2)) )The first use of TEXTBEFORE with the [instance_num] set to -1 removes the last line from each cell (Narration line).
The combination of TEXTBEFORE and TEXTAFTER is used to split the data into columns, using CHAR(10) as the delimiter. The width of the resulting array is determined by the item with the greatest number of delimiters. Any items with fewer delimiters than the maximum will be filled with #N/A in their excess columns. TOCOL sends the results to a single column with the errors removed by setting the [ignore] argument to 2 (ignore errors), and UNIQUE returns the final list.
I'm not a magician, but how about
=LET(v, UNIQUE(TEXTSPLIT(TEXTJOIN(CHAR(10), TRUE, VSTACK(B10:B13)), , CHAR(10))), FILTER(v, ISERROR(SEARCH("Bill No", v))))
