Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

mapping data old accounting

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Mapping data from an old accounting system into a new system

Mapping data from an old accounting system into a new system

ResolvedVersion 2007

Mark has attended:
Excel Advanced course

Mapping data from an old accounting system into a new system

Anthony,

As discussed, I have data by account code and cost centre in an old accounts system. I've generated a mapping document to the new account codes and cost centres in the new system.
Please can you confirm how the data should get mapped into the new system?

thank you

Mark

RE: Mapping data from an old accounting system into a new system

Hi Mark, I think I have a solution for you. However, you only gave me the corresponding Oracle Acc and CC codes for these Sun codes:

10000 FIN
20000 OPS
30000 ITS

So the rest will need to be included for this to work on all your data.

First, create a second table. Column 1 will combine the Sun Account Code and cost centre using concatenation (i.e =A2 & " " & B2), second and third columns contain the corresponding Oracle codes.

Sun Combined Oracle Account Code Oracle Cost Centre
10000 FIN 100 10
20000 OPS 200 20
30000 ITS 300 30

Once you have this table, you can use it as the table array for a VLookup. Return to your original data table (with the Sun Account code and Sun Cost codes and empty Oracle columns) and use Vlookups to populate the Oracle columns. Your lookup value should be concatenate the Sun codes and your table array should be the table we created above:

=VLOOKUP(A2& " " & B2,Oracle!$A$7:$C$9,2, FALSE)

Remember to alter the column index value to bring in the Oracle information into the right column.

This worked on the spreadsheet you left me, but have a play with the formulae and let me know how you get on. The key is creating that first table with the combined Sun values and then using them in a Vlookup to bring in the Oracle data. Good luck!

All the best,

Anthony

Sun 13 Dec 2009: Automatically marked as resolved.

Excel tip:

Remove unused toolbar buttons

Are there buttons on your Excel toolbars that you never use? Remove them from the toolbar by doing the following:

1. Go to Tools - Customise - Commands.
2. Select the toolbar button you wish to remove, then use your mouse to drag and drop the button into the Excel window. When you release your finger from the mouse, the button will disappear.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.1 secs.