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

#### Spartanjuli1

##### New Member
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

#### Attachments

• 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,)

##### Well-known Member
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:

#### Spartanjuli1

##### New Member
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

##### Well-known Member
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.

##### Well-known Member

Because You have Office 365 Try this Easy Method.

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

#### Spartanjuli1

##### New Member
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
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
End If
Next

'dictionary To Arraylist
For Each key In dict
Next key

' Sort the keys
ARL.Sort
'ARL.Reverse

For Each keyN In ARL
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

##### Well-known Member
You're Welcome & Thanks for Feedback.

Replies
15
Views
255
Replies
9
Views
241
Replies
22
Views
343
Replies
7
Views
173
Replies
3
Views
117

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.

### Which adblocker are you using?

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

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