I am using Excell 97 and need to consolidate
many tables which are located in one folder as
individual files onto one workbook as sheets.
The Data is something like this:
File 1: (Frank.xls) \\\\\File2: (Mary.xls)\\\\\\ File 3: (John.xls)
\\\\\\\\1\\\\\\\\\\\2\\\\\\\\\\1\\\\\\\\\\\2\\\\\\ \\\\1\\\\\\\\\\\\\2
A (Software)\ Frank\\\ (Software)\\\ Mary \\\(Software)\\ John
B Abode \\\\\\\\1 \\\\\\\Abode \\\\\\\1 \\\\\\\Abode\\\\\\\\\ 2
C A&T \\\\\\\\\\\3\\\\\\\ Photoshop\\ 1\\\\\\\\ A&T \\\\\\\\\\\\ 1
D MS Excel\\\\\ 1 \\\\\\\Win Zip \\\\\\2 \\\\\\\MS Excel\\\\\\\ 2
E MS Office\\\\\ 4 \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\Win Zip\\\\\\\\\ 1
F MS Word\\\\\ 1
G Win Zip\\\\\\\ 2
I'm trying to consolidate a report in Excel that calls the
files and places them on a seperate sheet and then
finds, sorts the software and creates a field with
the persons name and the number associated
with the software.
In other words:
I need to retrieve each file and place it on a new
sheet for each file and then build a report to
show which person has what software. The Macro
needs to search, sort and fill itself with an assigned button.
So if for example, there is no data on the 1st sheet for
photoshop, I need to create a field when it appears on the
2nd sheet and so on. So the Result sheet will contain all sheets
information on the one with out duplicates.
If I change or add data to one of
the files and run the macro it will update the appropriate
sheet and give the correct result.
Result:
\\\\\\\\\\1\\\\\\\\\\ 2\\\\\\\\\\ 3\\\\\\\\\\ 4
A (Software)\\\\ Frank\\\\\Mary\\\\\ John
B Abode\\\\\\\\\\\ 1\\\\\\\\\\ 1\\\\\\\\\\ 2
C A&T\\\\\\\\\\\\\\ 3\\\\\\\\\\\\\\\\\\\\\\\ 1
D MS Excel\\\\\\\\ 1\\\\\\\\\\\\\\\\\\\\\\\ 2
E MS Office\\\\\\\ 4
F MS Word \\\\\\\\1
G Photo shop\\\\\\\\\\\\\\\\\ 1
H Win Zip\\\\\\\\\ 2\\\\\\\\\\\ 2\\\\\\\\\ 1
I hope that is enough information to help me with this
problem. It is way over my head and it is taking me
ages to do, as I have over 50 Files I need to sift through.
I hope someone out there has done this before
and can help me.
Thanks in advance
Hanuman.
