Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sorting 2 colums of numbers and incremening them down | Excel Discussion (Misc queries) | |||
Macro question | Excel Worksheet Functions | |||
parsing data - formatting issue | Excel Discussion (Misc queries) | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
Sort pages? | Excel Discussion (Misc queries) |