SOLVED

Iron Contributor

# Finding Unique Values

@Peter Bartholomew

@djclements

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

# Re: Finding Unique Values

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

# Re: Finding Unique Values

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

# Re: Finding Unique Values

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

# Re: Finding Unique Values

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

# Re: Finding Unique Values

@KanwalNo1 , you are welcome, thank you for the feedback

1 best response

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

# Re: Finding Unique Values

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