Unique List from 2 Columns Using TextJoin and Unique if possible

[Stephen_IV]

I use TextJoin and Unique to get Unique data in 1 cell. What I am looking to do is is to get unique data from 2 columns into 1 cell. In D1: Steve, Mike, Jay, Mary, Ray, Al

This is the formula that I use for 1 cell: =TEXTJOIN(", ",,UNIQUE(A1:A5)). I would like to do something like, =TEXTJOIN(", ",,UNIQUE(A1:B5)) in 1 cell. Thanks in advance!!!!

Book1
	A	B
1	Steve	Ray
2	Steve	Mike
3	Mike	Mike
4	Jay	Steve
5	Mary	AL
Sheet2

 

[hernantorres23]

Hi, try this

Book1
	A	B	C
1	Steve	Ray
2	Steve	Mike
3	Mike	Mike
4	Jay	Steve
5	Mary	AL
6
Stephen_IV

Option Explicit

'by Hernan Torres (Mikel ERP)
'December 13, 2019
'Refer to Stephen_IV sheet
'topic: Unique List from 2 Columns Using TextJoin and Unique if possible

Sub extraer_unicos_desde_rango()
  Dim lastRow As Long, lastCol As Long
  Dim start_b As Range
  Dim b() As Variant
  Dim element, runAll, getKeys As Variant
 
  Range("A1").Activate   'you can adapt this range, like you want
  lastRow = Sheets("Stephen_IV").Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
  lastCol = Sheets("Stephen_IV").Cells(1, Columns.Count).End(xlToLeft).Column
  Set start_b = Range(Cells(ActiveCell.Column, ActiveCell.Row).Address)
  'Next: two ways for create our array, in this case, I take second option
  'b = Range(start_b, Cells(Rows.Count, start_b.Column).End(xlUp)) 'crea un array y lo guarda en la variable "b"
  b = Range(start_b, Cells(lastRow, lastCol))
  With CreateObject("Scripting.Dictionary")
    For Each element In b
    runAll = .Item(element)
    Next
    getKeys = .keys
    Cells(1, 4) = Join(.keys, ",")  'because you want it, in range D1
    MsgBox "Uniques values created", vbInformation, "Mikel ERP by htorres"
  End With
End Sub

 

[Aladin Akyurek]

[…]

This is the formula that I use for 1 cell: =TEXTJOIN(", ",,UNIQUE(A1:A5)). I would like to do something like, =TEXTJOIN(", ",,UNIQUE(A1:B5)) in 1 cell. Thanks in advance!!!!

[…]

What does UNIQUE(A1:B5) exactly return? Choose this bit on the formula bar, appy F9, copy what you see, and paste it here.

By the way, did you apply control+shift+enter to the formula involving A1:B5?

 

[Stephen_IV]

Hi Aladin,

This is what I get
{"Steve","Ray";"Steve","Mike";"Mike","Mike";"Jay","Steve";"Mary","AL"}

I did try cse. Did not work.

 

[hernantorres23]

Hi Aladin,

This is what I get
{"Steve","Ray";"Steve","Mike";"Mike","Mike";"Jay","Steve";"Mary","AL"}

I did try cse. Did not work.

Have you tried post 2?

 

[Bo_Ry]

Please try this

=TEXTJOIN(", ",,UNIQUE(INDEX(A1:B5,MOD(SEQUENCE(ROWS(A1:B5)*COLUMNS(A1:B5),,0),ROWS(A1:B5))+1,INT(SEQUENCE(ROWS(A1:B5)*COLUMNS(A1:B5),,0)/ROWS(A1:B5))+1)))

 

[Stephen_IV]

Bo_Ry, Thank you so much!!! That did it! That is the formula approach I was looking for. Thanks hernantorres23 yes I did and the code worked. Thank you so much for that.

 

[Aladin Akyurek]

Hi Aladin,

This is what I get
{"Steve","Ray";"Steve","Mike";"Mike","Mike";"Jay","Steve";"Mary","AL"}

I did try cse. Did not work.

A peculiar behavior I must say...

 

[Jon Peltier]

A peculiar behavior I must say...

Nope, just what you would expect. There is a second argument to UNIQUE(), if omitted or false, returns unique rows, and if true returns unique columns. Each row is distinct (each pair of cells is unique), so the entire input range is returned.

 

[Peter_SSs]

This may not scale up as well as Bo_Ry's formula, but does provide a simpler formula for a smallish range with shortish texts.

Book1
	A	B	C	D
1	Steve	Ray		Steve, Ray, Mike, Jay, Mary, AL
2	Steve	Mike
3	Mike	Mike
4	Jay	Steve
5	Mary	AL
Unique

Cell Formulas
Range	Formula
D1	D1	=TEXTJOIN(", ",,UNIQUE(TRIM(MID(TEXTJOIN(REPT(" ",100),1,A1:B5),SEQUENCE(,COUNTA(A1:B5),1,100),100)),1))