add and subtract multiple text strings.

[Guest]

The following are three columns in a reverse billing spread sheet. Can I
automate the tast of adding or subtracting codes from the codes column based
on the code deduction and code additions columns? The result of the first row
would be :
1L - 5C - - - - - - I would then assign a fixed dollar amount to each code
(a 1L= $20 and a 5C = $10 for a total value of that cell being $30) I don't
know if this is possible but i'm pretty sure I might be able to do it with a
list function. I also worry a little about size as In any given week I could
have 800 or more line items. I have a similar sheet for another site and one
file is about 5MB which works well.

CODES code deductions code
additions
2A - 1L - - - - - - 2A - - - - - - -
5C - - - - - - -
1L - 5C - - - - - -
5X - 2K - - - - - - 2K - - - - - - -
1A - 2A - - - - - -
1E - 2A - - - - - -
5X - - - - - - -
5X - 1L - - - - - - 1L - - - - - - -
1I - - - - - - -
3C - 1E - - - - - -
1A - 1E - - - - - -
3D - 2H - - - - - -
1I - - - - - - -
1E - 5B - 5X - - - - -
3C - 1E - - - - - -
1I - - - - - - -
1E - 2H - - - - - -
1L - 5B - - - - - -
3C - - - - - - -
3D - 5X - - - - - -
3C - 2H - 5B - - - - -
1A - 2B - 5B - 1E - - - -
4K - - - - - - -
1A - 1E - 2D - 5X - - - -
1A - 1E - 2L - - - - -
3C - 5X - 2J - - - - -
3D - 1E - - - - - -
6A - - - - - - -
- - - - - - -

 

[robert111]

you need a lookup table EXAMPLE

1c 20
2c 30
3c 40

=vlookup(2c,table,2)+vlookup(3c,table,2)

returns 70

if you want to code the 70 u need another lookup table2

60 6c
70 7c
80 8c

=vlookup(vlookup(2c,table,2)+vlookup(3c,table,2),table2,2)

returns 7c