Extracting unique records from excel

Extracting unique records from excel

Post by Rossi » Thu, 10 Jul 2003 23:04:46



Hope someone can help, What I would like to do is get rid of any duplicates
in "name1" column but get excel to check the date in the "date" column and
remove the newest row, if this is not possible can i copy unique records
applied to the "name1" column and take the whole row  to another sheet or
area of the same sheet, ive had it working for 1 column at a time but can't
take the entire line aswell !!!!

name1       data2    data3    data4    date

example1     x           x            x    12/12/03
example2     x           x            x    12/01/03
example1     x           x            x    13/12/03

TIA

Rossie

 
 
 

Extracting unique records from excel

Post by Rossi » Thu, 10 Jul 2003 23:46:44


Cracked it ! I went to http://www.cpearson.com/excel/deleting.htm and found
just what i was after, thanks anyway, ive included the macro for those of
you who may need it too.

Cheers

Rossie

 Suppose you have two columns of data -- column A containing some names, and
column B containing some dates.  If the data is grouped (not necessarily
sorted) by column A (but not necessarily by column B), this code will delete
the duplicates rows, but retaining the latest entry (by column B) of each
name in column A.

Sub DeleteTheOldies()
Dim RowNdx As Long
For RowNdx = Range("A1").End(xlDown).Row To 2 Step -1
    If Cells(RowNdx, "A").Value = Cells(RowNdx - 1, "A").Value Then
        If Cells(RowNdx, "B").Value <= Cells(RowNdx - 1, "B").Value Then
            Rows(RowNdx).Delete
        Else
            Rows(RowNdx - 1).Delete
        End If
    End If
Next RowNdx
End Sub


Quote:> Hope someone can help, What I would like to do is get rid of any
duplicates
> in "name1" column but get excel to check the date in the "date" column and
> remove the newest row, if this is not possible can i copy unique records
> applied to the "name1" column and take the whole row  to another sheet or
> area of the same sheet, ive had it working for 1 column at a time but
can't
> take the entire line aswell !!!!

> name1       data2    data3    data4    date

> example1     x           x            x    12/12/03
> example2     x           x            x    12/01/03
> example1     x           x            x    13/12/03

> TIA

> Rossie


 
 
 

1. vlookup a record then extract three fields from the found record

I was recently given an excel spreadsheet of 6400 employee mail
records and asked to lookup their employee numbers in a 18000 line HR
file.   Vlookup handled the lookup "pretty well" except for the
multiple "John Smith",s etc.   My question is not about the duplicates
for now, but how would be the best way to pickup multiple fields from
the HR records.    In ignorance, I used a vlookup for ID in Col A,
another vlookup for EMP status in col B, and another for location in
col C.  I believe that I caused excel to search the HR records three
times for each email record.

I couldn't find a way to vlookup once then somehow use the row
location of the HIT to access the other fields.

I suspect I am using the wrong function but couldn't find a better
one.   I don't know how to use VBA so am looking for an excel
solution.

Thanks for any help.

2. how to avoid winoldapp & FCB unavaliable?

3. Excel on PC: how to extract records

4. lexmark printers with hpux support from $110 to 3900

5. Data Extracting - Excel equivalent of Lotus /Data Query Extract

6. Looking for recomendations on portables...

7. Formula to Extract Unique Values :

8. SurfSquirrel+ZIP=SLOW

9. Excel: help with extracting data from another excel file

10. 123 V5 - Unique Record Extraction from a Database

11. Is it possible to record unique transactions for multiple users?

12. Unique IDs and Record Matching