Unique list of number sorted and extract with criteria

GerryZ

GerryZ

Well-known Member
Joined
Jul 4, 2014
Messages
1,224
Office Version
  1. 365
Platform
  1. Windows
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.
Sort by date Sort by votes
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
 
Upvote 0
GerryZ said:
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)
​
Click to expand...

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
 
Upvote 0
. 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.
 
Upvote 0
Sorry everybody for not answering
I'm so bad
but i was lost with my 100s of email
Thank you very much everybody and I will use vishaal589 formula
Thankyou
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Solved
unique with sort
Replies
7
Views
161
Peter_SSs
Peter_SSs
C
  • Solved
VLOOKUP or INDEX/MATCH or similar - across multiple workbooks
Replies
2
Views
336
CraigG
C
P
  • Solved
Automate a FIND and GOTO the result button VBA code
Replies
5
Views
228
Alex Blakenburg
A
R
  • Question
Unique IF Contains Specific Characters
Replies
3
Views
91
Fluff
Fluff
E
  • Solved
Obtain a Unique list based on the sum of another column
Replies
3
Views
209
Fluff
Fluff

Forum statistics

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

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