Formula to extract unique values as a horizontal list

[willow1985]

Hello,

I have tried a few formulas (index match with count if) But I cannot seem to get the results I am looking for.

I have a list of areas that I would like unique values transposed horizontally (see below picture)

Any help would be appreciated ( I think I am missing something in my formula attempts)

Thank you!

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	County		Derbyshire	West Yorkshire	Staffordshire	Greater Manchester	Somerset	Cumbria	Shropshire	County Durham	Lancashire	Northumberland	South Yorkshire
2	Derbyshire
3	West Yorkshire
4	Derbyshire
5	Derbyshire
6	West Yorkshire
7	Derbyshire
8	Staffordshire
9	Derbyshire
10	West Yorkshire
11	Greater Manchester
12	West Yorkshire
13	West Yorkshire
14	West Yorkshire
15	West Yorkshire
16	Staffordshire
17	Somerset
18	Cumbria
19	Shropshire
20	West Yorkshire
21	County Durham
22	Staffordshire
23	West Yorkshire
24	Lancashire
25	Northumberland
26	Lancashire
27	South Yorkshire
28	Greater Manchester
29	Derbyshire
30	West Yorkshire
31	County Durham
32	West Yorkshire
33	Lancashire
34	West Yorkshire
Main

Cell Formulas
Range	Formula
C1:N1	C1	=IFERROR(INDEX($A$2:$A$34,AGGREGATE(15,6,(ROW($A$2:$A$34)-ROW($A$2)+1)/(ISNA(MATCH($A$2:$A$34,$B1:B1,0))),1)),"")

 

[willow1985]

How about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	County		Derbyshire	West Yorkshire	Staffordshire	Greater Manchester	Somerset	Cumbria	Shropshire	County Durham	Lancashire	Northumberland	South Yorkshire
2	Derbyshire
3	West Yorkshire
4	Derbyshire
5	Derbyshire
6	West Yorkshire
7	Derbyshire
8	Staffordshire
9	Derbyshire
10	West Yorkshire
11	Greater Manchester
12	West Yorkshire
13	West Yorkshire
14	West Yorkshire
15	West Yorkshire
16	Staffordshire
17	Somerset
18	Cumbria
19	Shropshire
20	West Yorkshire
21	County Durham
22	Staffordshire
23	West Yorkshire
24	Lancashire
25	Northumberland
26	Lancashire
27	South Yorkshire
28	Greater Manchester
29	Derbyshire
30	West Yorkshire
31	County Durham
32	West Yorkshire
33	Lancashire
34	West Yorkshire
Main

Cell Formulas
Range	Formula
C1:N1	C1	=IFERROR(INDEX($A$2:$A$34,AGGREGATE(15,6,(ROW($A$2:$A$34)-ROW($A$2)+1)/(ISNA(MATCH($A$2:$A$34,$B1:B1,0))),1)),"")

I see the position of this formula matters quite a bit. What if I wanted the horizontal list on another sheet, say sheet 2, starting at A1,

How would that work?

Also is there a way to get rid of the "0" result when it hits the end? rather just have blanks...

Thank you so much!

 

[Fluff]

You could not have it in A1 as there has to be at least one cell to the left of the formula.

 

[Dossfm0q]

Hi
is it like this by changing index ranges
Feedback is highly Appreciated
on other Sheet

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
1	Derbyshire	West Yorkshire	Derbyshire	Derbyshire	West Yorkshire	Derbyshire	Staffordshire	Derbyshire	West Yorkshire	Greater Manchester	West Yorkshire	West Yorkshire	West Yorkshire	West Yorkshire	Staffordshire	Somerset	Cumbria	Shropshire	West Yorkshire	County Durham	Staffordshire	West Yorkshire	Lancashire	Northumberland	Lancashire	South Yorkshire	Greater Manchester	Derbyshire	West Yorkshire	County Durham	West Yorkshire	Lancashire	West Yorkshire
Sheet2

Cell Formulas
Range	Formula
A1:AJ1	A1	=IFERROR(INDEX(Sheet1!$A$2:$A$34,AGGREGATE(15,6,(ROW(Sheet1!$A$2:$A$34)-ROW($A$2)+1),COLUMNS($A2:A2))),"")

