Wildcard facilities

[stefburgas]

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

 

[Ron Coderre]

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]

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]

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]

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]

OK....we're getting closer. Thanks for posting more details.
I want to help, but I can't see any correlation between the upper section
and the lower section. Am I missing something? It would help if the same
references were in both sections so I could see how you want one section
translated into the other.

***********
Regards,
Ron

XL2002, WinXP

 

[ShaneDevenshire]

Hi,

I've looked over the sample data in your last post and I don't see and 6500
number and I don't understand what you mean by "my problem is that it could
be 5400
at one site but be 6500 at another and so on."

So without any clear understanding, something that seems to be hampering
other responders, I will contribute the following commants:

VLOOKUP supports *, ?, and ~ as wild cards. The problem you are probably
experiencing is that these work for text entries not numeric data. So if the
cell containing the lookup value is numeric and you attach * to it, for
example, *1234 or ="*"&B1 then the entry in the first column of the VLOOKUP
range must be text. If on the other hand the VLOOKUP lookup has numbers then
the wildcards won't work.

However, there is a workaround:

=VALUE(VLOOKUP(D1,TEXT(A7:B16,"@"),2,FALSE))

If D1 contains a wildcard entry like 1234*, and the range A7:B16 is you
lookup table with column A containing numeric entries, then the
TEXT(A7:B16,"@") function converts the entire range to text (within the
formula) and then does its wildcard lookup. The VALUE argument on the
outside is optional, you only need it if the data to be returned is numberic,
in which case this function converts the text back to a value. IMPORTANT:
you must enter this formula as an array - that is press Shift Ctrl Enter
rather than Enter to enter the formula.

 

[stefburgas]

Thanks Ron I hope you can help:

Maybe if I explain what I am trying to achieve it might help, my company has
over 140 sites throughout the UK however those sites use the same system but
have there own site numbers which can be split into split locations. When we
split the location we split the nominal accounts down so the main site will
be prefixed 0000 and the second location will be prefixed with it's site
number i.e 5400. The problem is that this data all comes out on one try
balance so I get

Column A Column B Column C Column D

Acct No Acct Desc Current Balance Previous
Balance

These details are exported from another system so they can't be changed, the
second worksheet is where we try to reconcile the balances to there accounts.

Column A Column B Column C
Column D Column E
Free keyed Acct No Vlookup to get Acct Vlookup taking Balance From
Sum working
from Trial Balance trial balance curr
Acct worksheet out the
Import balance
as each acct difference

has its own

worksheet

Within this worksheet I would like within Column A to have the main site
account number and then and addition field further down that has a wildcard
prefix and then the account number similar to what you can do in access.

Really hope I am starting to make some sense