Need to remove duplicated text values from a text Line Item

Suman Reddy

Hi Members,

 

I have an requirement of identifying unique values of customer in each department.  Please see below module which is have a dimension of departments list with three list items(D1,D,2,D3) and a line item(Customer Names) which is having concatenated names of all customers in each department with delimiter as ",". I want to remove duplicate names in each department and produce an output of unique names in each department in a sperate line item(Unique Customer Names). Please see sample data arranged in below table. 

 

Department	D1	D2	D3
L1:Customer Names(Text)	ABC,XYZ,ABC	ABC,XYZ	PQR,PQR
L2: Unique Customer Names(Text)	ABC,XYZ	ABC,XYZ	PQR

 

 

Thanks,

Suman.

KirillKuznetsov

@Suman Reddy 

Please check this formula in Anapedia: FIND

1) I assume you already have a created list of Customers in Anaplan (if no - you should create it)

Let's take your example.

Department	D1	D2	D3
L1:Customer Names(Text)	ABC,XYZ,ABC	ABC,XYZ	PQR,PQR
L2: Unique Customer Names(Text)	ABC,XYZ	ABC,XYZ	PQR

2) Then you need a module dimensioned by Customers and Departments.

3) In this module you will search the names of customers in each Department. Use FIND formula for it. The output of the formula is a place in the text where it is found.Let's take your example.

4) Let's create first line item called "Find first occurrence". Here we will search the first occurence of all customers using formula:

FIND(<customer name>, 'L1:Customer Names(Text)')

Find 1st occurrence	D1	D2	D3
ABC	1	1	0
XYZ	5	5	0
PQR	0	0	1

5) Another line item - text - will be based on the first one and be named "Customer for Department".

IF Find first occurrence = 0  THEN BLANK ELSE <customer name>

Customer for Department	D1	D2	D3
ABC	ABC	ABC
XYZ	XYZ	XYZ
PQR			PQR

 

6) Now all we need is to get everything together to see what each department has as Unique Customer Names.

This can be resolved by using TEXTLIST formula but i would not recommend you to use it as the Planual says to avoid it if it's possible. This function may lead to performance issues in the model. Please ask yourself if you really need to get all results concatenated together in one cell?

The workaround may be to pivot our Customer for Department line item and filter each combination by nonblank values to get the same list of elements. The output may look something like this:

Departments	Customers
D1	ABC
	XYZ
D2	ABC
	XYZ
D3	PQR

Let me know if you need any other explanation.