Formatting Text for Drop Down-HELP!

[Guest]

Hi,

I have a list of budget categories and accounts and would like to format the
list to use as a drop down when categorizing actual expenses. I have the
list in cols a, b, and c in a worksheet formatted as a budget with cols a, b,
c, and columns for monthly budget amounts and a total column. I want to take
columns a, b, and c from this worksheet and format it to:
1, remove column a ( not needed for the drop down)
2. retain column b category headings
3. remove column b totals (not needed in drop down)
4. remove the blank lines but keep a blank line between expense categories
(in col b)
5. and lastly insert spaces in the account so that it offsets in the drop
down so it is easy to distinguish when categorizing an actual expense

Whew!

For example:
Col a Col b Col c
1
2 Income
3 Oper Inc
4 Acct 1
5 Acct 2
6 Acct 3
7
8 Total Oper Inc
9
10 Other Inc
11 Acct 4
12 Acct 5
13
14 Total Other Inc
15
16 Expense
17
18 Professional Fees
19 Acct 6
20 Acct 7
21 Acct 8
22
23 Total Prof Fees
24
25 Maint Fees
26 Acct 9
27 Acct 10
28 ... and so on

So what I'd like to end up with is:

Col a
1 Oper Inc
2 Acct 1
3 Acct 2
4 Acct 3
5
6 Other Inc
7 Acct 4
8 Acct 5
9
10 Professional Fees
11 Acct 6
12 Acct 7
13 Acct 8
14
15 Maint Fees
16 Acct 9
17 Acct 10
18 and so on...

After the reformatting is complete I plan to name col A and use it in a data
validation list in another worksheet.

I need help with the logic to do the reformatting of the text. If the range
could be named in the same code that would also be good.

 

[somethinglikeant]

Apologies for this being a little messy but it does do the trick!



Sub Reformat()
'1, remove column a ( not needed for the drop down)
Columns("A:A").Delete
'find last row
Range("B65536").End(xlUp).Select
qend = ActiveCell.Row
'2. retain column b category headings
'but delete rows for empty cells in A & B
'3. remove column b totals (not needed in drop down)
Cells(qend, 1).Select
Do Until ActiveCell.Row = 1
If IsEmpty(ActiveCell) And IsEmpty(ActiveCell.Offset(0, 1)) Then
ActiveCell.EntireRow.Delete
End If
If UCase(Left(ActiveCell, 5)) = "TOTAL" Then
ActiveCell.EntireRow.Delete
End If
ActiveCell.Offset(-1, 0).Select
Loop
ActiveCell.EntireRow.Delete
'4. remove the blank lines but keep a blank line between expense
categories
'(in col b)
'find last row
Range("B65536").End(xlUp).Select
qend = ActiveCell.Row
[B2].Select
Do Until ActiveCell.Row > qend
If IsEmpty(ActiveCell) Then
Selection.EntireRow.Insert
ActiveCell.Offset(1, 0).Select
End If
ActiveCell.Offset(1, 0).Select
Loop
'5. and lastly insert spaces in the account so that it offsets in the
drop
'down so it is easy to distinguish when categorizing an actual expense
'find last row
Range("B65536").End(xlUp).Select
qend = ActiveCell.Row
[a1].Select
Do Until ActiveCell.Row > qend
ActiveCell.Offset(0, 1).Value = " " & ActiveCell.Offset(0,
1).Value
If IsEmpty(ActiveCell) Then ActiveCell.Value = ActiveCell.Offset(0,
1).Value
ActiveCell.Offset(0, 1).Value = ""
ActiveCell.Offset(1, 0).Select
Loop

qend = ActiveCell.Offset(-1, 0).Row
Range("A1:A" & qend).Select
End Sub

 

[Guest]

Wow! This works! Somethinglikeant, thanks so much for the help! Two things
that would be nice to fix:
1. For some reason in the last group of accounts, Reserve Income, Deposits,
and Transfers and Reserve Expenses there is no space.
2. The last row in col b, Total Reserve Expenses, is not deleted.

I attempted to make a few adjustments but messed it up. I ok with formulas
but out of my league with VB. Any thoughts? The following is the complete
list of categories and accounts in cols a, b, and c and the text after
reformatting:

Complete list of categories and accoounts in cols a, b, c:

Col a Col b Col c
Income

Operating Income
Dues ($250/unit/month)
Interest Income
Late Fees
Other Operating Income

Total Operating Income

Other Income
Bank Late/NSF Charge
Special Assessment
Reserve Interest
Other Misc. Income

Total Other Income

Total Income

Operating Expenses

Professional Fees
Accounting
Consulting Fees
Legal
Management Fees
Reserve Study
Other Professional Fees

Total Professional Fees

Office Supplies
Copying
Office Equipment Rental/Purchase
Office Supplies
Postage
Printing
Other Office Supplies Expenses

Total Office Supplies

Administration Expenses
Answering Service/Paging
Bank Service Charges
Check Printing
Entertainment
Payment Coupons
Payroll Processing
Resident Parties
Telephone
Travel
Other Administration Expenses

Total Administration Expenses

Tax and License Expenses
Federal and State Forms
Federal Tax Preparation
Fire Hydrant Inspection
State Tax Preparation
Other Tax and License Expense

Total Tax and License Expenses

Insurance Expense
Earthquake Insurance
Fidelity Bond
Fire and Liability Insurance
Workers Comp Insurance
Other Insurance Expense

Total Insurance Expense

Utilities Expense
Utilities - Electric
Utilities - Garbage
Utilities - Sewer
Utilities - Water
Other Utilities Expense

Total Utilities Expense

Maintenance Expense
Asphalt and Concrete
Buildings
Common Area
Electrical/Lighting
Fence
Fire Alarms
Gardening
Gutters
Landscaping
Pest Control
Plumbing
Roof
Termite Inspection/Control
Tree Trimming
Other Maintenance Expense

Total Maintenance Expense

Total Operating Expenses

Net Operating Income/(Loss)

Reserves

Reserve Income, Deposits, and Transfers
Reserve Deposits
Reserve Interest
Reserve Transfers
Special Assessments

Total Reserve Income, Deposits, and Transfers

Reserve Expenses
Asphalt and Concrete
Building Siding
Electrical
Exterior Painting
Fencing
Grounds
Roof
Termite Repair

Total Reserve Expenses

Net Reserve Income/(Loss)

Net Income (Loss)

Text after reformatting:

Col a

Operating Income
Dues ($250/unit/month)
Interest Income
Late Fees
Other Operating Income

Other Income
Bank Late/NSF Charge
Special Assessment
Reserve Interest
Other Misc. Income

Professional Fees
Accounting
Consulting Fees
Legal
Management Fees
Reserve Study
Other Professional Fees

Office Supplies
Copying
Office Equipment Rental/Purchase
Office Supplies
Postage
Printing
Other Office Supplies Expenses

Administration Expenses
Answering Service/Paging
Bank Service Charges
Check Printing
Entertainment
Payment Coupons
Payroll Processing
Resident Parties
Telephone
Travel
Other Administration Expenses

Tax and License Expenses
Federal and State Forms
Federal Tax Preparation
Fire Hydrant Inspection
State Tax Preparation
Other Tax and License Expense

Insurance Expense
Earthquake Insurance
Fidelity Bond
Fire and Liability Insurance
Workers Comp Insurance
Other Insurance Expense

Utilities Expense
Utilities - Electric
Utilities - Garbage
Utilities - Sewer
Utilities - Water
Other Utilities Expense

Maintenance Expense
Asphalt and Concrete
Buildings
Common Area
Electrical/Lighting
Fence
Fire Alarms
Gardening
Gutters
Landscaping
Pest Control
Plumbing
Roof
Termite Inspection/Control
Tree Trimming
Other Maintenance Expense

Reserve Income, Deposits, and Transfers
Reserve Deposits
Reserve Interest
Reserve Transfers
Special Assessments
Reserve Expenses
Asphalt and Concrete
Building Siding
Electrical
Exterior Painting
Fencing
Grounds
Roof
Termite Repair

Total Reserve Expenses

Apologies for this being a little messy but it does do the trick!



Sub Reformat()
'1, remove column a ( not needed for the drop down)
Columns("A:A").Delete
'find last row
Range("B65536").End(xlUp).Select
qend = ActiveCell.Row
'2. retain column b category headings
'but delete rows for empty cells in A & B
'3. remove column b totals (not needed in drop down)
Cells(qend, 1).Select
Do Until ActiveCell.Row = 1
If IsEmpty(ActiveCell) And IsEmpty(ActiveCell.Offset(0, 1)) Then
ActiveCell.EntireRow.Delete
End If
If UCase(Left(ActiveCell, 5)) = "TOTAL" Then
ActiveCell.EntireRow.Delete
End If
ActiveCell.Offset(-1, 0).Select
Loop
ActiveCell.EntireRow.Delete
'4. remove the blank lines but keep a blank line between expense
categories
'(in col b)
'find last row
Range("B65536").End(xlUp).Select
qend = ActiveCell.Row
[B2].Select
Do Until ActiveCell.Row > qend
If IsEmpty(ActiveCell) Then
Selection.EntireRow.Insert
ActiveCell.Offset(1, 0).Select
End If
ActiveCell.Offset(1, 0).Select
Loop
'5. and lastly insert spaces in the account so that it offsets in the
drop
'down so it is easy to distinguish when categorizing an actual expense
'find last row
Range("B65536").End(xlUp).Select
qend = ActiveCell.Row
[a1].Select
Do Until ActiveCell.Row > qend
ActiveCell.Offset(0, 1).Value = " " & ActiveCell.Offset(0,
1).Value
If IsEmpty(ActiveCell) Then ActiveCell.Value = ActiveCell.Offset(0,
1).Value
ActiveCell.Offset(0, 1).Value = ""
ActiveCell.Offset(1, 0).Select
Loop

qend = ActiveCell.Offset(-1, 0).Row
Range("A1:A" & qend).Select
End Sub

 

[somethinglikeant]

Glad it is working to some extent,

I assumed incorrectly that the cell furthest down the sheet would be in
column B after A is deleted

where you see

Range("B65536").End(xlUp).Select

you need to change it to

Range("A65536").End(xlUp).Select

This will ensure that all the loops work all the way down and should
fix those two issues hopefully.
Let me know if you have any other niggly issues.

Ant

 

[Guest]

Your suggestion worked with one small change. In the last loop the range
should be b65536. By the tiem it gets there column b has the last row of
data. I also removed the loop to insert a row between categories as the drop
down looks OK without spaces.

One small consequence of indenting the accounts in the list used to
categorize payments in the drop down is that when you choose from the drop
down list (from the reformatted data) in the sheet where expenses are
entered, the entries also have the spaces. I was having a formula sum these
on an income statement when they equaled the category from the budget. So
when the budget category is "Dues" and the expense categoty is " Dues" it
did not match. Had to use trim in the formula in the income statement.
Other than that all is well. Thanks for your help!