150 Parts Management Stock Value 218872.81 -218872.81 0.00
160 Parts Mgt Stock Value 12 Mth 12679.72 -12679.72 0.00
80008600 Headcount Contra -136 136 0.00
5400000150 Parts Management Stock Value 0 0 0.00

second worksheet

Code Desc Trial Balance Schedule Diff

9330 Prepayments 149,795.78 0.00 -149,795.78
9572 Accruals Miscellaneous -88,310.00 0.00 88,310.00
9570 Accrued Managers Bonus 0.00 0.00 0.00
9571 Accrued Customer Care 0.00 0.00 0.00

at the moment I am comparing the normal accounts fine but I want to build
the addition cost centres in of 5400 etc, my problem is that it could be 5400
at one site but be 6500 at another and so on.

Hope this helps - I can't split the trial balance spreadsheet down as it is
taken from another system and a serious of macro's are setup to run of the
trial balance.






"Ron Coderre" wrote:

We're still a bit short on details, but
maybe you could try something like this:

Split the account number field in to 2 fields
(either with Text-to-Columns or using formulas)
The first field would be the Account
The second fields would be the CCtr

Example:
50001234
50009876

would become
5000 1234
5000 9876

Then you could create a Pivot Table that would automatically group CCtrs by
Account and display totals. You'd also get the flexibility to show/hide items
for analysis.

Is that something you can work with?

Post back with more questions (and details).
***********
Regards,
Ron

XL2002, WinXP


"stefburgas" wrote:

Thanks, my problem is though that all the accounts appear in the same column
of my first worksheet and I want to do a breakdown analysis on another page
split by the cost centre.

At the moment I have it working for the accounts where there is no cost
centre prefix

9572 Accruals Miscellaneous the next column has a formula reviewing the
firstsheet to compare what is in column a to the trial balance.

"Ron Coderre" wrote:

We may need some more examples of your data, but see if this gets you headed
in the right direction...

With
Sheet2...
H2:H15 containing accounts
I2:I15 containing numbers

If the account references are TEXT,
then this may work:
=SUMIF(Sheet2!H2:H15,"*9330",Sheet2!I2:I15)

However, if the account references are numeric,
you may need something like this:
=SUMPRODUCT((RIGHT(Sheet2!H2:H15,4)="9330")*Sheet2 !I2:I15)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"stefburgas" wrote:

Is it possible in excel to use a wildcard search function, I have looked in
the help file and it mentions using * but this doesn't seem to do what I
want.

I am trying to vlookup a column against another worksheet where i have
account numbers for two cost centres, i.e 9330 and 54009330 so I want to
determine the balances by account i.e have two lines on another spreadsheet
one for 9330 and one for the second centre. However the cost centre part of
the account will change but the account number 9330 will remain the same.

Is this possible if so how