Formatting Account Number to the right place

S

stroffso

Board Regular
Joined
Jul 12, 2016
Messages
160
Office Version
  1. 365
Platform
  1. Windows
Hi All,

I export a P&L file from MYOB, however all my account/job numbers appear in the same coulmn as the account headings. I was wondering is there any way of sticking in a formula that can place these account numbers beside the relevant account categories for that account.


So currently the column looks like below. What i need to do is have a formula that puts the relevant account number in a column beside the account name. Note that not all account numbers follow the ####-### convention, some have a letter after the 7 digits.

[TABLE="width: 180"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Account Name[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]2016-423[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]Income[/TD]
[/TR]
[TR]
[TD]Consulting Fees[/TD]
[/TR]
[TR]
[TD]Total Income[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]Cost Of Sales[/TD]
[/TR]
[TR]
[TD]Equipment Purchases[/TD]
[/TR]
[TR]
[TD]Laboratory Fees[/TD]
[/TR]
[TR]
[TD]Site Consumables[/TD]
[/TR]
[TR]
[TD]Travel Costs[/TD]
[/TR]
[TR]
[TD]Total Cost Of Sales[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]Expense[/TD]
[/TR]
[TR]
[TD]M / V Expenses - Fuel[/TD]
[/TR]
[TR]
[TD]Travel & Accommodation[/TD]
[/TR]
[TR]
[TD]Total Expense[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]Net Profit/(Loss)[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]2017-042[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]Income[/TD]
[/TR]
[TR]
[TD]Consulting Fees[/TD]
[/TR]
[TR]
[TD]COGS + Markup[/TD]
[/TR]
[TR]
[TD]Total Income[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]Net Profit/(Loss)[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]2016-016a[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]Income[/TD]
[/TR]
[TR]
[TD]Consulting Fees[/TD]
[/TR]
[TR]
[TD]Total Income[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]Cost Of Sales[/TD]
[/TR]
[TR]
[TD]Site Consumables[/TD]
[/TR]
[TR]
[TD]Total Cost Of Sales[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]Expense[/TD]
[/TR]
[TR]
[TD]Office Supplies[/TD]
[/TR]
[TR]
[TD]Total Expense[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]Net Profit/(Loss)[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]
And this is how I would like it to look:

[TABLE="width: 271"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Account Number[/TD]
[TD]Account Name[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]2016-423[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2016-423[/TD]
[TD]Income[/TD]
[/TR]
[TR]
[TD]2016-423[/TD]
[TD]Consulting Fees[/TD]
[/TR]
[TR]
[TD]2016-423[/TD]
[TD]Total Income[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2016-423[/TD]
[TD]Cost Of Sales[/TD]
[/TR]
[TR]
[TD]2016-423[/TD]
[TD]Equipment Purchases[/TD]
[/TR]
[TR]
[TD]2016-423[/TD]
[TD]Laboratory Fees[/TD]
[/TR]
[TR]
[TD]2016-423[/TD]
[TD]Site Consumables[/TD]
[/TR]
[TR]
[TD]2016-423[/TD]
[TD]Travel Costs[/TD]
[/TR]
[TR]
[TD]2016-423[/TD]
[TD]Total Cost Of Sales[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2016-423[/TD]
[TD]Expense[/TD]
[/TR]
[TR]
[TD]2016-423[/TD]
[TD]M / V Expenses - Fuel[/TD]
[/TR]
[TR]
[TD]2016-423[/TD]
[TD]Travel & Accommodation[/TD]
[/TR]
[TR]
[TD]2016-423[/TD]
[TD]Total Expense[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2016-423[/TD]
[TD]Net Profit/(Loss)[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]2017-042[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2017-042[/TD]
[TD]Income[/TD]
[/TR]
[TR]
[TD]2017-042[/TD]
[TD]Consulting Fees[/TD]
[/TR]
[TR]
[TD]2017-042[/TD]
[TD]COGS + Markup[/TD]
[/TR]
[TR]
[TD]2017-042[/TD]
[TD]Total Income[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2017-042[/TD]
[TD]Net Profit/(Loss)[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]2016-016a[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2016-016a[/TD]
[TD]Income[/TD]
[/TR]
[TR]
[TD]2016-016a[/TD]
[TD]Consulting Fees[/TD]
[/TR]
[TR]
[TD]2016-016a[/TD]
[TD]Total Income[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2016-016a[/TD]
[TD]Cost Of Sales[/TD]
[/TR]
[TR]
[TD]2016-016a[/TD]
[TD]Site Consumables[/TD]
[/TR]
[TR]
[TD]2016-016a[/TD]
[TD]Total Cost Of Sales[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2016-016a[/TD]
[TD]Expense[/TD]
[/TR]
[TR]
[TD]2016-016a[/TD]
[TD]Office Supplies[/TD]
[/TR]
[TR]
[TD]2016-016a[/TD]
[TD]Total Expense[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2016-016a[/TD]
[TD]Net Profit/(Loss)



[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
thanks in advance
 

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.
Sort by date Sort by votes
stroffso said:
Hi All,

I export a P&L file from MYOB, however all my account/job numbers appear in the same coulmn as the account headings. I was wondering is there any way of sticking in a formula that can place these account numbers beside the relevant account categories for that account.


So currently the column looks like below. What i need to do is have a formula that puts the relevant account number in a column beside the account name. Note that not all account numbers follow the ####-### convention, some have a letter after the 7 digits.

[TABLE="class: grid, width: 100%, align: left"]
<tbody>[TR]
[TD]Account Name
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]2016-423[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Income[/TD]
[/TR]
[TR]
[TD]Consulting Fees[/TD]
[/TR]
[TR]
[TD]Total Income[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Cost Of Sales[/TD]
[/TR]
[TR]
[TD]Equipment Purchases[/TD]
[/TR]
[TR]
[TD]Laboratory Fees[/TD]
[/TR]
[TR]
[TD]Site Consumables[/TD]
[/TR]
[TR]
[TD]Travel Costs[/TD]
[/TR]
[TR]
[TD]Total Cost Of Sales[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Expense[/TD]
[/TR]
[TR]
[TD]M / V Expenses - Fuel[/TD]
[/TR]
[TR]
[TD]Travel & Accommodation[/TD]
[/TR]
[TR]
[TD]Total Expense[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Net Profit/(Loss)[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]2017-042[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Income[/TD]
[/TR]
[TR]
[TD]Consulting Fees[/TD]
[/TR]
[TR]
[TD]COGS + Markup[/TD]
[/TR]
[TR]
[TD]Total Income[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Net Profit/(Loss)[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]2016-016a[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Income[/TD]
[/TR]
[TR]
[TD]Consulting Fees[/TD]
[/TR]
[TR]
[TD]Total Income[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Cost Of Sales[/TD]
[/TR]
[TR]
[TD]Site Consumables[/TD]
[/TR]
[TR]
[TD]Total Cost Of Sales[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Expense[/TD]
[/TR]
[TR]
[TD]Office Supplies[/TD]
[/TR]
[TR]
[TD]Total Expense[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Net Profit/(Loss)[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]And this is how I would like it to look: -
[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 271"]
<tbody>[TR="class: grid"]
[TD]Account Number
[/TD]
[TD]Account Name
[/TD]
[/TR]
[TR="class: grid"]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="class: grid"]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="class: grid"]
[TD][/TD]
[TD]2016-423[/TD]
[/TR]
[TR="class: grid"]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="class: grid"]
[TD]2016-423[/TD]
[TD]Income[/TD]
[/TR]
[TR="class: grid"]
[TD]2016-423[/TD]
[TD]Consulting Fees[/TD]
[/TR]
[TR="class: grid"]
[TD]2016-423[/TD]
[TD]Total Income[/TD]
[/TR]
[TR="class: grid"]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="class: grid"]
[TD]2016-423[/TD]
[TD]Cost Of Sales[/TD]
[/TR]
[TR="class: grid"]
[TD]2016-423[/TD]
[TD]Equipment Purchases[/TD]
[/TR]
[TR="class: grid"]
[TD]2016-423[/TD]
[TD]Laboratory Fees[/TD]
[/TR]
[TR="class: grid"]
[TD]2016-423[/TD]
[TD]Site Consumables[/TD]
[/TR]
[TR="class: grid"]
[TD]2016-423[/TD]
[TD]Travel Costs[/TD]
[/TR]
[TR="class: grid"]
[TD]2016-423[/TD]
[TD]Total Cost Of Sales[/TD]
[/TR]
[TR="class: grid"]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="class: grid"]
[TD]2016-423[/TD]
[TD]Expense[/TD]
[/TR]
[TR="class: grid"]
[TD]2016-423[/TD]
[TD]M / V Expenses - Fuel[/TD]
[/TR]
[TR="class: grid"]
[TD]2016-423[/TD]
[TD]Travel & Accommodation[/TD]
[/TR]
[TR="class: grid"]
[TD]2016-423[/TD]
[TD]Total Expense[/TD]
[/TR]
[TR="class: grid"]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="class: grid"]
[TD]2016-423[/TD]
[TD]Net Profit/(Loss)[/TD]
[/TR]
[TR="class: grid"]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="class: grid"]
[TD][/TD]
[TD]2017-042[/TD]
[/TR]
[TR="class: grid"]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="class: grid"]
[TD]2017-042[/TD]
[TD]Income[/TD]
[/TR]
[TR="class: grid"]
[TD]2017-042[/TD]
[TD]Consulting Fees[/TD]
[/TR]
[TR="class: grid"]
[TD]2017-042[/TD]
[TD]COGS + Markup[/TD]
[/TR]
[TR="class: grid"]
[TD]2017-042[/TD]
[TD]Total Income[/TD]
[/TR]
[TR="class: grid"]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="class: grid"]
[TD]2017-042[/TD]
[TD]Net Profit/(Loss)[/TD]
[/TR]
[TR="class: grid"]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="class: grid"]
[TD][/TD]
[TD]2016-016a[/TD]
[/TR]
[TR="class: grid"]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="class: grid"]
[TD]2016-016a[/TD]
[TD]Income[/TD]
[/TR]
[TR="class: grid"]
[TD]2016-016a[/TD]
[TD]Consulting Fees[/TD]
[/TR]
[TR="class: grid"]
[TD]2016-016a[/TD]
[TD]Total Income[/TD]
[/TR]
[TR="class: grid"]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="class: grid"]
[TD]2016-016a[/TD]
[TD]Cost Of Sales[/TD]
[/TR]
[TR="class: grid"]
[TD]2016-016a[/TD]
[TD]Site Consumables[/TD]
[/TR]
[TR="class: grid"]
[TD]2016-016a[/TD]
[TD]Total Cost Of Sales[/TD]
[/TR]
[TR="class: grid"]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="class: grid"]
[TD]2016-016a[/TD]
[TD]Expense[/TD]
[/TR]
[TR="class: grid"]
[TD]2016-016a[/TD]
[TD]Office Supplies[/TD]
[/TR]
[TR="class: grid"]
[TD]2016-016a[/TD]
[TD]Total Expense[/TD]
[/TR]
[TR="class: grid"]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="class: grid"]
[TD]2016-016a[/TD]
[TD]Net Profit/(Loss)

[/TD]
[/TR]
</tbody>[/TABLE]
thanks in advance
Click to expand...

I don't think that you need a formula, I believe you need VBA code that runs this for you. Do you know how to record macros and run them?
 
Upvote 0
Yes I do but what would i record here? The problem I have is the variation in row numbers between each Account
 
Upvote 0
You can use VBA but a second column and formulas is the best way to solve this problem. Here is what you can do:

1) After you import your data in column A, filter the data to delete all the blank cells for better presentation.
2) Copy/paste the column A to column B. Doing that columns A & B contain exactly the same data.
3) Replace A1 & B1 with your preferable header: A1= Account Number, B1= Account Name.
4) In cell A2 write this formula: =IF(MID(B2,5,1)="-",B2,A1) and copy/paste it to all other cells of column A. As I understand, the fifth character of the account number is "-", so that's why I use this formula.
5) Copy the column A and paste special only the values on the same column to remove the formulas.
6) Filter the data in column B by using: Text Filters > Contains... > -
7) Finally, clear the filter data from column A (better check cell by cell before you press Del) and remove the filter.

I believe this is your result you ask for.
 
Last edited:
Upvote 0
Was looking for an answer to this problem for a long time and stumbled across this. thanks so much Chris

ChrisGT7 said:
You can use VBA but a second column and formulas is the best way to solve this problem. Here is what you can do:

1) After you import your data in column A, filter the data to delete all the blank cells for better presentation.
2) Copy/paste the column A to column B. Doing that columns A & B contain exactly the same data.
3) Replace A1 & B1 with your preferable header: A1= Account Number, B1= Account Name.
4) In cell A2 write this formula: =IF(MID(B2,5,1)="-",B2,A1) and copy/paste it to all other cells of column A. As I understand, the fifth character of the account number is "-", so that's why I use this formula.
5) Copy the column A and paste special only the values on the same column to remove the formulas.
6) Filter the data in column B by using: Text Filters > Contains... > -
7) Finally, clear the filter data from column A (better check cell by cell before you press Del) and remove the filter.

I believe this is your result you ask for.
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

KiwiGrue
  • Solved
Selecting Dynamic Range in Excel
Replies
5
Views
582
Alex Blakenburg
A
S
  • Question Question
Circular iterations and using the max function.
Replies
0
Views
676
Satterfield
S
S
  • Question Question
Power Pivot Dax formula assistance required
Replies
0
Views
436
Sumesh
S
G
  • Question Question
Pivot Table Calculated Item Includes all accounts from Profit and Loss Statement
Replies
0
Views
942
Grimm127
G
P
  • Question Question
Valid data from other file (table) to P&L
Replies
1
Views
374
Panoos64
P

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

Share this page

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
Back
Top