mymailwasher
New Member
- Joined
- Mar 15, 2022
- Messages
- 1
- Office Version
- 365
- 2016
- Platform
- Windows
- 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.
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,
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: