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

A2B2C2
Stations
<Data filter>		Unique Station Values
<Data filter>		Number of Station Listings
<sumif from A2:a based on value of B2><Data filter>
Station 1Station 13
Station 2Station 23
Station 3Station 32
Station 2
Station 3
Station 1
Station 2
Station 1

<tbody>
</tbody>


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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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

StationsStationsCount
Station 1Station 1
3​
Station 2Station 2
3​
Station 3Station 3
2​
Station 2
Station 3
Station 1
Station 2
Station 1

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

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Stations[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Stations[/COLOR][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Count[/COLOR]
Station 1Station 1
3​
Station 2Station 2
3​
Station 3Station 3
2​
Station 2
Station 3
Station 1
Station 2
Station 1

<tbody>
</tbody>


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

SanjayGMusafir
  • Solved
Filter Unique Values out of a Multi-Column List
Replies
7
Views
367
Fluff
Fluff
U
  • Question
Filter First Unique value
2 3 4
Replies
32
Views
1K
Uzma Shaheen
U
C
  • Question
Issues using FILTER formula
Replies
1
Views
498
Alex Blakenburg
A
A
  • Question
Get a running total of only certain cells within a category
Replies
3
Views
253
kevin9999
K
H
  • Question
How to filter for a unique value.
Replies
5
Views
317
RobP
R

Forum statistics

Threads
1,214,375
Messages
6,119,168
Members
448,870
Latest member
max_pedreira

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