Find unique column of data

  • Hi Guys,


    I have a query which I cannot fathom out the way to do it.


    I am creating a bit of vba that goes through some database tables to compare to those in a different version of the same database for testing purposes.
    The script I have works fine at comparing the data however some of the tables are in different orders. What I need to do is sort the data according to a unique identifier, here in lies my issue.


    The column of the identifier isnt always in the same place ie I would want it always in column A but as its third party software I have no control over this.


    What I would like to know is is there a way to get a vba code look through the columns of a given table and find the column which contains only unique values?


    Thanks in advance

  • Re: Find unique column of data


    Update:


    This works in a way


    Code
    x = Evaluate("SUM(IF(FREQUENCY(E2:E3834,E2:E3834)>0,1))")


    However I am struggling to use the above in a loop of columns.


    Any ideas would be appreciated

  • Re: Find unique column of data


    So does only ONE column contain ONLY unique values? All other columns have at least one duplicate value in column? Is that correct?

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




  • Re: Find unique column of data


    There is always an ID column of some form which will contain only unique values. Potentially some of the other columns could contain entirelly unique values as well but as it is financial trading data this is unlikely but I cannot rule it out all together

  • Re: Find unique column of data


    I am thinking you could maybe loop through each column using the Scripting.Dictionary and if the number of .keys equals the number of rows.. then you've found your column with only unique values in it..


    But that would mean looping through each column.. probably not the fastest method..


    How many values are likely to be in these columns.. hundreds or thousands?

  • Re: Find unique column of data


    Thankfully it doesnt have to be that fast so im not too worried about that side of things.


    In terms of size they vary greatly, majority have just a couple hundred rows but a couple (typically the ones we will need to run through most) are in the tens of thousands of rows currently

  • Re: Find unique column of data


    You could use the following if there are no empty rows



    Or as Apo suggested this would work if there are some rows with empty values (as long as the unique column does not have empty rows)



    Try attached workbook

    Files

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




  • Re: Find unique column of data


    Actually in case the column with unique values has empty rows just change the line

    Code
    If tstResult = Cells(Rows.Count, k).End(xlUp).Row Then


    to

    Code
    If tstResult = WorksheetFunction.CountA(testRng) Then

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




  • Re: Find unique column of data


    I cannot think of a faster way - however to maybe just increase speed slightly (I assume you are using option 2) add an Exit For statement


    At least that way it will stop once it has found the result and not test more columns once it has the result.
    (Sorry Apo - credited wrong person for the scripting dictionary suggestion)

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!