Sorting and Importing data from multiple Excel workbooks

Reverend

New Member
Joined
May 24, 2006
Messages
8
I have been using a spreadsheet to log computer users in the computer lab that I work at.

It is time for me to prepare a quarterly report for the organization that finances the computer lab and I would like help automating this process.

I have 3 months of excel files (one file for each day) seperated into three different folders (one folder for each month). Files are named in the following way YYYYMMDD.xls. I would like to create a spreadsheet that can look at all the other spread sheets and sort and pull the following information.

-how many total users? (for the three month period)

- how many users selected the "National Guard" value
- of National Guard users how many were here for TRAINING and how many for EDUCATIONAL.

- how many users selected the "Army" value
- of ARMY users how many were here for TRAINING and how many for EDUCATIONAL.

- how many users selected the "Civilian" value
- of CIVILIAN users how many were here for TRAINING and how many for EDUCATIONAL.

- how many users selected the "Family member" value
- Also how many were for TRAINING and how many for EDUCATIONAL

- how many users selected the "Other" value.
- Also how many were for TRAINING and how many for EDUCATIONAL

I can send a sample of the LOGIN sheet spreadsheet and a copy of the QUARTER report spread sheet if needed. Also I see there are utilities offered that I can post screen shots of the spreadsheets if the would be helpful. Or if anyone can help me come up with some key terms that I can google to find help performing the type of sorting and importing actions that I need. I'm sure it is a simple enough process, but I have no experience working with excel formulas that call on accessing seperate excel documents. Any help will be much appreciated.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Reverend

New Member
Joined
May 24, 2006
Messages
8
providing html view of spreadsheets

Thank you so much for the link to the utility.

The utililty worked fine to convert the quarter report file, but I had problems converting the daily sign in sheet to html. I had to close out several error messages but after closing them all out I did end up with the source code I needed to post in html.
Yoest Quarter report template.xls
ABCDEFGHIJKLM
14. MULTI-USE LEARNING FACILITY (MLF)
2PURPOSE OF VISITCOSTS
3NUMBERON-SITEREMOTEMDEP VOPROTHER FUNDS
4OFEDUCATIONTRAININGEDUCATIONTRAINING
5VISITSACTIVITYACTIVITYACTIVITYACTIVITYEQUIP/SUPCONTRACTSEQUIP/SUPCONTRACTS
6(a)(b)(c)(d)(e)(f)(g)(h)(i)
7ARMYXXXX
8FAMILY MEMBERSXXXX
9CIVILIANXXXX
10RC/NGXXXX
11OTHERXXXX
12TOTAL00000$0$0$0$0
13
Sheet1


