Help with TextJoin and obtain Unique values (remove duplicates)

[Dani_LobP]

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!

 

[Fluff]

Glad you sorted it & thanks for the feedback.

And does the Vlookup formula make sense to you?

That looks fine, although you haven't posted the 2nd & 3rd argument of the IF

 

[Dani_LobP]

=IF(VLOOKUP([@[DITL participant]],GrayPlus[[Legal Name]:[Course ID in BlueTable?]],7,0)="Yes",TEXTJOIN(", ",TRUE,UNIQUE(FILTER(GrayPlus[Role name in training],GrayPlus[Legal Name (Raw)]=[@[DITL participant]],""))),"")

For some reason i am getting same results as if had no vlookup in formula.

Legal Name	Role name in training	Course ID	Course ID in BlueTable?	Scenario ID
Ilse	RTR - General Ledger Accountant	FIN-C-31	Yes	9e, 71c, 11b, 113c, 10d
Ilse	RTR - General Ledger Accountant	FIN-C-45	No
Ilse	RTR - General Ledger Accountant	FIN-C-30	No
Ilse	RTR - General Ledger Accountant	Fin-C-25	No
Ilse	RTR - General Ledger Accountant	FIN-C-34	No
Ilse	RTR - General Ledger Accountant	SC-IMIC-C-04	No
Ilse	STP - AP Display	PRO-C-08	No
Ilse	STP - AP Display	Proc-C-01	No

with above example data, i should have, normal formula displaying 2 roles, few courses, and the scenario ID...
and in a column with Vlookup to show only Yes, i should get only RTR - General Ledger Accountant, Fin c 31 as results.. right? but i get same results as if it ignores the condition of only show if Yes.

Any idea what can be the reason?

 

[Fluff]

Is the "Course ID in BlueTable?" column, the 7th column in your table.?

 

[Dani_LobP]

Is the "Course ID in BlueTable?" column, the 7th column in your table.?

Yes, its 7th.

Legal Name

Role name in training

Course ID

Role name in BlueTable (Role1)?

Role name in BlueTable (Role2)?

Role name in BlueTable (Role3)?

Course ID in BlueTable?

=IF(VLOOKUP([@[DITL participant]],GrayPlus[[Legal Name]:[Course ID in BlueTable?]],7,0)="Yes",TEXTJOIN(", ",TRUE,UNIQUE(FILTER(GrayPlus[Role name in training],GrayPlus[Legal Name (Raw)]=[@[DITL participant]],""))),"")

The Course ID In BlueTable column thou is not Yes/No situation, its a formula there. So could it be the reason it doesn't detect a Yes?

=IFNA(IF(ISNA(VLOOKUP([@[Course ID]],Blue[Training Asset Code],1,0)), "No", "Yes"),"")

Above is the formula in that column... from what i know, it should be taking the Yes... right?

 

[Fluff]

The fact it's a formula should not make any difference.
Use the Evaluate Formula button on the formula tab, to step through the calculation sequence to see where it's going wrong.

 

[Dani_LobP]

The fact it's a formula should not make any difference.
Use the Evaluate Formula button on the formula tab, to step through the calculation sequence to see where it's going wrong.

All good now thanks!
issue with that Vlookup i try make is that there are many rows for the same person, some are No and others Yes... so Vlookup checks first row, so if its No, keeps walking.... and i guess it wont be possible to achieve what i wanted.. unless formula is more complex or without using VBA.

So if a person has 20 rows, and just 2 rows have in column a Yes. then what i wanted is have the item from another column to be added... but as i mentioned, sounds too ambitious :D