On the Same Sheet

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1	Derbyshire	West Yorkshire	Derbyshire	Derbyshire	West Yorkshire	Derbyshire	Staffordshire	Derbyshire	West Yorkshire	Greater Manchester	West Yorkshire	West Yorkshire	West Yorkshire	West Yorkshire	Staffordshire	Somerset	Cumbria	Shropshire	West Yorkshire	County Durham	Staffordshire	West Yorkshire	Lancashire	Northumberland	Lancashire	South Yorkshire	Greater Manchester	Derbyshire	West Yorkshire	County Durham	West Yorkshire	Lancashire	West Yorkshire
2	Derbyshire
3	West Yorkshire
4	Derbyshire
5	Derbyshire
6	West Yorkshire
7	Derbyshire
8	Staffordshire
9	Derbyshire
10	West Yorkshire
11	Greater Manchester
12	West Yorkshire
13	West Yorkshire
14	West Yorkshire
15	West Yorkshire
16	Staffordshire
17	Somerset
18	Cumbria
19	Shropshire
20	West Yorkshire
21	County Durham
22	Staffordshire
23	West Yorkshire
24	Lancashire
25	Northumberland
26	Lancashire
27	South Yorkshire
28	Greater Manchester
29	Derbyshire
30	West Yorkshire
31	County Durham
32	West Yorkshire
33	Lancashire
34	West Yorkshire
Sheet1

Cell Formulas
Range	Formula
A1:AH1	A1	=IFERROR(INDEX($A$2:$A$34,AGGREGATE(15,6,(ROW($A$2:$A$34)-ROW($A$2)+1),COLUMNS($A2:A2))),"")

 

[AhoyNC]

Here is an array formula that should work.
Must be entered with CTRL-SHIFT-ENTER. then drag across columns.

Sheet1

Book1
	A
1	Area
2	Plant Floor
3	Zinc Oven
4	M Saw
5	Saw
6	Barrel Line
7	Plant Floor
8	Plant Floor
9	Plant Floor
10	Parking Lot
11	Parking Lot
12	Maint Tent
13	Parking Lot
14	Office
15	Cell3
16	Barrel Line
17	Industrial
Sheet1

Sheet2

Book1
	A	B	C	D	E	F	G	H	I	J
1	Plant Floor	Zinc Oven	M Saw	Saw	Barrel Line	Parking Lot	Maint Tent	Office	Cell3	Industrial
Sheet2