I'm posting the DAILY SIGN IN SHEET in a seperate post as there doesn't seem to be enough room to include both of them in one post. (the bottom half of the sheet isn't diplayed as well as the comments I typed below it.)<!--





-->
 

Reverend

New Member
Joined
May 24, 2006
Messages
8
Daily sign in sheet

Below is the daily sign in sheet. I have about 90 of these that I would like to pull data from to include into a quarterly report.</br>
The values for UNIT are - NG/RC (national guard or reservere corps), DAC (department of army civilian), ARMY (regular army soldiers), FAMILY (family members of personel stationed here, OTHER (soldiers from non US contingents)
20060913.xls
ABCDEFGHIJ
1Upcoming college classes for you: Ethics in Management Instructor 1SG Redrick 22-AUG - 02 NOV Military Science II Instructor CPT Kost 10-SEPT - 20 SEPT SIGN UP NOW !!!
2By using this terminal you agree to all posted lab rules, users will be banned if rules are not followed.
3LAST NAMEMFO IDRANKUNITUSECOMP #TIME INTIME OUTTOTAL TIME
4SLAUGHTER28571SPCNG\RCTRAINING128:2408:45021
5GLANTON28710SGTNG\RCTRAINING118:4108:4504
6YOUNG28544SPCNG\RCTRAINING38:2709:07040
7WHITE28731SGTNG\RCTRAININGL67:5909:34135
8GUTHRIE28714SGTNG\RCEDUCATION59:1909:47028
9WHITE J29788CIVDACTRAINING139:0410:0410
10BRANDON28253SGTNG\RCTRAINING89:3710:04027
11COLLAZO29725SPCNG\RCTRAINING510:0810:30022
12HALL28813PFCARMYTRAINING119:3710:3912
13DELACRUZ28550CIVOTHERTRAINING1310:1410:50036
14SNEAD28540CPLNG\RCTRAINING710:0010:54054
15GUTHRIE28714SGTNG\RCTRAINING1410:4511:04019
16WALLACE28427CPLNG\RCTRAINING810:1411:13059
17ROQUE28725SGTNG\RCTRAINING1210:5411:20026
18SULLIVAN28635SGTNG\RCEDUCATIONL38:3711:27050
ALC Sign IN

We greatly appreciate your use of time and skills to help us out. Thank you to all of you who took the time to view this post and thank you even more for any advice or assistance you have to offer.
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

Hi
How do you know ON-SITE/REMOTE ?
It would help us to understand if you post desired result out of your sample..
 

joose

Active Member
Joined
Mar 16, 2005
Messages
250
My first thought on the easiest way to do this would be to write a macro that would copy the information from each of the sign-in sheets and paste it to one big list within the "quartly report" workbook. Then you could use a pivot table to get all the different numbers you need. When copying the information you might also want to add a column for the date if you don't already have one. Does that sound reasonable? Do you have any idea how to approach that?
 

Reverend

New Member
Joined
May 24, 2006
Messages
8

ADVERTISEMENT

Sample of desired result

Thank you Jindon. Here is a sample - I'm only concerned with ON-SITE.
2006Q4-SC-ALC-REPORT.xls
ABCDEFGHIJ
14.MULTI-USELEARNINGFACILITY(MLF)
2PURPOSEOFVISITCOSTS
3NUMBERON-SITEREMOTEMDEPVOPROTHERFUNDS
4OFEDUCATIONTRAININGEDUCATIONTRAINING
5VISITSACTIVITYACTIVITYACTIVITYACTIVITYEQUIP/SUPCONTRACTSEQUIP/SUPCONTRACTS
6(a)(b)(c)(d)(e)(f)(g)(h)(i)
7ARMY93728909n/an/aXXXX
8FAMILYMEMBERS202n/an/aXXXX
9CIVILIAN983464n/an/aXXXX
10RC/NG50601574903n/an/aXXXX
11OTHER1682481634n/an/aXXXX
12TOTAL7779267751200$0$0$0$0
Sheet1
 

Reverend

New Member
Joined
May 24, 2006
Messages
8
Tuning the approach

Joose,

Thank you. I understand how to approach making a list within the "Quarterly Report" workbook. I understand how to create a macro that will sort data. I'm not sure how to target that macro to work on seperate workbooks within a folder (the ALC SIGN-IN files). It seems like I will need a series of macros.
One to sort all the workbooks by USE then by UNIT.
A second to calculate the numbers of different types of USERS and their respective UNITS and pull this information to a NEW LIST in the "QUARTERLY REPORT" workbook (which will have a DATE field).

I've only just begun reading up and trying to learn about MACROS and I've only seen pivot tables referred to in the Excel help files, and have no idea yet on how to set them up.

As far as approaching this I guess the best way would be to break everything down into small simple steps to begin with and then later combine simplier steps together into more complex steps where practical.

Right now I can create the NEW LIST in the QUARTERLY REPORT workbook and create a macro that I can run (by opening each file manually) and running the macro using a shortcut key combination (that will sort data by USE then by UNIT).

For the calculations I'm thinking I'm going to need to get into some sort of nested IF: THEN: statements. If USER is EDUCATION THEN go to UNIT. If unit is NG/RG then +1 to the corresponding value in the NEWLIST on the QUARTERLY REPORT workbook ETC..ETC. Perhaps this is the role of the pivot tables. I'll start with making the NEWLIST. IF you know any tricks to run a macro on every .xcl file in a certain directory let me know.

-Jonathan

My first thought on the easiest way to do this would be to write a macro that would copy the information from each of the sign-in sheets and paste it to one big list within the "quartly report" workbook. Then you could use a pivot table to get all the different numbers you need. When copying the information you might also want to add a column for the date if you don't already have one. Does that sound reasonable? Do you have any idea how to approach that?
 

joose

Active Member
Joined
Mar 16, 2005
Messages
250
First you can build a 'collection' of the files you want a summary of. You can do this with the following:

Code:
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(StartPath).Files

The 'StartPath' term is the path for the folder containing the files you want to open.

Then use a 'For Each' loop to open each file in your colleciton. Such as:

Code:
For Each f1 In f
  -Here put code for opening each f1(file) and copying the information-
  -You don't need to sort or calculate anyting at this point-
Next

You can put the above code in another 'For Each' loop to run through each month folder you want a summary of.

I would recommend taking the information in each workbook and pasting it into a new workbook in one big list.
Once you get the information into one big list you can use a pivot table to calculate all the information you want. You won't have to write any complicated formulas or reference any other workbooks.

I hope this helps get you started. I will try to get some more complete code to illustrate what I mean. If you have no idea how to do what I explained above, just try to learn how to use pivot tables and I will post some more complete code when I have a chance to put it together.
 

Reverend

New Member
Joined
May 24, 2006
Messages
8
Thank you very much

Joose,

Thank you so much - for giving me something solid to work with. I'll google around some more and find out how to utilize the code you gave me and start learning about pivot tables. Having helpful people like you in this world makes me feel all warm and fuzzy...kinda like an ewok next to a campfire.

swi31davis6.jpg
 

Forum statistics

Threads
1,137,206
Messages
5,680,191
Members
419,887
Latest member
Vasokir

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
Top