Using Table to convert numbers to a Positive or Negative Amount

[brandonmcg]

I have a spreadsheet with three tabs.

Tab A Final I need the Loan number and the converted amount to go here.
Tab B Table See Below
Tab C Where I paste data that needs to be converted. There is a column for a loan number and a positive or negative amount
Loan Number Amount
12345 123.56
456788 -569.78

What I need to do is get the pasted data from Tab C to Tab A while applying the rules in Tab B.

Here is the table with some examples at the bottom.

The goal is using the last number of the positive or negative number and replace the last number with the Replacement character from the table.

Kind of goofy I know but that is what I need help with.

Making any sense?

 

[brandonmcg]

I am sorry this is such a mess. I will close the ticket because I just got more information how we want to apply these rules. Thank you for trying to help. Take care.

This is for loans on our system that have the wrong due date.
I need a formula to adjust due dates using the rules below and the attached matrix.

I will use a spreadsheet with loan numbers, number of months (+/-) the due date is off and the loans current due date.
I will paste the data on one tab and then need to have the loan number, adjusted number using the matrix and new date on another tab There can be a third tab with the matrix.

Loan # Number of Months Due Date Difference Current Due Date which will be different for each loan
1234567890 -108 06/01/22
2345678901 -096 01/01/22

Using the new data I need to see on the new tab
Loan # Adjusted using the matrix New Due Date
1234567890 10Q 06/01/13
2345678901 09O 01/01/14

What the guide says
(+/-) DUE DATE ADJ The number of periods to increase or decrease the due date. The system adds the number to the existing next payment due date.
To change the due date to an earlier date, type two digits and a negative replacement character in the last position. Refer to the "Positive and Negative Values" topic for additional information.

Example: The date is 12/01/18 and the loan's period is 12 (monthly). You want the new payment due date of 10/01/18, which is two months earlier (002). Type 00 followed by the replacement character for 2.

Positive and Negative Values
To specify a positive amount or quantity, type the actual number or a positive replacement character in the field's last entry position.
To specify a negative amount or quantity, type a negative replacement character in the field's last entry position.
The system rejects free-form transactions that include minus (-) signs.

See the attached matrix

View attachment 785019


Final Positive Negative
Digit of Replacement Replacement
Number Character Character
1 A J
2 B K
3 C L
4 D M
5 E N
6 F O
7 G P
8 H Q
9 I R
0 { }

 

[brandonmcg]

The formula you provided works perfectly, your help is very much appreciated. This will make our lives much easier and our information more accurate on our system.

 

[Peter_SSs]

You're welcome. Glad we could help.