Need a formula to update data in 2 columns

  • Thread starter Thread starter MMcGee
  • Start date Start date
M

MMcGee

Hello:

I have a spreadsheet with 2 columns that consistently need to be updated.
One column is a contact person ("Contact"), and another column is a group
that the contract person is responsible for ("Group"). I download the data
from SAP once a week and the contacts and groups in SAP never match
correctly, so I have to manually change them. I do this by filtering on the
'Group' column and changing the name for each group. I would like to create
a formula to do this automatically.

My dilemma is, not all of the groups and contacts are a mismatch. Out of
the 14 choices in the "Group" column, only 7 need to be updated with the
correct contact person. 3 of those 7 groups have the same contact person.
The closest I have come is an 'IF' function, but that doesn't work because it
does not apply to all the groups. Any suggestions? I'm working in Excel
2003. Thanks!
 
You should be able to use vlookup to "translate" one group code to
another. List all your 14 groups say in X1:X14 and next to them put in
what you want them to be in Y1:Y14. Then, assuming the groups from SAP
are in A1, use this formula:

=VLOOKUP(A1,X$1:Y$14,2,0)

Copy this down as required.

Hope this helps.

Pete
 
Back
Top