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!

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.

"James8309" wrote:

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!

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" wrote:

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!

On Jun 16, 4:46*pm, kg_singapore
wrote:
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" wrote:
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!- Hide quoted text -

- Show quoted text -

Thanks guys! :D