Extract Unique values except blank cells

D

dbarbella

New Member
Joined
Jun 6, 2017
Messages
32
Hi all,
I've seen the topic in a variety of posts, but for some reason, I am having challenges getting a working model.

The idea is that the master list of values (which has duplicates), will be updated often via a copy and paste of an updated column.
The number of rows can vary, so in the unique value list, I need to scan a large number of rows (which will have a number of blank values as I'll always scan more then I should technically need).

I also need to make sure that I can use a Data Filter on the heading of unique values to sort and filter

[TABLE="width: 800"]
<tbody>[TR]
[TD]A2[/TD]
[TD]B2[/TD]
[TD]C2[/TD]
[/TR]
[TR]
[TD]Stations
<Data filter>[/TD]
[TD]Unique Station Values
<Data filter>[/TD]
[TD]Number of Station Listings
<sumif from A2:a based on value of B2><Data filter>[/TD]
[/TR]
[TR]
[TD]Station 1[/TD]
[TD]Station 1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Station 2[/TD]
[TD]Station 2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Station 3[/TD]
[TD]Station 3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Station 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Station 3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Station 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Station 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Station 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I found a variety of approaches but for one reason or another when I try to transcribe the example to my sheet, something breaks. In one instance all worked except the data filter could sort (very odd).

If someone can share a suggested formula to extract the unique values from a2:a99999, and list then in column B (ignoring blanks from column a, and able to sort via the data filter), I would be exceptionally grateful.

Thanks very much
-Dave
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
In B2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$2:$A$9,SMALL(IF(FREQUENCY(IF(1-($A$2:$A$9=""),MATCH($A$2:$A$9,$A$2:$A$9,0)),ROW($A$2:$A$9)-ROW($A$2)+1),ROW($A$2:$A$9)-ROW($A$2)+1),ROWS($B$2:B2))),"")

In C2 just enter and copy down:

=IF(B2="","",COUNTIFS($A$2:$A$9,$B2))
 
Upvote 0
try PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Stations[/td][td][/td][td=bgcolor:#70AD47]Stations[/td][td=bgcolor:#70AD47]Count[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Station 1[/td][td][/td][td=bgcolor:#E2EFDA]Station 1[/td][td=bgcolor:#E2EFDA]
3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Station 2[/td][td][/td][td]Station 2[/td][td]
3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Station 3[/td][td][/td][td=bgcolor:#E2EFDA]Station 3[/td][td=bgcolor:#E2EFDA]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Station 2[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Station 3[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Station 1[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Station 2[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Station 1[/td][td][/td][td][/td][td][/td][/tr]
[/table]

Code: 
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Stations", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Stations] <> null)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Stations"}, {{"Count", each Table.RowCount(_), type number}})
in
    #"Grouped Rows"[/SIZE]
 
Upvote 0
Hi All,
Thank you for the great help.
I've applied Aladin's formula and it works great (thanks very much).

There is one oddity.

The column showing the unique results, I cannot sort.
I can apply a filter to show only particular values... but if I try to sort the column A-Z or Z-A, it does not work.
I made sure the formating is set to text.

Any insight as to why it will not sort?
I can upload an example of the excel file, if there is an appropriate place to do so

thanks very much
 
Upvote 0
sandy666 said:
try PowerQuery

[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Stations[/COLOR][/TD]
[TD][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Stations[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Count[/COLOR][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]Station 1[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]Station 1[/TD]
[TD="bgcolor: #E2EFDA"]
3​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]Station 2[/TD]
[TD][/TD]
[TD]Station 2[/TD]
[TD]
3​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]Station 3[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]Station 3[/TD]
[TD="bgcolor: #E2EFDA"]
2​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]Station 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]Station 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]Station 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]Station 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]Station 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Code: 
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Stations", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Stations] <> null)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Stations"}, {{"Count", each Table.RowCount(_), type number}})
in
    #"Grouped Rows"[/SIZE]
Click to expand...

I agree with Sandy, PowerQuery is a great option. You can also get the same output by using a Pivot Table. You can easily sort the results, and I would imagine that your workbook would run a lot more smoothly than using a crazy, albeit brilliant, formula.
<table valign="top" border="1"><caption>LEGO HTML</caption><col width="54"><col width="124"><col width="124"><col width="145"><col width="162">
<tr><td></td><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">A</font></th><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">B</font></th><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">C</font></th><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">D</font></th></tr>
<tr><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">1</font></th><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">Stations</font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">Row Labels</font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">Count of Stations</font></td></tr>
<tr><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">2</font></th><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">Station 1</font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">Station 1</font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">3</font></td></tr>
<tr><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">3</font></th><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">Station 2</font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">Station 2</font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">3</font></td></tr>
<tr><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">4</font></th><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">Station 3</font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">Station 3</font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">2</font></td></tr>
<tr><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">5</font></th><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">Station 2</font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">Grand Total</font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">8</font></td></tr>
<tr><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">6</font></th><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">Station 3</font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)"></font></td></tr>
<tr><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">7</font></th><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">Station 1</font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)"></font></td></tr>
<tr><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">8</font></th><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">Station 2</font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)"></font></td></tr>
<tr><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">9</font></th><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">Station 1</font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)"></font></td></tr></table>
 
Upvote 0
dbarbella said:
Hi All,
Thank you for the great help.
I've applied Aladin's formula and it works great (thanks very much).
Click to expand...

You are welcome.

There is one oddity.

The column showing the unique results, I cannot sort.
I can apply a filter to show only particular values... but if I try to sort the column A-Z or Z-A, it does not work.
I made sure the formating is set to text.

Any insight as to why it will not sort?
I can upload an example of the excel file, if there is an appropriate place to do so

thanks very much
Click to expand...

Try to sort the source data the way you want. You can even record a macro for this.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Filter and Unique missing data
Replies
5
Views
103
etaf
etaf
J
  • Solved
Unique, Filter and Sort Using a Range defined by two cells
Replies
8
Views
217
Peter_SSs
Peter_SSs
F
  • Solved
=SORT(UNIQUE(FILTER
Replies
4
Views
930
FluffySlipper
F
M
  • Solved
Unique() filter to exclude blanks and multiple strings
Replies
2
Views
531
metcala
M
M
  • Solved
Sort, Unique, Filter and Large Functions
Replies
3
Views
253
Fluff
Fluff

Forum statistics

Threads
1,223,907
Messages
6,175,301
Members
452,633
Latest member
DougMo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top