# Unique list of number sorted and extract with criteria

#### GerryZ

##### Well-known Member
Hello everyBody
I need a formula in E2 and copy down to create a unique list of number and in D2 a formula to copy down that extratct the corriponding value
as in this example
thank you!

Book1
ABCDE
1pointsListpointsList
2P13P4,P52
3P27P1,P3,P73
4P33P64
5P42P87
6P52
7P64
8P73
9P87
Foglio1 (2)

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

I'm not 100% sure you can do it the way you wanted to. But this should work:

I have created a function, put this in an empty module in the VBA editor

You need to pass the function 4 values
1) A range containing the 'labels' in your example this is \$A\$2:\$A\$9
2) A range containing the 'values' in your example this is \$B\$2:\$B\$9
3) you need to provide the value being looked up "2","3" etc...
4) if you want to return the label then put '0' if you want to return the count put '1'

Code:
``````    Function ListCount(LabelRange As Range, ValueRange As Range, MatchValue As String, TextOrCount As Integer)
x = x

Dim TheText As String
TheText = ""
Dim TheCount As Integer
TheCount = 0
Dim Position As Integer
Position = 1

Dim Counter As Integer
Counter = 0

For Each cellz In ValueRange.Cells
Counter = Counter + 1
If cellz.Value = MatchValue Then
Dim Counter2 As Integer
Counter2 = 0
For Each cellz2 In LabelRange.Cells
Counter2 = Counter2 + 1
If Counter2 = Counter Then
If TheText = "" Then
TheText = cellz2
Else
TheText = TheText & "," & cellz2
End If
End If
Next

TheCount = TheCount + 1

End If

Next
If TextOrCount = 0 Then
ListCount = TheText
Else
ListCount = TheCount
End If

End Function``````

Hello everyBody
I need a formula in E2 and copy down to create a unique list of number and in D2 a formula to copy down that extratct the corriponding value
as in this example
thank you!

ABCDE
1pointsListpointsList
2P13P4,P52
3P27P1,P3,P73
4P33P64
5P42P87
6P52
7P64
8P73
9P87

<tbody>
</tbody>
Foglio1 (2)

Hi,
Try this in cell E2:

=SMALL(\$B\$2:\$B\$9,1+SUMPRODUCT(COUNTIF(\$B\$2:\$B\$9,\$E\$1:E1)))

in Cell D2:

=CHOOSE(SUMPRODUCT(--(H2=\$B\$2:\$B\$9)),INDEX(\$A\$2:\$B\$9,MATCH(H2,\$B\$2:\$B\$9,0),1),INDEX(\$A\$2:\$B\$9,MATCH(H2,\$B\$2:\$B\$9,0),1)&", "&INDEX(\$A\$2:\$B\$9,MATCH(H2,\$B\$2:\$B\$9,0)+1,1),INDEX(\$A\$2:\$B\$9,MATCH(H2,\$B\$2:\$B\$9,0),1)&", "&INDEX(\$A\$2:\$B\$9,MATCH(H2,\$B\$2:\$B\$9,0)+2,1)&", "&INDEX(\$A\$2:\$B\$9,MATCH(H2,\$B\$2:\$B\$9,0)+3,1))

Note:
1) List data (Column B) must be in Sorting order.
2) Max count of lookup is 3.

Thanks & Regards,
CMA Vishal Srivastava

. Hi Gerry,
. I stumbled over this recent Thread of yours. I was not sure if you have now a satisfactory solution?
.
. In any case I had a go At applying / “ Tweaking “ a similar solution I did in a Thread....
.
. Full description of the development of these Formulas starts from about Post #6 in that Thread:
http://www.mrexcel.com/forum/excel-...function-pull-column-b-populate-column-c.html
.
. I am not sure if my attempt may be very inefficient and unnecessary complicated. But if I attempt to give the formulas here, and drop off the file I was working on, you may be able to get at least some ideas from it. ( File XL 2007 : https://app.box.com/s/v4zrgjyexvu9bznuedh046gktm68kghq )

. Some general notes:
. 1) List or points can be in any order.
. 2) Max count of Index is about 18. ( So up to 18 Concatenations of the points )

