1. Add a new tab and name it RawData
2. Copy A12 à last row/column of data (or 1st cell containing an account string to the bottom right-hand corner of the last row contining a complete account string like: 101-000-401.000)
3. Paste onto RawData starting at A2
4. Insert 12 columns to the left of column A
5. Enter these column headers: LocalUnitName, FiscalYear, ImpMunID, ImpAndID, ImpFutID, ImpFtsID, ImpLfcID, ImpSscID, ImpFundCode, ImpFunctionCode, ImpSection, ImpObjectCode, ImpAccountString, ImpDescription
6. The column with data to load as ImpAmount
7. Name the final column (Q?): CalcSection
8. Populate Column A with the local unit’s Name
9. Populate Column B with the local unit’s Fiscal Year being imported (like 2016)
10. Populate Column C with the local unit’s MunID
11. Populate Column D with the AndID
12. Populate CalcSection (P?): =RIGHT(LEFT(M2,9),1). Typically, Expenses will be 7, 8 & 9. Revenues will be 4, 5 & 6. SOP will be 1 – 4.
13. Populate Column K with Revenues, Expense or SOP depending on the line item by filtering on the column: CalcSection. This is a very important step and quite often the account string does not identify Revenues from Expense or SOP.
14. Populate column I with =LEFT(M2,3) this is the ImpFundCode column.
15. NOTE: the following formulas may change based on the account string for each local unit.
16. Filter the entire sheet with Column K = Expense only
17. Populate Column J (ImpFunctionCode) with =RIGHT(LEFT(M2,7),3) for Expense only (L2 is only a sample it represents the 1st cell for expense)
18. Populate Column L (ImpObjectCode) with =RIGHT(LEFT(M2,11),3).
19. Populate Column H (ImpSscID) with 1 for Expense Only (this is Not Identified)
20. Filter the entire sheet with Column K = Revenues only
21. Populate Column H (ImpSscID) with 115 for Revenues Only (this is Miscellaneous Revenue)
22. Populate Column J (ImpFunctionCode) with =RIGHT(LEFT(M2,11),3) for Revenues only (M2 is only a sample it represents the 1st cell for revenues)
23. Filter the entire sheet with Column K = SOP only
24. Populate Column J with =RIGHT(LEFT(M2,11),3) for SOP only (L2 is only a sample it represents the 1st cell for SOP)
25. Populate Column E (ImpFutID) with 4 (this is General Fund)
26. Populate Column F (ImpFtsID) with 105 (this is General Fund)
27. Populate Column H (ImpSscID) with 439 for SOP Only (this isOther Assets)
28. Using Column M (Account String) as a filter, delete any rows that do not have a valid account number