Help with TextJoin and obtain Unique values (remove duplicates)

D

Dani_LobP

Board Regular
Joined
Aug 16, 2019
Messages
134
Office Version
  1. 365
Platform
  1. Windows
Hi all,

Hope someone can help me figure out why i am not getting the right outcome from the formula.

I am trying to obtain some kind of summary, based on a vlookup for a person.
So the idea is that by lookup the person name, then i will get 3 columns, providing different details connected to that name.

In column B i have the name of the person.
And then in another tab i have a table called GrayPlus, i have the name of the person (Legal Name (Raw)), item1 i want to display (Role name in training), item 2 i want also to display in another column (course ID) and item 3 (scenario ID).

=TEXTJOIN(", ",TRUE,UNIQUE(IF(GrayPlus[[Legal Name (Raw)]:[Role name in training]]=B3,GrayPlus[Role name in training],"")))

Formula will bring results... but from what i see, there are many repetitions. So i am unsure in why Unique is not helping...

Truth is that in the GrayPlus table, the same name will appear repeated in many rows, so chances that item1 2 or 3 is repeated also many times.
One example, is a name, that should display just 5 items1 , but instead will appear 28 times, with duplications of those 5 many times. and so on with item2 and item3.. .hope it makes sense the explanation.

then i also tried making another column that should display the same results... but based on a condition from another column in the table.

=IF(VLOOKUP([@[DITL participant]],'Participants+'!B:H,7,0)="Yes",TEXTJOIN(", ",TRUE,UNIQUE(IF(GrayPlus[[Legal Name (Raw)]:[Role name in training]]=B3,GrayPlus[Role name in training],""))),"")

So its checking again the name in B column, and looking it up in the table, and see if there is a Yes in column, in which case, will do the textjoin. In this case, the number of items displayed should be way less than the total. since not all items will have a Yes...

Hope it makes sense what i tried to explain above.
And not sure if there would be an easier way to do what i try to do... accept suggestions too :)

thanks in advance!
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Can you post some sample data of both sheets, along with your formula.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
Hi thanks for reply, not sure if i can install that tool in this pc. but hopefully tables below help to see/understand better?
Have to make it up a bit because not fully sure i can share the data... :D I'm trying to help a colleague.

GrayPlus table
Legal Name (Raw)Role name in trainingCourse IDCourse ID in BlueTable?Scenario ID
Name1Role1Course1No
Name1Role1Course1No
Name2Role1Course1No
Name3Role1Course2No
Name4Role1Course1No
Name5Role1Course2No
Name6Role2Course3No
Name7Role2Course3No
Name8Role2Course2No
Name1Role2Course2No
Name1Role2Course2No
Name2Role2Course2No
Name3Role2Course3No
Name4Role3Course4No
Name5Role3Course4No
Name6Role3Course1No
Name7Role6Course1Yes
Name8Role6Course1No
Name1Role6Course2No
Name1Role6Course1No
Name2Role6Course2Yes
Name3Role6Course3No
Name4Role6Course3No
Name5Role6Course2No
Name6Role6Course2No
Name7Role6Course2No
Name8Role6Course2No
Name4Role6Course3No
Name5Role6Course4No
Name6Role6Course4No
Name7Role6Course1No
Name8Role6Course1No
Name4Role6Course1No
Name5Role6Course2No
Name6Role6Course1No
Name7Role6Course2No
Name8Role6Course3No
Name4Role6Course3No
Name5Role6Course2No
Name6Role6Course2No
Name7Role6Course2No
Name8Role6Course2Yes9e, 71c, 11b, 113c, 10d
Name1Role6Course3No
Name1Role6Course4No
Name2Role6Course4No
Name3Role6Course1No
Name4Role6Course1No
Name5Role4Course1No
Name6Role4Course2No
Name7Role4Course1No
Name8Role4Course2No
Name1Role4Course3Yes9e, 71c, 11b, 113c, 10d
Name1Role4Course3No
Name2Role4Course2No
Name3Role1Course2No
Name4Role1Course2No
Name5Role4Course2No
Name6Role4Course3No
Name7Role4Course4No
Name8Role4Course4No
Name1Role4Course2Yes9e, 71c, 11b, 113c, 10d
Name1Role4Course2No
Name2Role4Course2No
Name3Role5Course3No
Name4Role5Course4No
Name5Role5Course4No
Name6Role5Course2No
Name7Role5Course2Yes9e, 71c, 11b, 113c, 10d
Name8Role5Course2No
Name4Role5Course3No
Name5Role5Course4No
Name6Role5Course4No
Name7Role5Course2No
Name8Role5Course1No
Name4Role4Course2No
Name5Role4Course3No
Name6Role4Course3No
Name7Role4Course2No
Name8Role4Course1Yes9e, 71c, 11b, 113c, 10d
Name4Role4Course1No
Name5Role4Course1No
Name6Role4Course2No
Name7Role4Course1No
Name8Role4Course2No
Name9Role4Course3No
Name10Role4Course3Yes9e, 71c, 11b, 113c, 10d
Name11Role4Course2No
Name12Role4Course2No

