Assign an acronym of the relevant plant in a separated column

Dear forum users and bloggers,

I would be really greatful if you could help me with the following matter.

I need to assign the "freight payer" which is either the sending or in some times the receiving plant, e.g. OT for Ontario etc.

I have two columns "sender" and "receiver" and did this so long by inserting a new column, first filtering all senders (the company has 10 plants) and then filling the new column. E.g. if I saw Ontario, I entered OT . After I finished the "senders", I checked the "receivers", and if there were some receiving plants (normally, the receivers are customers), I entered the acronyms for the receivers in the new column, and if there already has been any plant, I overrid it.  This all, because I wanted to sum up the freight costs paid by the relevant plant.

If there any VBA function that could help me insert the right acronym by checking the two columns at once?

See example below for explanations

Sender  Receiver     Freight    Toll          Who pays the freight

Ontario  Customer1  123           3             OT

Ontario   Toronto       144         5              TO

Montreal Customer2   155          2            MO

Montreal Ontario         44          4             OT

Thank you very much in advance,

Best regards,

Ksénia

Ksénia,

I don't think you need a code solution for this, but let me make sure that I understand the situation.  If a plant is the reciever, they pay the freight, otherwise the sending plant pays the freight, is that correct?

If so, you can build an acronym table (say on another worksheet) that provides the acronym for each plant.

Then you can use an IF and a couple of VLookup formulas in the 'Who pays the freight' column to find the acronym. The below formula assumes your table starts in Cell A1.  I'm placing the formula in Cell E2.  You'll also want to replace 'AcronymTable' with the address of your lookup table.

=IF(ISNA(VLOOKUP(B2,AcronymTable,2,FALSE),VLOOKUP(A2,AcronymTable,2,FALSE),VLOOKUP(B2,AcronymTable,2,FALSE))

What this formula does is to see if it can find the customer name in your Acronym Table.  As that table only contains plants, it will return an NA error for your customers.  When it returns that error, the formula looks to the A column instead.

Hope that makes sense.

David B.

Dear David,

thank you very much. I thought that there was a way to use just an IF formula, but I was afraid I would mix something up or just become confused because of that much IF's :-).

I will try your solution, thank you! Hope I don't make any mistakes :-)

Best regards,

Ksénia

Dear David,

I tried this formula and it works, but not always... it does not consider, that if the unloading point (unload. pt.) is a plant, too, than it should pay the freight.... here a small example: (see below). Do you know, if it is possible to modify your formula by addind an additional IF inside? or an AND? Thank you soooo much - you really helped me and facilitated my work :-) bye bye best regards, ksénia

Where does load and unload pt figure in the previous table? The previous formula returned TO when the receiver was in the lookup table.

 Ksénia,

i'm not entirely sure why it's not working.  The formula looks first at the Unload point or Receiver (assuming your first table started in A1) and looks for that value in the table.  If the value is not found, it assumes the Load Point or Sender column should be used and looks up that value in the table.

In your posted example, the formula would be placed in Cell B2 and would be altered to "=IF(ISNA(VLOOKUP(C2,AcronymTable,2,FALSE),VLOOKUP(A2,AcronymTable,2,FALSE),VLOOKUP(C2,AcronymTable,2,FALSE))

The way that works is:  If the Vlookup for the unload pt. returns NA, it executes the vlookup on the Load Pt.  Otherwise it returns the code from the table for the unload pt.

Sorry for the delay in responding.  Pulling 12 hour work days lately and I've not had much time for anything extracurricular.

David B.

Dear dear dear David!!!!!

Thank you soooooo much!!!! I only had to change the order of the lookups!!!!!!!!!!!! yuhuuuuuu -- first the receiver and then the sender :-) I am really sorry that you work so hard - please, take care of yourself and your health, this world needs as good people as you are if you still can find time to help stupid students in their internships' tasks :-)

All the best!

I wish you to relax a bit at least on the weekend - and indulge yourself with a wellness weekend and a picknick.... or something nice like this...

Ksénia