Formatting Data Lists
 

When I import data in cloumn A from my accounts package it is not lined up
against Column B which is previouly imported(expense code 102 is missing).
How can I line the rows up, as I need to subtract column A from column B
figures ?
A B
101 Wages 101 Wages
103 Adverts 102Training
104 Motor Expenses 103 Adverts
105 Light & Heat 104 Motor Expenses

Formatting Data Lists
 

Hi Nige

I am assuming that there are 2 columns in each case, column A with the
Nominal code No. and Heading (101 Wages) and Column B with the value for
that code for that month.

You will need a sheet which has the complete set of Nominal code with no
gaps, lets call this sheet Master.
Lets call last month's data sheet 1, this month's data sheet2

On Master in cell B1 enter
=IF(A1="","",IF(ISNA(VLOOKUP(A1,Sheet1!A:B,2,0),
"",VLOOKUP(A1,Sheet1!A:B,2,0)))
copy down as far as required

In cell C1 repeat the formula, but change the sheet name
=IF(A1="","",IF(ISNA(VLOOKUP(A1,Sheet2!A:B,2,0),
"",VLOOKUP(A1,Sheet2!A:B,2,0)))

If you want to "fix" the data on Master, so it no longer needs to
reference another sheet, copy column B (for example) the Paste
SpecialValues back over the formulae to turn them into straight data
values

--
Regards

Roger Govier


"nigerobinson" wrote in message
...
When I import data in cloumn A from my accounts package it is not
lined up
against Column B which is previouly imported(expense code 102 is
missing).
How can I line the rows up, as I need to subtract column A from column
B
figures ?
A B
101 Wages 101 Wages
103 Adverts 102Training
104 Motor Expenses 103 Adverts
105 Light & Heat 104 Motor Expenses