2 TEXTJOIN problems - remove duplicate/only unique output, and remove '0' from conditional IF results

[Seb_C]

I have a few tables of data and I am trying to pull some information in Column F, based on the contents of Column A, into a cell on a different tab with TEXTJOIN. I need it in 2 different formats for different things. I made a sharable sheet with no company info and reduced the dataset for trying to solve my issues and try out different functions. That is what you see below.

Problem 1 – removing duplicates. I can remove duplicates from the whole column, but I only want to remove duplicates from the range containing the matched name in Column A, not the whole column. My base equation is in K3:K5, while my attempt to remove duplicates is in J3:J5, and the desired result is in J10:J12. I’m guessing I need to change the equation inside IFNA so it is only referring to the rows for a given name with something like calling column A from ROW(First instance of name XX):ROW(last instance of name XX), but I am having trouble trying to figure out how to get there. Column A is sorted, so each name will be a contiguous selection, just of changing lengths

Problem 2 – For this one I want to display all the results, but I want the ‘0’ to be removed and replaced with a line break/carriage return (I am using CHAR(10)). I want to do this without adding another column, but even adding SUBSTITUTE in another column, it replaces all of the 0s in output, while I only want it to remove the standalone 0s that are created when the IF statement inside TEXTJOIN encounters a blank cell. ie 600755* currently becomes [6 CHAR(10) CHAR(10) 755*] instead of remaining as 600755*

Cells J23:K25 show my current output on the left, and the desired output on the right for this issue.


For both of these problems I would prefer to be able to do them within the cell, but if I need to go with a custom function or macro to make it happen I can. Since I am in 2019 I can’t use UNIQUE, which seems to be able to solve at least Problem 1 based on some other posts on this forum.

Book87.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	ID	Other	Stuff	Goes	Here	Ref Doc					TEXTJOIN(", ",TRUE,IF('Sheet1 (2)'!$A:A=I3,IF('Sheet1 (2)'!F:F>0,'Sheet1 (2)'!F:F,""),""))
2	Sally									Current output - only the first isntance of a name
3	Sally					600755 Doc 1			Sally	600755 Doc 1, Doc 3, 600800 Doc 2, 103377-3	600755 Doc 1, Doc 3, 600800 Doc 2, 103377-3, Doc 3, Doc 3
4	Sally					Doc 3			Robert	Test 2, 60002 Test Report, BACON!!	Doc 3, Doc 3, Test 2, 60002 Test Report, 103377-3, 600800 Doc 2, BACON!!
5	Sally					600800 Doc 2			Adam	Dash 45	Test 2, Dash 45
6	Sally					103377-3
7	Sally					Doc 3
8	Sally					Doc 3
9	Sally									Desired results
10	Robert					Doc 3				600755 Doc 1, Doc 3, 600800 Doc 2, 103377-3
11	Robert					Doc 3				Doc 3, Test 2, 60002 Test Report, 103377-3, 600800 Doc 2, BACON!!
12	Robert					Test 2				Test 2, Dash 45
13	Robert					60002 Test Report
14	Robert					103377-3
15	Robert
16	Robert					600800 Doc 2
17	Robert					BACON!!
18	Adam
19	Adam					Test 2
20	Adam
21	Adam					Dash 45				Replace all '0' with CHAR(10)
22											Desired result
23									Sally	0 600755 Doc 1 Doc 3 600800 Doc 2 103377-3 Doc 3 Doc 3 0	600755 Doc 1 Doc 3 600800 Doc 2 103377-3 Doc 3 Doc 3
24									Robert	Doc 3 Doc 3 Test 2 60002 Test Report 103377-3 0 600800 Doc 2 BACON!!	Doc 3 Doc 3 60002 Test Report 103377-3 600800 Doc 2 BACON!!
25									Adam	0 Test 2 0 Dash 45	Test 2 Dash 45
Sheet1 (2)

Cell Formulas
Range	Formula
J3:J5	J3	=TEXTJOIN(", ",TRUE,IF('Sheet1 (2)'!$A:A=I3,IF('Sheet1 (2)'!F:F>0,IFNA(IF(MATCH(F:F,F:F,0)=MATCH(ROW(F:F),ROW(F:F)),F:F,""),""),""),""))
K3:K5	K3	=TEXTJOIN(", ",TRUE,IF('Sheet1 (2)'!$A:A=I3,IF('Sheet1 (2)'!F:F>0,'Sheet1 (2)'!F:F,""),""))
J23:J25	J23	=TEXTJOIN(CHAR(10),TRUE,IF('Sheet1 (2)'!$A:A=I3,'Sheet1 (2)'!F:F,""))
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
'Sheet1 (2)'!_FilterDatabase	='Sheet1 (2)'!$A$1:$F$64	J23:J25, J3:K5

 

[jorismoerings]

Hi,

Try this and see if this helps. Be aware, working with full column ranges increases the calculation time on the first formula dramatically.
I've amended the full column refs to the range.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	ID	Other	Stuff	Goes	Here	Ref Doc
2	Sally
3	Sally					600755 Doc 1			Sally	600755 Doc 1, Doc 3, 600800 Doc 2, 103377-3	600755 Doc 1 Doc 3 600800 Doc 2 103377-3 Doc 3 Doc 3
4	Sally					Doc 3			Robert	Doc 3, Test 2, 60002 Test Report, 103377-3, 600800 Doc 2, BACON!!	Doc 3 Doc 3 Test 2 60002 Test Report 103377-3 600800 Doc 2 BACON!!
5	Sally					600800 Doc 2			Adam	Test 2, Dash 45	Test 2 Dash 45
Sheet1 (2)

Cell Formulas
Range	Formula
J3:J5	J3	=TEXTJOIN(", ",TRUE,IFNA(IF(MATCH(ROW($F$2:$F$21),(ROW($F$2:$F$21)*($A$2:$A$21=I3)),0)=MATCH(IF(($A$2:$A$21=I3)*($F$2:$F$21>0),$F$2:$F$21,NA()),IF(($A$2:$A$21=I3)*($F$2:$F$21>0),$F$2:$F$21,NA()),0),$F$2:$F$21,""),""))
K3:K5	K3	=TEXTJOIN(CHAR(10),TRUE,IF('Sheet1 (2)'!$A2:A21=I3,IF('Sheet1 (2)'!F2:F21=""," ",'Sheet1 (2)'!F2:F21),""))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Seb_C]

Jorismoerings you rock!!
That is perfect. Thank you. I need to go through it in detail still to make sure I understand it, but it works perfectly when I transfer it over to my actual sheet.

Thank you for the tip on full columns. I did that because the actual data is of variable and unknown length, but it should be <1000 rows so I set the column refs to go 2:1000, and just need to note that in the documentation.

 

[Ray_Dem]

Thanks a bunch this is exactly what i needed