data integration and consolidation. create folder at drive c: name of folders to be created:...
TRANSCRIPT
Data Integrationand
Consolidation
Create Folder at Drive C:
Name of folders to be created:• BMS-ALLOTMENT• BMS-UTILIZATION• BMS-PRIORYEARS• BMSAllData
C:\BMS-ALLOTMENTC:\BMS-UTILIZATIONC:\BMS-PRIORYEARSC:\BMSAllData
Exporting and keeping of Excel Files from each
Implementing Unit BMS
C:\BMS-PRIORYEARSC:\BMS-UTILIZATION
C:\BMSAllData
C:\BMS-ALLOTMENT
Step 1
Step 2
Step 3
Export data to Excel.
Step 4
Utilizations
Allotments
Prior Year’s
SAVE AS to C:\BMS-UTILIZATION folder.Filename: Name of Division/School
SAVE AS to C:\BMS-ALLOTMENT folder.Filename: Name of Division/School
SAVE AS to C:\BMS-PRIORYEARS folder.Filename: Name of Division/School
Exported Excel FilesSaved at C:\BMS-UTILIZATION folder
Names of implementing units
including the division office proper
Exported Excel FilesSaved at C:\BMS-ALLOTMENT folder
Names of implementing units
including the division office proper
Exported Excel FilesSaved at C:\BMS-PRIORYEARS folder
Names of implementing units
including the division office proper
Integration of Excel Files
C:\BMSAllData
Utilizations Allotments Prior Years
Three (3) Excel FilesIntegrator of all BMS excel files
Filenames:
Integrate data fromC:\BMS-UTILIZATION folder
1. Open this excel file: Integrator – BMS-UTILIZATION
Click this button.
Integrate data fromC:\BMS-UTILIZATION folder
Output:
Delete column A
Integrate data fromC:\BMS-UTILIZATION folder
Output:
Insert row at first row (A1)
If you noticed, there is no heading. The next step is to copy the heading from one of exported excel files (C:\
BMS-UTILIZATION folder).
Integrate data fromC:\BMS-UTILIZATION folder
Output:
Copy columns A1:T1 from one exported excel file and Paste here at A1
Format Column/Cells to Text Format
1. SelectColumn
SAVE TOC:\BMSAllData folder
Output:
Save As to C:\BMSAllData folder. Use exactly this filename: tempTable
Integrate data fromC:\BMS-ALLOTMENT folder
1. Open this excel file: Integrator – BMS-ALLOTMENT
Click this button.
Integrate data fromC:\BMS-ALLOTMENT folder
Output:
Delete column A
Integrate data fromC:\BMS-ALLOTMENT folder
Output:
Insert row at first row (A1)
If you noticed, there is no heading.The next step is to copy the heading from one of exported excel files (C:\
BMS-ALLOTMENT folder).
Integrate data fromC:\BMS-ALLOTMENT folder
Output:
Insert row at first row (A1)
Integrate data fromC:\BMS-ALLOTMENT folder
Output:
Copy columns A1:X1 from one exported excel file and Paste here at A1
Format Column/Cells to Text Format
1. SelectColumn
SAVE TOC:\BMSAllData folder
Output:
Save As to C:\BMSAllData folder.Use exactly this filename: tblAllRAODataAnnexA1
Integrate data fromC:\BMS-PRIORYEARS folder
1. Open this excel file: Integrator – BMS-PRIORYEARS
Click this button.
Integrate data fromC:\BMS-PRIORYEARS folder
Output:
Delete column A
Integrate data fromC:\BMS-PRIORYEARS folder
Output:The next step is to copy heading from
one of exported excel files from (C:\BMS-PRIORYEARS folder).
Insert row at first row (A1)
Integrate data fromC:\BMS-PRIORYEARS folder
Output:
Insert row at first row (A1)
Integrate data fromC:\BMS-PRIORYEARS folder
Output:
Paste heading here at A1
SAVE TOC:\BMSAllData folder
Output:
Save As to C:\BMSAllData folder.Use exactly this filename:
PriorYearObligation
Contents ofC:\BMSAllData folder
1. tblAllRAODataAnnexA12. tempTable3. PriorYearObligation
CONSOLIDATEDATA
Consolidate Data
1. Open this Access file: Consolidator Access File
Preview/Print Reports
Thank you!