• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Unique Records

Portucale

Portucale

Member
Hi,
I have a table containing several weeks of data, I am looking to extract the unique records in column 'Installer Identity Number' according to a preselected week, enclosed a sample of what I need.

Thanks in advance for all the help.
 

Attachments

Put this formula in O2, confirm formula as array using Ctrl+Shift+Enter:
=IFERROR(INDEX(E:E,(1/MIN(IF(($A$2:$A$21=$L$5)*(ISNA(MATCH($E$2:$E$21,O1:O$1,0))),ROW($E$2:$E$21))))^-1),"")

Note for the curious:
Why the (1/x)^-1? Because (1/x)^-1 = x, for everything except 0! If there are no true values from the IF function, the MIN will return a 0, and INDEX takes that as a valid input. The 1/x will thrown an error for x=0, and the IFERROR can then trap it.
 
PS. In your sample workbook, be careful. The raw data table has numbers stored as text for "201314", but the rows for 201315 have numbers. Need to be careful when you have numbers stored as text that you are always comparing the right thing, and not a number to a string.
 
@Portucale

Use below formula in cell M2 and copy down.
=IFERROR(INDEX($E$2:$E$21,SMALL(IF(FREQUENCY(IF($A$2:$A$21=$L$5,MATCH($E$2:$E$21,$E$2:$E$21,0)),MATCH($E$2:$E$21,$E$2:$E$21,0)),ROW($E$2:$E$21)-ROW($E$2)+1),ROWS(M$2:M2))),"")

Just advise if any issue.

Regards,
 
Great help, thanks guys, both solutions work:) , the strange thing is that I was 'playing' with the Frequency but was just getting the number of occurrences... doh...
 
One more question if I may, do you know how to execute this in VBA? As I need know to extract the complete list into a separated sheet to perform some calculations.

Thanks
 
There a numerous ways to do this in VBA. Do you want the code that will create a formula, or generate the list by itself?

For instance, here's my "standard" macro that I use to create a unique list. I've got it in my Personal.xls workbook, hotkeyed to Ctrl+alt+u
Code: 
Sub GetUniqueList()
'Ctrl+Alt+u

    Dim rCell As Range
    Dim colUnique As Collection
    Dim sh As Worksheet
    Dim i As Long
   
    'only work on ranges
   If TypeName(Selection) = "Range" Then
       
        'create a new collection
       Set colUnique = New Collection
       
        'loop through all selected cells
       'and add to collection
'XXXXXXXXX  MARK
       For Each rCell In Selection.Cells
            On Error Resume Next
                'if value exists, it won't be added
               colUnique.Add rCell.Value, CStr(rCell.Value)
            On Error GoTo 0
        Next rCell
       
        'make a new sheet to put the unique list
       Set sh = ActiveWorkbook.Worksheets.Add
       
        'Write the unique list to the new sheet
       For i = 1 To colUnique.Count
            sh.Range("A1").Offset(i, 0).Value = colUnique(i)
        Next i
       
        'sort with no headers
       sh.Range(sh.Range("A2"), sh.Range("A2").End(xlDown)) _
            .Sort sh.Range("A2"), xlAscending, , , , , , xlNo
       
    End If
   
End Sub
Where' I put the XXXX MARK comment is the For...Next loop were you could add a criteria to check another range of cells. Something like,
If rCell.offset(-6,0).value = MyCritValue Then

I'm assuming you have some familiarity with VB, but let us know if you need further help.
 
Hi Luke,

I just need a list of the 'Installer Identity Number' for the week selected, would you kindly help me in extracting these Unique records?
I have looked to the VBA, but honestly, I can't see where do i need to change to fit my needs. My knowledge of VBA isn't to great still learning :(
Thanks in advance,
 
Hi Luke,
Many thanks for your time in developing this procedure, I have carefully read the comments in your code and I do understand the way of working, but, I am stuck in a couple of things:(.
For some reason the output is bringing records from other weeks, you can see these in 'sheet2' and highlighted in yellow, also, I do have a 'Define Name' (CurrWeek) which is myWeek, trying to replace the input message with the 'define name'
Code: 
myWeek = CurrWeek
but for some reason I can't get it, may I kindly ask for your, yet again assistance?

File is too big to attach so I placed it in Google Documents:
https://drive.google.com/file/d/0B9tPTGVjoo1qVzBnVjU2MXAyTXM/edit?usp=sharing

Kind regards,
 
I'm afraid I can't access documents outside of Chandoo. My guess is that you have a named Range called CurrWeek? In that case, need to refer to a range, otherwise the VB will think you have a variable called CurrWeek.
Code: 
Dim myWeek as Single
myWeek = Range("CurrWeek").Value
 
You must log in or register to reply here.
Back
Top