NEW Unique function - multiple arrays

[gcefaloni]

Hi!

Is it possible to use the new "unique" function on multiple non-contiguous arrays? If so, what would be the proper syntax to make it work?

As an example:
I'd like to do something like:
=UNIQUE({'Sheet1'!A1:A10,'Sheet2'!A1:A10}) where both Sheet 1 and Sheet 2 column As have the same type of data and have common data points. I want the unique ones to show up in a single array.

So, looking up unique values in non-contiguous arrays would be the objective. Is that doable?

 

[kweaver]

Probably VBA or a UDF is cleaner. I tried this (ugly) approach which appears to work. The formula in A2 is filled down to A16.

Book6
	A	B	C	D	E	F
1	Stack D-F	Unique D-F		Name	Name	Name
2	James	James		James	Jones	Jackson
3	Jones	Jones		Jones	Smithe	Smith
4	William	William		William	Jackson	Jones
5	Williamson	Williamson		Williamson	Johnson	Williamson
6	William	Smithe		William	Brown	Johnson
7	Jones	Jackson
8	Smithe	Johnson
9	Jackson	Brown
10	Johnson	Smith
11	Brown
12	Jackson
13	Smith
14	Jones
15	Williamson
16	Johnson
Sheet1

Cell Formulas
Range	Formula
B2:B10	B2	=UNIQUE(A2:A16)
A2	A2	=IFERROR(INDEX($D$2:$D$6,ROWS(A2:$A$2)),IFERROR(INDEX($E$2:$E$6,ROWS(A2:$A$2)-ROWS($D$2:$D$6)),IFERROR(INDEX($F$2:$F$6,ROWS(A2:$A$2)-ROWS($D$2:$D$6)-ROWS($E$2:$E$6)),"")))
A3:A16	A3	=IFERROR(INDEX($D$2:$D$6,ROWS(A$2:$A3)),IFERROR(INDEX($E$2:$E$6,ROWS(A$2:$A3)-ROWS($D$2:$D$6)),IFERROR(INDEX($F$2:$F$6,ROWS(A$2:$A3)-ROWS($D$2:$D$6)-ROWS($E$2:$E$6)),"")))
Dynamic array formulas.

 

[gcefaloni]

Probably VBA or a UDF is cleaner. I tried this (ugly) approach which appears to work. The formula in A2 is filled down to A16.

Book6
	A	B	C	D	E	F
1	Stack D-F	Unique D-F		Name	Name	Name
2	James	James		James	Jones	Jackson
3	Jones	Jones		Jones	Smithe	Smith
4	William	William		William	Jackson	Jones
5	Williamson	Williamson		Williamson	Johnson	Williamson
6	William	Smithe		William	Brown	Johnson
7	Jones	Jackson
8	Smithe	Johnson
9	Jackson	Brown
10	Johnson	Smith
11	Brown
12	Jackson
13	Smith
14	Jones
15	Williamson
16	Johnson
Sheet1

Cell Formulas
Range	Formula
B2:B10	B2	=UNIQUE(A2:A16)
A2	A2	=IFERROR(INDEX($D$2:$D$6,ROWS(A2:$A$2)),IFERROR(INDEX($E$2:$E$6,ROWS(A2:$A$2)-ROWS($D$2:$D$6)),IFERROR(INDEX($F$2:$F$6,ROWS(A2:$A$2)-ROWS($D$2:$D$6)-ROWS($E$2:$E$6)),"")))
A3:A16	A3	=IFERROR(INDEX($D$2:$D$6,ROWS(A$2:$A3)),IFERROR(INDEX($E$2:$E$6,ROWS(A$2:$A3)-ROWS($D$2:$D$6)),IFERROR(INDEX($F$2:$F$6,ROWS(A$2:$A3)-ROWS($D$2:$D$6)-ROWS($E$2:$E$6)),"")))
Dynamic array formulas.

Would there be a way to nest the entire thing together? My solution right now, which is somewhat similar to yours I think, was to put in cell A2: =Unique("array1"), and put in cell A51: =Unique("array2") and then in cell B2: =Unique("A2:A100"). But like you said, it is quite ugly and I was hoping for a single formula rather than relying on a helper column.

 

[kweaver]

Maybe someone has another solution...possibly Microsoft will come up with UNIQUES sometime in the future.

 

[Peter_SSs]

Is this the same question as : Before/After using UNIQUE function on 2 arrays

I was thinking of using the UNIQUE function in order to do that but the issues I'm having are 2 fold:
1 - I do not know how to use the unique function on 2 separate arrays

 

[gcefaloni]

Is this the same question as : Before/After using UNIQUE function on 2 arrays

No. Not exactly. They are part of the same overall problem I'm trying to solve but they are different components. (1) is about finding the unique identifiers using formulas (I was thinking of using the Unique function) and the other question (2) is about merging results based on those merged identifiers. The second question I suppose could be solved with a simple sumifs. I'm not sure if there's a better way of doing it using arrays but the sumifs might work just fine. I'm still having issues with trying to solve (1) and use formulas to find unique "identifiers/tickers" without helper columns.

 

[Peter_SSs]

No. Not exactly.

Thanks for the clarification.


If you want to use standard worksheet formulas then I think you will have to put up with helper cells. (happy to be proved wrong though)

If you do not want helper cells then you could employ a user-defined function like this:

Function UniqueMult(ParamArray ranges() As Variant) As Variant
  Dim d As Object
  Dim rng As Variant
  Dim c As Range
 
  Set d = CreateObject("Scripting.Dictionary")
  For Each rng In ranges
    For Each c In rng
      If Len(c.Value) > 0 Then d(c.Value) = 1
    Next c
  Next rng
  UniqueMult = Application.Transpose(d.Keys)
End Function

The function needs only be entered in a single cell and the results should still 'spill' as required.
Sheet1

gcefaloni 2020-06-04 1.xlsm
	A
1	a
2	b
3	c
4	b
5	c
6	a
7	c
8	a
9	a
10	c
Sheet1

Sheet2

gcefaloni 2020-06-04 1.xlsm
	A
1	a
2	t
3	r
4	t
5	r
6	h
7	t
8	a
9	t
10	r
Sheet2

Sheet3

gcefaloni 2020-06-04 1.xlsm
	A
1	a
2	b
3	c
4	t
5	r
6	h
7
8
9
10
Sheet3

Cell Formulas
Range	Formula
A1:A6	A1	=UniqueMult(Sheet1!A1:A10,Sheet2!A1:A10)
Dynamic array formulas.

 

[JonsLab]

Hi!

Is it possible to use the new "unique" function on multiple non-contiguous arrays? If so, what would be the proper syntax to make it work?

As an example:
I'd like to do something like:
=UNIQUE({'Sheet1'!A1:A10,'Sheet2'!A1:A10}) where both Sheet 1 and Sheet 2 column As have the same type of data and have common data points. I want the unique ones to show up in a single array.

So, looking up unique values in non-contiguous arrays would be the objective. Is that doable?

Yes, you can easily do that by combining your arrays using VSTACK
=UNIQUE(VSTACK('Sheet1'!A1:A10,'Sheet2'!A1:A10))

 

[Fluff]

Yes, you can easily do that by combining your arrays using VSTACK

Except that VSTACK did not exist when this thread was started.