Get unique values from one column from all spreadsheets in a folder and copy to a new spreadsheet

mymailwasher

New Member
Joined
Mar 15, 2022
Messages
1
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
Hi,

I have anywhere from 60-100 spreadsheets generated from a reporting process in a folder. Each spreadsheet has set number of columns (A through G). Column G (Notes) is used by end-users to put notes. I am working on creating a unique list of all these notes from all the spreadsheets in the folder. Objective is to -
=================
1. Open the folder picker dialog to allow user selection ('If no folder is selected, abort)
2. Assign selected folder to some variable, say MyFolder
3. Loop through all files in a folder until DIR cannot find anymore
a. Open the first file (and then so on..)
b. Extract unique values from the Notes column
c. Copy these unique values to a new spreadsheet (call it Target spreadsheet starting col A1)
d. Keep doing it for all the files in the folder
4 Do a final unique on the target spreadsheet column A.
==================

I am able to get up to 3.a but not sure how to get unique values from the Notes column. I tried following -
ActiveSheet.Range("G:G").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ActiveSheet.Range("B2"), Unique:=True

I need help in doing 3.b onwards. Appreciate reading and big thanks if you could help. Please let me know if a mini-sheet can help and I will upload it promptly.
The number of rows in the spreadsheet are from 1 to 80K.
 
Last edited by a moderator:

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Forum statistics

Threads
1,223,882
Messages
6,175,164
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top