Cell Formulas
Range	Formula
A1:J1	A1	{=IFERROR(INDEX(Sheet1!$A$2:$A$17,SMALL(IF(FREQUENCY(MATCH(Sheet1!$A$2:$A$17,Sheet1!$A$2:$A$17,0),ROW(Sheet1!$A$2:$A$17)-ROW(Sheet1!$A$2)+1),ROW(Sheet1!$A$2:$A$17)-ROW(Sheet1!$A$2)+1),COLUMNS($A$1:A1))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Dossfm0q]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Area	Plant Floor	Zinc Oven	M Saw	Saw	Barrel Line	Parking Lot	Maint Tent	Office	Cell3	Industrial
Sheet4

Cell Formulas
Range	Formula
A1:L1	A1	=IFERROR(INDEX(Sheet1!$A$1:$A$17,SMALL(IF(FREQUENCY(MATCH(Sheet1!$A$1:$A$17,Sheet1!$A$1:$A$17,0),ROW(Sheet1!$A$1:$A$17)-ROW(Sheet1!$A$1)+1),ROW(Sheet1!$A$1:$A$17)-ROW(Sheet1!$A$1)+1),COLUMNS($A$1:A1))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Peter_SSs]

You could not have it in A1 as there has to be at least one cell to the left of the formula.

Could just use a stand-alone formula for the first result cell.

willow1985 1.xlsm
	A
1	Area
2	Plant Floor
3	Zinc Oven
4	M Saw
5	Saw
6	Barrel Line
7	Plant Floor
8	Plant Floor
9	Plant Floor
10	Parking Lot
11	Parking Lot
12	Maint Tent
13	Parking Lot
14	Office
15	Cell3
16	Barrel Line
17	Industrial
Sheet1

willow1985 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Plant Floor	Zinc Oven	M Saw	Saw	Barrel Line	Parking Lot	Maint Tent	Office	Cell3	Industrial
Sheet2

Cell Formulas
Range	Formula
A1	A1	=Sheet1!A2
B1:L1	B1	=IFERROR(INDEX(Sheet1!$A:$A,AGGREGATE(15,6,ROW(Sheet1!$A2:$A17)/ISNA(MATCH(Sheet1!$A2:$A17,$A1:A1,0)),1)),"")

If there could be blanks in the original list or you want to allow for a larger (unknown) range, the B1 formula above could be something like

=IFERROR(INDEX(Sheet1!$A:$A,AGGREGATE(15,6,ROW(Sheet1!$A2:$A100)/(ISNA(MATCH(Sheet1!$A2:$A100,$A1:A1,0))*(Sheet1!$A2:$A100<>"")),1)),"")

 

[mohadin]

Hi
Why not

Sub test()
    Dim a As Variant, lr, i, x, s, k, itm
    a = Sheets("sheet1").Range("a2:a" & Cells(Rows.Count, 1).End(xlUp).Row)
    With CreateObject("scripting.dictionary")
        For i = 1 To UBound(a)
            If a(i, 1) <> "" Then
                If Not .exists(a(i, 1)) Then
                    .Add a(i, 1), 0
                End If: End If
        Next
      Sheets("sheet2").Cells(1, 1).Resize(, .Count) = .keys
    End With
End Sub

 

[willow1985]

Hi
Why not

Sub test()
    Dim a As Variant, lr, i, x, s, k, itm
    a = Sheets("sheet1").Range("a2:a" & Cells(Rows.Count, 1).End(xlUp).Row)
    With CreateObject("scripting.dictionary")
        For i = 1 To UBound(a)
            If a(i, 1) <> "" Then
                If Not .exists(a(i, 1)) Then
                    .Add a(i, 1), 0
                End If: End If
        Next
      Sheets("sheet2").Cells(1, 1).Resize(, .Count) = .keys
    End With
End Sub

I have to say I do like the idea of using VBA but I could not get the above code to work for me right

Sub test()
    Dim a As Variant, lr, i, x, s, k, itm
    a = Sheets("Log").Range("h2:h" & Cells(Rows.Count, 1).End(xlUp).Row)
    With CreateObject("scripting.dictionary")
        For i = 1 To UBound(a)
            If a(i, 1) <> "" Then
                If Not .exists(a(i, 1)) Then
                    .Add a(i, 1), 0
                End If: End If
        Next
      Sheets("Score Card").Cells(4, 3).Resize(, .Count) = .keys
    End With
End Sub

One sheet will have the data (Log) and the other is actually a score card (which is why I want the headers to score each area)

Would it also be possible for the VBA code to account for the year in cell A1 on sheet "Score Card"?

See the example below:

Safety Stats test.xlsx
	A	B	C	D	E	F	G	H
1	First Name	Last Name	Year	Date	Building	Division	Reportable/ non-reportable	Area
2			2020	2020-09-24			Non-Reportable	Balancer
3			2020	2020-08-03			Reportable	Plant Floor
4			2020	2020-07-31			Non-Reportable	Plant Floor
5			2020	2020-06-30			Reportable	Ovens
6			2020	2020-06-22			Reportable	Shop
7			2020	2020-05-08			Reportable	Saw
8			2020	2020-04-24			Reportable	Barrel Line
9			2020	2020-04-08			Reportable	Plant Floor
10			2020	2020-04-08			Non-Reportable	Plant Floor
11			2020	2020-04-03			Reportable	Parking Lot
12			2020	2020-03-26			Reportable	Maint Tent
13			2020	2020-03-26			Reportable	Parking Lot
14			2020	2020-03-09			Reportable	Parking Lot
15			2020	2020-03-04			Reportable	Office
16			2020	2020-02-24			Non-Reportable	Line
17			2019	2019-06-11			Non-Reportable	Industrial
18			2018	2018-01-22			Reportable	Still Line
19			2018	2018-01-16			Non-Reportable	Still Line
20			2018	2018-03-22			Non-Reportable	Shipping
Log

Cell Formulas
Range	Formula
C2:C20	C2	=YEAR(D2)

Safety Stats test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	2020
2	Key Performance Indicator	Operations		Areas:
3		Target	Actual	Balancer	Plant Floor	Ovens	Shop	Saw	Barrel Line	Parking Lot	Maint Tent	Office	Line
4	Safety (Employees)
5	Reportable	0	11	0	2	1	1	1	1	3	1	1	0
6	Non-Reportable	N/A	4	1	2	0	0	0	0	0	0	0	1
Score Card

Cell Formulas
Range	Formula
C5:C6	C5	=SUM(D5:AA5)
D5:M5	D5	=COUNTIFS(Log!$H:$H,'Score Card'!D3,Log!$C:$C,'Score Card'!$A$1,Log!$G:$G,'Score Card'!$A$5)
D6:M6	D6	=COUNTIFS(Log!$H:$H,'Score Card'!D3,Log!$C:$C,'Score Card'!$A$1,Log!$G:$G,'Score Card'!$A$6)