Creating a Vertical Header list

[Venom]

Hello again,

This is probably simple (I hope). But I'd like to create a list of Locations from a dynamic list on a separate sheet.

Sheet2!H2:H10000 has a list of Locations, with a LOT of repeats.
Sheet1!A2:A100 is where I'd like to list the locations, without repeats.

Sheet1!A2:A100 is also used for a summary (in column B) and as a Data Validation point (for a list in G1)

The table on Sheet2 can vary from 0 items to 10,000 items and the number of different possible Locations is about to expand as it's resulted from a query to an Access database with filters.

Any help would be appreciated. =)

 

[SteveO59L]

Use an Advanced Filter, with the "Unique Values" and "Copy to Another Location" options

 

[Venom]

That's telling me it can't do it to another sheet. =(

 

[SteveO59L]

Do it on the same sheet, then copy, or use a Pivot Table

 

[PCL]

Have a look in
Filter > Advanced Filter > Copy to Another Location > No Criteria > Unique Record Only

 

[Venom]

hmm, is there something that is automatic? The data changes every 5 minutes...

 

[SteveO59L]

Use a pivot table then

 

[PCL]

Here something collected somewhere in a forum you could adapt it to your need.

<TABLE style="WIDTH: 181pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=241 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 85pt; mso-width-source: userset; mso-width-alt: 4132" width=113><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffffcc" width=64 height=17>Israel</TD><TD class=xl30 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=64 x:fmla='= SUM(IF(FREQUENCY(IF(A1:A7<>"",MATCH("~"&A1:A7,A1:A7&"",0)),ROW(A1:A7)-ROW(A1)+1),1))' x:arrayrange="B1" x:num>5</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 85pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=113 x:fmla='=IF(ROWS(C$1:C1)<=B$1,INDEX(A$1:A$7,SMALL(IF(FREQUENCY(IF(A$1:A$7<>"",MATCH("~"&A$1:A$7,A$1:A$7&"",0)),ROW(A$1:A$7)-ROW(A$1)+1),ROW(A$1:A$7)-ROW(A$1)+1),ROWS(C$1:C1))),"")' x:arrayrange="C1">Israel</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Lebanon</TD><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:fmla='=IF(ROWS(C$1:C2)<=B$1,INDEX(A$1:A$7,SMALL(IF(FREQUENCY(IF(A$1:A$7<>"",MATCH("~"&A$1:A$7,A$1:A$7&"",0)),ROW(A$1:A$7)-ROW(A$1)+1),ROW(A$1:A$7)-ROW(A$1)+1),ROWS(C$1:C2))),"")' x:arrayrange="C2">Lebanon</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ffffcc" height=17>Israel</TD><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:fmla='=IF(ROWS(C$1:C3)<=B$1,INDEX(A$1:A$7,SMALL(IF(FREQUENCY(IF(A$1:A$7<>"",MATCH("~"&A$1:A$7,A$1:A$7&"",0)),ROW(A$1:A$7)-ROW(A$1)+1),ROW(A$1:A$7)-ROW(A$1)+1),ROWS(C$1:C3))),"")' x:arrayrange="C3">Jordan</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ccffff" height=17>Jordan</TD><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:fmla='=IF(ROWS(C$1:C4)<=B$1,INDEX(A$1:A$7,SMALL(IF(FREQUENCY(IF(A$1:A$7<>"",MATCH("~"&A$1:A$7,A$1:A$7&"",0)),ROW(A$1:A$7)-ROW(A$1)+1),ROW(A$1:A$7)-ROW(A$1)+1),ROWS(C$1:C4))),"")' x:arrayrange="C4">Syria</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Syria</TD><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:fmla='=IF(ROWS(C$1:C5)<=B$1,INDEX(A$1:A$7,SMALL(IF(FREQUENCY(IF(A$1:A$7<>"",MATCH("~"&A$1:A$7,A$1:A$7&"",0)),ROW(A$1:A$7)-ROW(A$1)+1),ROW(A$1:A$7)-ROW(A$1)+1),ROWS(C$1:C5))),"")' x:arrayrange="C5">Egypt</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Egypt</TD><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:str="" x:fmla='=IF(ROWS(C$1:C6)<=B$1,INDEX(A$1:A$7,SMALL(IF(FREQUENCY(IF(A$1:A$7<>"",MATCH("~"&A$1:A$7,A$1:A$7&"",0)),ROW(A$1:A$7)-ROW(A$1)+1),ROW(A$1:A$7)-ROW(A$1)+1),ROWS(C$1:C6))),"")' x:arrayrange="C6"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #ccffff" height=17>Jordan</TD><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:str="" x:fmla='=IF(ROWS(C$1:C7)<=B$1,INDEX(A$1:A$7,SMALL(IF(FREQUENCY(IF(A$1:A$7<>"",MATCH("~"&A$1:A$7,A$1:A$7&"",0)),ROW(A$1:A$7)-ROW(A$1)+1),ROW(A$1:A$7)-ROW(A$1)+1),ROWS(C$1:C7))),"")' x:arrayrange="C7"></TD></TR></TBODY></TABLE>

Data to filter : A1: A7
B1 = SUM(IF(FREQUENCY(IF(A1:A7<>"",MATCH("~"&A1:A7,A1:A7&"",0)),ROW(A1:A7)-ROW(A1)+1),1)) area formula confirm with CONTROL + SHIFT + ENTER
A1 = IF(ROWS(C$1:C1)<=B$1,INDEX(A$1:A$7,SMALL(IF(FREQUENCY(IF(A$1:A$7<>"",MATCH("~"&A$1:A$7,A$1:A$7&"",0)),ROW(A$1:A$7)-ROW(A$1)+1),ROW(A$1:A$7)-ROW(A$1)+1),ROWS(C$1:C1))),"") area formula confirm with CONTROL + SHIFT + ENTER
Copy A1 and drag down far down

 

[Venom]

Which brings me to another question... how does one make a Pivot Table refresh itself? I see a check box for "Refresh every __", but it's always greyed out.

 

[PCL]

In the TAB code where the Pivot Table is you could put next code which will be launched each time you access to this sheet and therefore update the PT.

Option Explicit
Private Sub Worksheet_Activate()
    Dim PT As PivotTable
        For Each PT In ActiveSheet.PivotTables
            PT.RefreshTable
        Next PT
End Sub