Above would be some sample of the tab where GrayPlus table is, the column H where i try do the lookup for the Yes is Course ID in bluetable? as you can imagine. I removed some other columns and made up data, hopefully makes sense or helps to solve the issue.

The sheet were i try use formulas to make summary should look like below:



DITL participant (column B)Role name in trainingCourse IDScenario ID
Name1Role1, Role2, Role3Course1, Course2, Course39e, 71c, 11b, 10d
Name2Role2, Role4Course 3, Course4, Course5
Name3Role1, Role2, Role3Course1
Name4Role5Course29e, 71c, 11b, 10d

Formula in Role name in training: =TEXTJOIN(", ",TRUE,UNIQUE(IF(GrayPlus[[Legal Name (Raw)]:[Role name in training]]=B3,GrayPlus[Role name in training],"")))
Formula in Course ID: =TEXTJOIN(", ",TRUE,UNIQUE(IF(GrayPlus[[Legal Name (Raw)]:[Course ID]]=B3,GrayPlus[Course ID],"")))
Formula in Scenario ID: =TEXTJOIN(", ",TRUE,UNIQUE(IF(GrayPlus[[Legal Name (Raw)]:[Scenario ID]]=B3,GrayPlus[Scenario ID],"")))

Then i would have 3x more columns, with same formulas, but at the start have: =IF(VLOOKUP([@[DITL participant]],GrayPlus[[Legal Name]:[Course ID in BlueTable?]],7,0)="Yes"

But instead, shows as below:

DITL participantRole name in trainingCourse IDScenario ID
Name1Role1, Role2, Role3,Role1, Role2, Role3,Role1, Role2, Role3,Role1, Role2, Role3Course1, Course2, Course3,Course1, Course2, Course3,Course1, Course2, Course39e, 71c, 11b, 10d,9e, 71c, 11b, 10d,9e, 71c, 11b, 10d
Name2Role2, Role4,Role2, Role4,Role2, Role4,Role2, Role4Course 3, Course4, Course5,Course 3, Course4, Course5,Course 3, Course4, Course5,Course 3, Course4, Course5
Name3Role1, Role2, Role3,Role1, Role2, Role3,Role1, Role2, Role3Course1,Course1,Course1
Name4Role5,Role5,Role5,Role5,Role5Course2,Course2,Course2,Course2,Course29e, 71c, 11b, 10d,9e, 71c, 11b, 10d,9e, 71c, 11b, 10d

Instead of providing item that appears 1 time and ignore duplications, it simply add again if it finds the name in another row... I thought Unique would be removing duplications.
 
Upvote 0
Thanks for that, this is what I get with that data & your formula which looks to be correct, except for the last column.
+Fluff 1.xlsm
ABCDE
1DITL participantRole name in trainingCourse IDScenario ID
2Name1Role1, Role2, Role6, Role4Course1, Course2, Course3, Course40, 9e, 71c, 11b, 113c, 10d
3Name2Role1, Role2, Role6, Role4Course1, Course2, Course40
4Name3Role1, Role2, Role6, Role5Course2, Course3, Course10
5Name4Role1, Role3, Role6, Role5, Role4Course1, Course4, Course3, Course20
Summary
Cell Formulas
RangeFormula
C2:C5C2=TEXTJOIN(", ",TRUE,UNIQUE(IF(GrayPlus[[Legal Name (Raw)]:[Role name in training]]=B2,GrayPlus[Role name in training],"")))
D2:D5D2=TEXTJOIN(", ",TRUE,UNIQUE(IF(GrayPlus[[Legal Name (Raw)]:[Course ID]]=B2,GrayPlus[Course ID],"")))
E2:E5E2=TEXTJOIN(", ",TRUE,UNIQUE(IF(GrayPlus[[Legal Name (Raw)]:[Scenario ID]]=B2,GrayPlus[Scenario ID],"")))
 
Upvote 0
What happens if you use these formulae
+Fluff 1.xlsm
ABCDE
1DITL participantRole name in trainingCourse IDScenario ID
2Name1Role1, Role2, Role6, Role4Course1, Course2, Course3, Course49e, 71c, 11b, 113c, 10d
3Name2Role1, Role2, Role6, Role4Course1, Course2, Course4 
4Name3Role1, Role2, Role6, Role5Course2, Course3, Course1 
5Name4Role1, Role3, Role6, Role5, Role4Course1, Course4, Course3, Course2 
Summary
Cell Formulas
RangeFormula
C2:C5C2=TEXTJOIN(", ",TRUE,UNIQUE(FILTER(GrayPlus[Role name in training],GrayPlus[Legal Name (Raw)]=B2,"")))
D2:D5D2=TEXTJOIN(", ",TRUE,UNIQUE(FILTER(GrayPlus[Course ID],GrayPlus[Legal Name (Raw)]=B2,"")))
E2:E5E2=TEXTJOIN(", ",TRUE,UNIQUE(FILTER(GrayPlus[Scenario ID],GrayPlus[Legal Name (Raw)]=B2,"")))
 
Upvote 0
Solution
Oh boy. So what can be reason for not working on my side?

This is 1 example:

GrayPlus table
Legal NameRole name in trainingCourse ID
JoseSTP - STP AnalystPRO-C-03
JoseSTP - STP AnalystPRO-C-09
JoseSTP - STP AnalystPRO-C-08
JoseSTP - STP AnalystPRO-C-07
JoseSTP - STP AnalystProc-C-01

DITL participantRole name in trainingCourse ID
Jose STP - STP Analyst, STP - STP AnalystPRO-C-03, PRO-C-03, PRO-C-09, PRO-C-09, PRO-C-08, PRO-C-08, PRO-C-07, PRO-C-07, Proc-C-01, Proc-C-01

From what i understand, it should just be displaying STP - STP Analyst 1 time only, and Course ID, one time each... right?
 
Upvote 0
Fluff said:
What happens if you use these formulae
+Fluff 1.xlsm
ABCDE
1DITL participantRole name in trainingCourse IDScenario ID
2Name1Role1, Role2, Role6, Role4Course1, Course2, Course3, Course49e, 71c, 11b, 113c, 10d
3Name2Role1, Role2, Role6, Role4Course1, Course2, Course4 
4Name3Role1, Role2, Role6, Role5Course2, Course3, Course1 
5Name4Role1, Role3, Role6, Role5, Role4Course1, Course4, Course3, Course2 
Summary
Cell Formulas
RangeFormula
C2:C5C2=TEXTJOIN(", ",TRUE,UNIQUE(FILTER(GrayPlus[Role name in training],GrayPlus[Legal Name (Raw)]=B2,"")))
D2:D5D2=TEXTJOIN(", ",TRUE,UNIQUE(FILTER(GrayPlus[Course ID],GrayPlus[Legal Name (Raw)]=B2,"")))
E2:E5E2=TEXTJOIN(", ",TRUE,UNIQUE(FILTER(GrayPlus[Scenario ID],GrayPlus[Legal Name (Raw)]=B2,"")))
Click to expand...
Will check it, looks promising!
 
Upvote 0
Seems to be working partially. so will check why some cases works, and why in other cases it gives a duplicate stil... not really sure how the filter formula works :D
 
Upvote 0
It sounds as though some of your values may have leading/trailing spaces.
 
Upvote 0
Fluff said:
It sounds as though some of your values may have leading/trailing spaces.
Click to expand...
found the reason. Because i am trying to pull the result, from a colum that has a similar formula.
so actually is not duplicates, but is concatenating 2 items already concatenated..
so if its 9, 10, 12... and in another line 9, 10 ... it will give me 9, 10, 12, 9, 10 ... so it makes sense why it shows duplication. will just have to change that source then :)
Thanks for the help.

And does the Vlookup formula make sense to you? not sure if its not working as i think it should.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Combine Cells when same Value in another column
2
Replies
10
Views
676
Fluff
Fluff
A
  • Question Question
Dynamically add new rows via VBA when using IFERROR TEXTJOIN and FILTER formulas from Table?
Replies
0
Views
508
AlfredDB
A
M
  • Question Question
Hi, Have a problem with Power Automate and decided to use Excel VBA
Replies
3
Views
412
ides315
I
D
  • Solved
Textjoin Calc error help
Replies
7
Views
4K
Fluff
Fluff
P
  • Question Question
Return a value from a table that is in the top n percent of a person's trial scores
2
Replies
13
Views
898
Fluff
Fluff

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top