RePost: Can someone tell me if what I want to do is possible with functions? ...please??

[Shaun Sheffield]

Howdy!

I need to sort finacial bookings into cost categories based on three
criteria.

"Key" table to sort the bookings by:
Column A - Account number - 2-8 digit number that is formatted as text
Column B - Type of business - a single letter
Column C - Partner Code - a 1-4 digit number that is formatted as text
Column D - is then the Cost Category


"Key" table
A B C D
10 Fees
11 Commissions
12 C 4 Travel Expenses
12 C 5 Gifts
136 Services
1369 A 4 PC's
1369 A 5 Notebooks
1369 B 2 Server
1370 A Network Services
1375 A 6 IT Consultation

My bookings contain:
Column A - Account number - 8 Digit account number formatted as text
Column B - Type of business - a single letter
Column C - Partner Code - 4 digit number that is formatted as text
Column D - Formula (that I'm having trouble conceptualizing) that assigns
the booking to a cost type. My end goal is to use this table as the basis
for a Pivot Table for the purpose of cost analysis.


The rule that I want to apply is generally simple:
The formula should pick the right category from Column D based on "best
match in each column" (always sorted descending order top to bottom).

Examples:
a booking with account numbers between 10100000-10999999 will be consigned
as Fees no matter what the business type or partner code.
Account 13688537 will be consigned to Services
13690000 could be either PC's, Notebook oder Servers, depending on B+C.

I have been trying to nest VLookup with IF and OR, but I can't picture the
descision steps, then I just end up getting so confused that I don't know if
I'm on the right track.I've looked through Google but I don't seem to be
searching for the right things.

Can any one:
Tell me if I'm on the right track trying to nest the VLookup commands? or
Give me an idea in which direction I need to go?

Thanks,

Shaun Sheffield

 

[Max]

Try this set-up:

In Sheet2:
------------
Assume your "Key" table mentioned
is in Sheet2, in A110

Insert a new col to the left of col A

In this "new" col A
put in A1: =TRIM(B1&"_"&C1&"_"&D1)
copy down to A10

Name the range A1:E10 as : KeyTable

In Sheet1 (where your bookings are)
--------------------------------------------
you have your cols A - C as mentioned, in row2 down (say)

Put in D2:
=IF(VALUE(LEFT(A2,3))<110,"Fees",IF(VALUE(LEFT(A2,4))<1369,"Services",VLOOKU
P(TRIM(LEFT(A2,4)&"_"&B2&"_"&C2),KeyTable,5)))

copy D2 down col D

Col D should give the returns satisfying your given criteria
(if I've read it correctly)

--
hth
Max
-----------------------------------------
Please reply in newsgroup

Use xdemechanik
<at>yahoo<dot>com for email
--------------------------------------------------

 

[Max]

May be better to use FALSE as the 4th param in the vlookup for col D, so
please replace the line:

Put in D2:
=IF(VALUE(LEFT(A2,3))<110,"Fees",IF(VALUE(LEFT(A2,4))<1369,"Services",VLOOKU
P(TRIM(LEFT(A2,4)&"_"&B2&"_"&C2),KeyTable,5)))

by

Put in D2:
=IF(VALUE(LEFT(A2,3))<110,"Fees",IF(VALUE(LEFT(A2,4))<1369,"Services",VLOOKU
P(TRIM(LEFT(A2,4)&"_"&B2&"_"&C2),KeyTable,5,FALSE)))

--
hth
Max
-----------------------------------------
Please reply in newsgroup

Use xdemechanik
<at>yahoo<dot>com for email
------------------------------------------

 

[Max]

Apologies, disregard the earlier post, think I missed out
the Commissions, Travel Expenses & Gifts parts in your "Key" table

Replace the formula in D2 with:

:
=IF(VALUE(LEFT(A4,3))<110,"Fees",IF(VALUE(LEFT(A4,3))<120,"Commissions",IF(V
ALUE(LEFT(A4,3))<130,VLOOKUP(TRIM(LEFT(A4,2)&"_"&B4&"_"&C4),KeyTable,5,FALSE
),IF(VALUE(LEFT(A4,4))<1369,"Services",VLOOKUP(TRIM(LEFT(A4,4)&"_"&B4&"_"&C4
),KeyTable,5,FALSE)))))

--
hth
Max
-----------------------------------------
Please reply in newsgroup

Use xdemechanik
<at>yahoo<dot>com for email
--------------------------------------------------

 

[Max]

Sheesh... pasted the wrong row's formula for D2 (row4 instead of row2),
it should read:

Replace the formula in D2 with:

=IF(VALUE(LEFT(A2,3))<110,"Fees",IF(VALUE(LEFT(A2,3))<120,"Commissions",IF(V
ALUE(LEFT(A2,3))<130,VLOOKUP(TRIM(LEFT(A2,2)&"_"&B2&"_"&C2),KeyTable,5,FALSE
),IF(VALUE(LEFT(A2,4))<1369,"Services",VLOOKUP(TRIM(LEFT(A2,4)&"_"&B2&"_"&C2
),KeyTable,5,FALSE)))))

--
hth
Max
-----------------------------------------
Please reply in newsgroup

Use xdemechanik
<at>yahoo<dot>com for email
-----------------------------------------

 

[Shaun Sheffield]

Thanks for your help, however, I should have mentioned that I had massively
simpplified the key table in order to illustrate my problem for the news
group. I have 850 different keys sorting into183 different cost types in the
key table.

I wanted to avoid a solution in which I used CONCATENATE (or, as I have just
learned from you, Max, TRIM) for two reasons. First, I didn't want to "hard
wire" the keys into the formulas as there are so many. Secondly, and most
importantly, our Accounting and Controlling department publishes the key at
least 4 times a year. I wanted to be able to simply copy in the new key,
without having to rewrite my formulas.

 

[Max]

Ok, Shaun.

Perhaps others would have alternative views to offer.

Btw, TRIM is used in the VLOOKUP
to make the lookup/matching more robust
just in case there are inadvertent (& invisible) spaces in the
look-up value cells / values in the 1st col of the vlookup array

Good luck!

Max

 

[Debra Dalgleish]

You could create lookup codes in the each table, to return the correct
cost category.

In each table, insert a column to the left of the Cost Category column.
Add a heading, e.g. LookupCode
In the Key table, with your key codes starting in row 2, enter the
following formula in D2:

=A2&REPT("0",4-LEN(A2))&"-"&IF(B2=""," ",B2)&"-"&TEXT(C2,"000")
and copy down to the last row of data

In the Bookings table, with bookings starting in row 2, enter the
following formula in D2:

=TEXT(LEFT(A2,4),"0000")&"-"&IF(B2=""," ",B2)&"-"&TEXT(C2,"000")
and copy down to the last row of data

In E2 (Cost Category), enter the following formula:

=VLOOKUP(D2,KeyTable!$D$2:$E$11,2)

substituting your KeyTable sheet name and range, and copy down to the
last row of data

 

[Shaun Sheffield]

WoW! Thanks, Max! Thanks, Debra! Thanks a million! I konw that I've spent 20
hours on this problem (10 at work and 10 at home), then with you tip, I
solved the problem in 10 minutes! With this tool, I'm going to be able to
tell my boss where we're going to land 4 *days* before the trolls from the
IT department can turn the crank on the database, and be able to be able to
pinpoint exact expenses that are out of line (with a list of the
evil-doers). <evil grin>

By the way, I've had your website bookmarked for a long time, I've dug up
real gems there!

 

[Debra Dalgleish]

Max, you're welcome. Thanks for reporting that you solved the problem.

 

[Max]

Shaun, you mean... Debra <g>

--
hth
Max
-----------------------------------------
Please reply in newsgroup

Use xdemechanik
<at>yahoo<dot>com for email
--------------------------------------------------

 

[Peo Sjoblom]

Max?

--

Regards,

Peo Sjoblom

Max, you're welcome. Thanks for reporting that you solved the problem.

 

[Gord Dibben]

Who's on first?

 

[Debra Dalgleish]

Oops! Sorry Shaun and Max.

(Would you believe that 'Max' is the Canadian spelling of 'Shaun'?)

 

[Max]

Ahh, that figures, then.. <g>

cheers
Max
-----------------------------------------
Please reply in newsgroup

Use xdemechanik
<at>yahoo<dot>com for email
--------------------------------------------------