Getting Unique Data Only!

[James8309]

Hi everyone.

I have multiple excel file reports saved under C:\My Docs\. Each
reports contain large amount of data and even though its in the same
format everymonth, data changes.

1. Column H:H in all the reports contains 3 letter + 2 number digit
code.
e.g. DTF01, COF01 etc

2. There are multiple of those codes in column H:H quite many of them
repeating so I just do an advanced filter -> unique records only to
pull up the unique ones.

3. Every report has those codes in column H:H and they are different
in numbers, different in code.

What I did in VBA is just to open up each file automatically, copy
column H:H then paste into new workbook column A:A. Once it did that
for all the reports, it performs advanced filter -> unique records
only then pastes in to column B:B.

I can achieve the end result by doing this however I found this
extremly not efficient.

Is there any other way to extract unique records only from column H:H
for all the reports combined?

Thank you for your help in advance!

 

[Joel]

would use advance filters when I copy the data from each workbook and then
use advance filters again after all the data is placed in the new workbook.

 

[kg_singapore]

Hi,

Use the following code in a macro and run it

Range("A1:A11").Select 'Here type your source range A1:A11
Range("A1:A11").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"G1"), Unique:=True 'Here type ur destination range, if different
sheet prefix the sheet name sheet1.range(..).advancedfilter

 

[James8309]

Hi,

Use the following code in a macro and run it

 Range("A1:A11").Select  'Here type your source range A1:A11
    Range("A1:A11").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "G1"), Unique:=True   'Here type ur destination range,if different
sheet prefix the sheet name sheet1.range(..).advancedfilter













- Show quoted text -

Thanks guys!