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

[Spartanjuli1]

Hello everyone,

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

	col A	col B	col 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

 

[maabadi]

Try this:

Book1.xlsx
	A	B	C	D	E	F	G
1	1	2	3		Unique Values	Version 2
2	1000	2000	1200		1000	1000
3	350	700	1050		2000	2000
4	1000	800	3000		1200	1200
5	350	700	1050		350	350
6	400	2000	3000		700	700
7					1050	1050
8					800	800
9					3000	3000
10					400	400
11
12
13
Sheet1

Cell Formulas
Range	Formula
F2:F10	F2	=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:E13	E2	=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.

 

[Spartanjuli1]

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]

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]

Because You have Office 365 Try this Easy Method.

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

 

[Spartanjuli1]

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]

Here is an additional VBA based solution.
Select the area where the values are and run the macro "TS_Values_Count_Sort"

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]

You're Welcome & Thanks for Feedback.