S
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
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