SOLVED

Finding Unique Values

Iron Contributor

@Peter Bartholomew

@djclements

@SergeiBaklan 

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

5 Replies
best response confirmed by KanwalNo1 (Iron Contributor)
Solution

@KanwalNo1 

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))))

@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.

@KanwalNo1 

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

@Peter Bartholomew
@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

1 best response

Accepted Solutions
best response confirmed by KanwalNo1 (Iron Contributor)
Solution

@KanwalNo1 

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))))

View solution in original post