Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a formula to update data in 2 columns
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a formula to update data in 2 columns
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 On May 12, 8:53*pm, MMcGee wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
some worksheet columns don't update formula calculations | Excel Worksheet Functions | |||
Formula to match 7 columns of non adjacent data | Excel Worksheet Functions | |||
auto update formula when inserting additional columns | Excel Worksheet Functions | |||
update data in workbooks through formula | Excel Discussion (Misc queries) | |||
Formula won't update with new data | New Users to Excel |