Creating a unique list of values from a selection in multiple columns

Spartanjuli1

New Member
Joined
Sep 13, 2016
Messages
13
Office Version
  1. 365
Platform
  1. Windows
Hello everyone,

In the table attached, I have "intervals" from column A to C
The content shows "Hours" for each interval.

col Acol Bcol C
Row 1
1​
2​
3​
Row 2
1000​
2000​
1200​
Row 3
350​
700​
1050​
Row 4
1000​
800​
3000​
Row 5
350​
700​
1050​
Row 6
400​
2000​
3000​



I want to pull out a single list out of those 3 columns into a single one as the following:
List with unique values based on selection from A2:C6
List
350
400
700
800
1000
1050
1200

I checked various topics but not able to find this particular one.

Thank you for helping there.
Regards
 

Attachments

  • Excel.png
    Excel.png
    5.6 KB · Views: 2

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,801
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Try this:
Book1.xlsx
ABCDEFG
1123Unique ValuesVersion 2
210002000120010001000
3350700105020002000
41000800300012001200
53507001050350350
640020003000700700
710501050
8800800
930003000
10400400
11 
12 
13 
Sheet1
Cell Formulas
RangeFormula
F2:F10F2=INDEX($A$2:$C$6, MIN(IF(COUNTIF($F$1:F1, $A$2:$C$6)=0, ROW($A$2:$C$6)-MIN(ROW($A$2:$C$6))+1)), MATCH(0, COUNTIF($F$1:F1, INDEX($A$2:$C$6, MIN(IF(COUNTIF($F$1:F1,$A$2:$C$6)=0, ROW($A$2:$C$6)-MIN(ROW($A$2:$C$6))+1)), , 1)), 0), 1)
E2:E13E2=INDIRECT(TEXT(MIN(IF(($A$2:$C$6<>"")*(COUNTIF($E$1:E1,$A$2:$C$6)=0),ROW($2:$6)*100+COLUMN($A:$C),7^8)),"R0C00"),)&""
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Last edited:
Solution

Spartanjuli1

New Member
Joined
Sep 13, 2016
Messages
13
Office Version
  1. 365
Platform
  1. Windows
Hello,
That's great this way and working really good !
Last challenge on this one if possible: how would it be possible to sort them from smallest to largest ?

That would be it.
Thank you a lot
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,801
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Please Upload Account Detail to we Know what version of Excel & Os you Used. to Help based on.
For example if you have office 365 you can Use Sort function.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,801
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Because You have Office 365 Try this Easy Method.

Excel Formula:
=SORT(UNIQUE(FILTER(A2:C6,A2:C6<>"")))
 

Spartanjuli1

New Member
Joined
Sep 13, 2016
Messages
13
Office Version
  1. 365
Platform
  1. Windows
Hello,

Sorry for the time to answer here, I tried as well with the SORT and it works, great tip !

Have a good day,
I am grateful for your assistance !
 

Tupe77

Board Regular
Joined
Nov 26, 2020
Messages
97
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Here is an additional VBA based solution.
Select the area where the values are and run the macro "TS_Values_Count_Sort"

VBA Code:
Sub TS_Values_Count_Sort()

On Error GoTo ErrHand
Application.Calculation = xlManual: Application.ScreenUpdating = False: Application.DisplayAlerts = False

    Dim SeaRNG As Range, ResRNG As Range, Cell As Range
    Set SeaRNG = Selection
    Dim Tmp As Single ' If need count strings then change to As String
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    Dim dictNew As Object: Set dictNew = CreateObject("Scripting.Dictionary")
    Dim ARL As Object: Set ARL = CreateObject("System.Collections.ArrayList")
    Dim key As Variant, keyN As Variant, i As Integer
            
    Set ResRNG = Range("E1") ' Range to return values (Writes to E Columns)
        For Each Cell In SeaRNG
            Tmp = Cell.Value
            If dict.exists(Tmp) Then
                dict(Tmp) = dict(Tmp) + 1
            Else
                dict.Add (Tmp), 1
           End If
        Next
        
    'dictionary To Arraylist
    For Each key In dict
        ARL.Add key
    Next key
    
    ' Sort the keys
    ARL.Sort
    'ARL.Reverse
    
    For Each keyN In ARL
        dictNew.Add keyN, dict(keyN)
    Next keyN

            i = 1
            ResRNG.Value = "Value" ': ResRNG.Offset(0, 1).Value = "Count"
            
                For Each key In dictNew.Keys
                    'If dictNew(key) = 1 Then
                            ResRNG.Offset(i).Value = key
                            'ResRNG.Offset(i, 1).Value = dict(key)
                            i = i + 1
                    'End If
                Next key


                
ErrHand:
Application.Calculation = xlAutomatic: Application.ScreenUpdating = True: Application.DisplayAlerts = True
If Err.Number <> 0 Then MsgBox "Something went badly wrong!" & vbCrLf & "VBA-code is ended!" & vbCrLf & vbCrLf & "Error number: " & Err.Number & " " & Err.Description: End

End Sub
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,801
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
You're Welcome & Thanks for Feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,573
Messages
5,637,124
Members
416,959
Latest member
Mohzein

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
Top