Exporting Unique Ids from a data list

[mgao77]

Hi All,

I have a database which has information being dumped into it every month. The data is an employee list with lots of information like pay, performance, levels etc. Every month, only some elements of the data changes.

To analyze this data, I need to find a way to extract the employee ids every month- but only unique ones.

For example, say that, in January, I had the following IDs: 1A, 2B, 1C

and in February, I had 1A, 1C, 1D... (meaning there was an attrition with 2B) and 1D is a new joiner.

In February, when extracting the IDs, I will already have 1A, 2B, and 1C but when doing the extract, I will need 1D to be pulled it and not double count 1A,2B, and 1C.. Im sure this is a simple formula, but i cant get my head around it.

Any thoughts?

Thanks guys,

 

[Xiq]

Why not a Pivot Table?

 

[mgao77]

I afraid it will be too bulky as we will also have to run some formulas off of it. I'm hoping for a more flexable approach-either formulas or macro

 

[Debraj]

Hi Mgao77!​

​

Please upload a sample file..​

On the meanwhile..​

Try this...​

​

Sub UniqueId()
    Do
        deb = Application.Intersect(ActiveSheet.UsedRange, Application.InputBox("Please select Range..", _
            "Single Column Please..", "A1:A20", , , , , 8))
            For i = 1 To UBound(deb)
                If InStr(raj & ",", "," & deb(i, 1) & ",") = 0 Then raj = raj & "," & deb(i, 1)
            Next i
        ans = MsgBox("Do you want to add more List", vbYesNo)
    Loop Until ans = vbNo
   
    roy = Split(Mid(raj, 2), ",")
    Range(Application.InputBox("Click where you want to paste Unique Records", "Single cell please..", _
        "x1", , , , , 8).Address).Resize(UBound(roy) + 1) = Application.Transpose(roy)
End Sub

1st select the 1st List.. and then select the second list.. so on..

and finally click on a blank area.. where you want to get the result...

 

[Faseeh]

Hi mgao77,

If you search this forum you will find many post related to this, i just googled and found this one handy:

http://www.get-digital-help.com/200...-and-the-duplicates-in-excel-from-one-column/

 

[mgao77]

Here is the sheet.

Basically, the formula (preferably) or the macro will have to look at the "database" tab and return the IDs to the "current month" tab that have multiple records only ONCE and also the IDs that are unique... Any thoughts?

 

[Faseeh]

Hi mgao77,

This one is adjusted for your sheet so plz try:

=INDEX($A$2:$A$400,MATCH(0,INDEX(COUNTIF($C$1:C1,$A$2:$A$400),0,0),0))

Enter in C2 and drag down.

 

[mgao77]

Thanks everyone for your help