. Here are my final obtained results which I think are at least very close to what you want, ( Note you may have had a typo in your Post #1 ? – I suspect List 7 should have Points P2 and P8, not just P8 ? )

Using Excel 2007
Row\Col
A
B
C
D
E
1
pointsListpointsList
2
P1
3​
P4 , P5 , 2
3
P2
7​
P1 , P3 , P73
4
P3
3​
P64
5
P4
2​
P2 , P8 , 7
6
P5
2​
7
P6
4​
8
P7
3​
9
P8
7​
 Gerry2d

........................................................................................

. I was not able to do it in a simple set of two formulas. But I have a row of formulas, which as per your original request you can copy ( drag ) down.
. Formula Table:

Using Excel 2007
Row\Col
D
E
F
G
H
I
J
K
2
=IFERROR(VLOOKUP(F2,\$I\$2:\$K\$234,3,FALSE),"")=SUBSTITUTE(F2,"F","")=IFERROR(INDEX(\$I\$2:\$I\$9, MATCH(0, COUNTIF(\$F\$1:\$F1,\$I\$2:\$I\$9), 0),1),"")=IFERROR(INDEX(\$H\$2:\$H\$9, MATCH(0, COUNTIF(\$G\$1:\$G1,\$H\$2:\$H\$9), 0),1),"")="F"&B2=INDEX(\$G\$2:\$H\$9, MATCH(SMALL(COUNTIF(\$H\$2:\$H\$9,"" & "<"&""&\$H\$2:\$H\$9&""), ROWS(\$I\$1:\$I1)), COUNTIF(\$H\$2:\$H\$9, "<"&\$H\$2:\$H\$9), 0),2)=INDEX(\$A\$2:\$J\$9,SMALL(IF(\$H\$2:\$H\$9=\$I2,ROW(\$H\$2:\$H\$9)-ROW(\$H\$2)+1),COUNTIF(\$I\$2:\$I2,\$I2)),1)=SUBSTITUTE(IF(OR(\$I2=\$I1,\$I2=""),"",INDEX(IF(\$I2=\$I2:\$I\$234,\$I2:\$J\$234,""),1,2) & " , ") & IF(OR(\$I2=\$I1,\$I2=""),"",INDEX(IF(\$I2=\$I2:\$I\$234,\$I2:\$J\$234,""),2,2) & " , ") & IF(OR(\$I2=\$I1,\$I2=""),"",INDEX(IF(\$I2=\$I2:\$I\$234,\$I2:\$J\$234,""),3,2) & " , ") & IF(OR(\$I2=\$I1,\$I2=""),"",INDEX(IF(\$I2=\$I2:\$I\$234,\$I2:\$J\$234,""),4,2) & " , ") & IF(OR(\$I2=\$I1,\$I2=""),"",INDEX(IF(\$I2=\$I2:\$I\$234,\$I2:\$J\$234,""),5,2) & " , ") & IF(OR(\$I2=\$I1,\$I2=""),"",INDEX(IF(\$I2=\$I2:\$I\$234,\$I2:\$J\$234,""),6,2) & " , ") & IF(OR(\$I2=\$I1,\$I2=""),"",INDEX(IF(\$I2=\$I2:\$I\$234,\$I2:\$J\$234,""),7,2) & " , ") & IF(OR(\$I2=\$I1,\$I2=""),"",INDEX(IF(\$I2=\$I2:\$I\$234,\$I2:\$J\$234,""),8,2) & " , ") & IF(OR(\$I2=\$I1,\$I2=""),"",INDEX(IF(\$I2=\$I2:\$I\$234,\$I2:\$J\$234,""),9,2) & " , ") & IF(OR(\$I2=\$I1,\$I2=""),"",INDEX(IF(\$I2=\$I2:\$I\$234,\$I2:\$J\$234,""),10,2) & " , ") & IF(OR(\$I2=\$I1,\$I2=""),"",INDEX(IF(\$I2=\$I2:\$I\$234,\$I2:\$J\$234,""),11,2) & " , ") & IF(OR(\$I2=\$I1,\$I2=""),"",INDEX(IF(\$I2=\$I2:\$I\$234,\$I2:\$J\$234,""),12,2) & " , ") & IF(OR(\$I2=\$I1,\$I2=""),"",INDEX(IF(\$I2=\$I2:\$I\$234,\$I2:\$J\$234,""),13,2) & " , ") & IF(OR(\$I2=\$I1,\$I2=""),"",INDEX(IF(\$I2=\$I2:\$I\$234,\$I2:\$J\$234,""),14,2) & " , ") & IF(OR(\$I2=\$I1,\$I2=""),"",INDEX(IF(\$I2=\$I2:\$I\$234,\$I2:\$J\$234,""),15,2) & " , ") & IF(OR(\$I2=\$I1,\$I2=""),"",INDEX(IF(\$I2=\$I2:\$I\$234,\$I2:\$J\$234,""),16,2) & " , ") & IF(OR(\$I2=\$I1,\$I2=""),"",INDEX(IF(\$I2=\$I2:\$I\$234,\$I2:\$J\$234,""),17,2) & " , ") & IF(OR(\$I2=\$I1,\$I2=""),"",INDEX(IF(\$I2=\$I2:\$I\$234,\$I2:\$J\$234,""),18,2) & " , ")," , , ","")
 Gerry2d

.........................................

. I am sure, Gerry, you understand already the following, but I include it for anyone following that may be unaware of “CSE” type formulas.

Notes:

. 1) The formulas are of the “CSE” sort which are placed in one cell and dragged (copied) down
. 2) To put each of these formulas in turn into the spreadsheet:
. 2a) copy ( Ctrl C ) the formula complete from the above Formula Table to clipboard
. 2b) select ( click in ) the cell where the formula should go
. 2c) Hit F2 or select ( click in ) the formula bar (To be on the safe side do both!! )
. 2d) paste in the formula from the clipboard ( Ctrl V ) ( check that the formula includes a = at the start)
. 2e) now you do the famous “CSE” . – That is hold down the keys Ctrl and Shift, and hit Enter.
. 3) select the entire range D2 : K2
. 4) click and hold on the tiny black square at the right of the selection, and drag the entire row of formulae down.
. ( Note the formulas in column D E and H do not have to be “CSE” type, and can be copied in “normally” , but it does no harm to enter them all in as “CSE” as per the instructions .1) to .4) above )
........................................

. Hope my attempt could be of some help
.
. Alan

..............................................
P.s.1 : File again XL 2007
https://app.box.com/s/v4zrgjyexvu9bznuedh046gktm68kghq

P.s. 2: Formula Table again with HMTL Maker:

Excel 2007
ABCDEFGHIJK
1pointsListpointsListUnique from Ordered CategoryUnique from UnOrdered CategoryUngrouped Category or Sort BoxOrdered Category or Sort BoxOrdered Part Number
2P13P4 , P5 ,2F2F3F3F2P4P4 , P5 ,
3P27P1 , P3 , P73F3F7F7F2P5
4P33P64F4F2F3F3P1P1 , P3 , P7
5P42P2 , P8 ,7F7F4F2F3P3
6P52F2F3P7
7P64F4F4P6P6
8P73F3F7P2P2 , P8 ,
9P87F7F7P8
Gerry2d
Cell Formulas
RangeFormula
D2=IFERROR(VLOOKUP(F2,\$I\$2:\$K\$234,3,FALSE),"")
E2=SUBSTITUTE(F2,"F","")
H2="F"&B2
F2{=IFERROR(INDEX(\$I\$2:\$I\$9, MATCH(0, COUNTIF(\$F\$1:\$F1,\$I\$2:\$I\$9), 0),1),"")}
G2{=IFERROR(INDEX(\$H\$2:\$H\$9, MATCH(0, COUNTIF(\$G\$1:\$G1,\$H\$2:\$H\$9), 0),1),"")}
I2{=INDEX(\$G\$2:\$H\$9, MATCH(SMALL(COUNTIF(\$H\$2:\$H\$9,"" & "<"&""&\$H\$2:\$H\$9&""), ROWS(\$I\$1:\$I1)), COUNTIF(\$H\$2:\$H\$9, "<"&\$H\$2:\$H\$9), 0),2)}
J2{=INDEX(\$A\$2:\$J\$9,SMALL(IF(\$H\$2:\$H\$9=\$I2,ROW(\$H\$2:\$H\$9)-ROW(\$H\$2)+1),COUNTIF(\$I\$2:\$I2,\$I2)),1)}
K2{=SUBSTITUTE(IF(OR(\$I2=\$I1,\$I2=""),"",INDEX(IF(\$I2=\$I2:\$I\$234,\$I2:\$J\$234,""),1,2) & " , ") & IF(OR(\$I2=\$I1,\$I2=""),"",INDEX(IF(\$I2=\$I2:\$I\$234,\$I2:\$J\$234,""),2,2) & " , ") & IF(OR(\$I2=\$I1,\$I2=""),"",INDEX(IF(\$I2=\$I2:\$I\$234,\$I2:\$J\$234,""),3,2) & " , ") & IF(OR(\$I2=\$I1,\$I2=""),"",INDEX(IF(\$I2=\$I2:\$I\$234,\$I2:\$J\$234,""),4,2) & " , ") & IF(OR(\$I2=\$I1,\$I2=""),"",INDEX(IF(\$I2=\$I2:\$I\$234,\$I2:\$J\$234,""),5,2) & " , ") & IF(OR(\$I2=\$I1,\$I2=""),"",INDEX(IF(\$I2=\$I2:\$I\$234,\$I2:\$J\$234,""),6,2) & " , ") & IF(OR(\$I2=\$I1,\$I2=""),"",INDEX(IF(\$I2=\$I2:\$I\$234,\$I2:\$J\$234,""),7,2) & " , ") & IF(OR(\$I2=\$I1,\$I2=""),"",INDEX(IF(\$I2=\$I2:\$I\$234,\$I2:\$J\$234,""),8,2) & " , ") & IF(OR(\$I2=\$I1,\$I2=""),"",INDEX(IF(\$I2=\$I2:\$I\$234,\$I2:\$J\$234,""),9,2) & " , ") & IF(OR(\$I2=\$I1,\$I2=""),"",INDEX(IF(\$I2=\$I2:\$I\$234,\$I2:\$J\$234,""),10,2) & " , ") & IF(OR(\$I2=\$I1,\$I2=""),"",INDEX(IF(\$I2=\$I2:\$I\$234,\$I2:\$J\$234,""),11,2) & " , ") & IF(OR(\$I2=\$I1,\$I2=""),"",INDEX(IF(\$I2=\$I2:\$I\$234,\$I2:\$J\$234,""),12,2) & " , ") & IF(OR(\$I2=\$I1,\$I2=""),"",INDEX(IF(\$I2=\$I2:\$I\$234,\$I2:\$J\$234,""),13,2) & " , ") & IF(OR(\$I2=\$I1,\$I2=""),"",INDEX(IF(\$I2=\$I2:\$I\$234,\$I2:\$J\$234,""),14,2) & " , ") & IF(OR(\$I2=\$I1,\$I2=""),"",INDEX(IF(\$I2=\$I2:\$I\$234,\$I2:\$J\$234,""),15,2) & " , ") & IF(OR(\$I2=\$I1,\$I2=""),"",INDEX(IF(\$I2=\$I2:\$I\$234,\$I2:\$J\$234,""),16,2) & " , ") & IF(OR(\$I2=\$I1,\$I2=""),"",INDEX(IF(\$I2=\$I2:\$I\$234,\$I2:\$J\$234,""),17,2) & " , ") & IF(OR(\$I2=\$I1,\$I2=""),"",INDEX(IF(\$I2=\$I2:\$I\$234,\$I2:\$J\$234,""),18,2) & " , ")," , , ","")}
Press CTRL+SHIFT+ENTER to enter array formulas.

but i was lost with my 100s of email
Thank you very much everybody and I will use vishaal589 formula
Thankyou

Replies
7
Views
161
Replies
2
Views
336
Replies
5
Views
228
Replies
3
Views
91
Replies
3
Views
209

1,203,514
Messages
6,055,840
Members
444,828
Latest member
StaffordStag

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