| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Not sure which function and how to use it. Help
Hello Everyone,
Im trying to change all the customer ids in column A to the corresponding ship to ids in column F. Theres a one to many relationship between F and A. I need a formula that will look up the values in column A. Then look for a matching number in column D. When it finds a matching number between column A and D, take the ship to id in column F that is on the same row as the matching customer id in column D and place it on the appropriate row in column C. customer_id A customer_name B C customer_id D customer_name E ship_to_id F 1288 INDUSTRY TECNOLOGICAL 1264 Quality Hyd. and Machining 24267 1288 INDUSTRY TECNOLOGICAL 1265 Northside Implement 24278 1290 PARTS UNLIMITED 1266 Scott Equipment 24289 1290 PARTS UNLIMITED 1267 Tiger Machinery 24300 1290 PARTS UNLIMITED 1268 JR's Construction Parts, Inc. 24311 1295 EVERGREEN SUPPLY 1288 INDUSTRY TECNOLOGICAL 24312 1295 EVERGREEN SUPPLY 1290 PARTS UNLIMITED 24313 1295 EVERGREEN SUPPLY 1295 EVERGREEN SUPPLY 24314 1295 EVERGREEN SUPPLY customer_id A customer_name B C customer_id D customer_name E ship_to_id F 1288 INDUSTRY TECNOLOGICAL 1264 Quality Hyd. and Machining 24267 1288 INDUSTRY TECNOLOGICAL 1265 Northside Implement 24278 1290 PARTS UNLIMITED 1266 Scott Equipment 24289 1290 PARTS UNLIMITED 1267 Tiger Machinery 24300 1290 PARTS UNLIMITED 1268 JR's Construction Parts, Inc. 24311 1295 EVERGREEN SUPPLY 1288 INDUSTRY TECNOLOGICAL 24312 1295 EVERGREEN SUPPLY 1290 PARTS UNLIMITED 24313 1295 EVERGREEN SUPPLY 1295 EVERGREEN SUPPLY 24314 1295 EVERGREEN SUPPLY So in this example what I would end up with is this; customer_id A customer_name B C customer_id D customer_name E ship_to_id F 1288 INDUSTRY TECNOLOGICAL 1264 Quality Hyd. and Machining 24267 1288 INDUSTRY TECNOLOGICAL 1265 Northside Implement 24278 1290 PARTS UNLIMITED 1266 Scott Equipment 24289 1290 PARTS UNLIMITED 1267 Tiger Machinery 24300 1290 PARTS UNLIMITED 1268 JR's Construction Parts, Inc. 24311 1295 EVERGREEN SUPPLY 1288 INDUSTRY TECNOLOGICAL 24312 1295 EVERGREEN SUPPLY 1290 PARTS UNLIMITED 24313 1295 EVERGREEN SUPPLY 1295 EVERGREEN SUPPLY 24314 1295 EVERGREEN SUPPLY customer_id A customer_name B C customer_id D customer_name E ship_to_id F 1288 INDUSTRY TECNOLOGICAL 24312 1264 Quality Hyd. and Machining 24267 1288 INDUSTRY TECNOLOGICAL 24312 1265 Northside Implement 24278 1290 PARTS UNLIMITED 24313 1266 Scott Equipment 24289 1290 PARTS UNLIMITED 24313 1267 Tiger Machinery 24300 1290 PARTS UNLIMITED 24313 1268 JR's Construction Parts, Inc. 24311 1295 EVERGREEN SUPPLY 24314 1288 INDUSTRY TECNOLOGICAL 24312 1295 EVERGREEN SUPPLY 24314 1290 PARTS UNLIMITED 24313 1295 EVERGREEN SUPPLY 24314 1295 EVERGREEN SUPPLY 24314 1295 EVERGREEN SUPPLY 24314 I pasted this example from excel. Sorry if the colums do not line up. CAn anyone tell me how I can better align the columns so that this example is better to understand? |
|
#2
|
|||
|
|||
|
try
=index(F:F,match(A1,D:D,0),0,1,1) in C1 and copy down "Damon Johnson" wrote: Hello Everyone, Im trying to change all the customer ids in column A to the corresponding ship to ids in column F. Theres a one to many relationship between F and A. I need a formula that will look up the values in column A. Then look for a matching number in column D. When it finds a matching number between column A and D, take the ship to id in column F that is on the same row as the matching customer id in column D and place it on the appropriate row in column C. customer_id A customer_name B C customer_id D customer_name E ship_to_id F 1288 INDUSTRY TECNOLOGICAL 1264 Quality Hyd. and Machining 24267 1288 INDUSTRY TECNOLOGICAL 1265 Northside Implement 24278 1290 PARTS UNLIMITED 1266 Scott Equipment 24289 1290 PARTS UNLIMITED 1267 Tiger Machinery 24300 1290 PARTS UNLIMITED 1268 JR's Construction Parts, Inc. 24311 1295 EVERGREEN SUPPLY 1288 INDUSTRY TECNOLOGICAL 24312 1295 EVERGREEN SUPPLY 1290 PARTS UNLIMITED 24313 1295 EVERGREEN SUPPLY 1295 EVERGREEN SUPPLY 24314 1295 EVERGREEN SUPPLY customer_id A customer_name B C customer_id D customer_name E ship_to_id F 1288 INDUSTRY TECNOLOGICAL 1264 Quality Hyd. and Machining 24267 1288 INDUSTRY TECNOLOGICAL 1265 Northside Implement 24278 1290 PARTS UNLIMITED 1266 Scott Equipment 24289 1290 PARTS UNLIMITED 1267 Tiger Machinery 24300 1290 PARTS UNLIMITED 1268 JR's Construction Parts, Inc. 24311 1295 EVERGREEN SUPPLY 1288 INDUSTRY TECNOLOGICAL 24312 1295 EVERGREEN SUPPLY 1290 PARTS UNLIMITED 24313 1295 EVERGREEN SUPPLY 1295 EVERGREEN SUPPLY 24314 1295 EVERGREEN SUPPLY So in this example what I would end up with is this; customer_id A customer_name B C customer_id D customer_name E ship_to_id F 1288 INDUSTRY TECNOLOGICAL 1264 Quality Hyd. and Machining 24267 1288 INDUSTRY TECNOLOGICAL 1265 Northside Implement 24278 1290 PARTS UNLIMITED 1266 Scott Equipment 24289 1290 PARTS UNLIMITED 1267 Tiger Machinery 24300 1290 PARTS UNLIMITED 1268 JR's Construction Parts, Inc. 24311 1295 EVERGREEN SUPPLY 1288 INDUSTRY TECNOLOGICAL 24312 1295 EVERGREEN SUPPLY 1290 PARTS UNLIMITED 24313 1295 EVERGREEN SUPPLY 1295 EVERGREEN SUPPLY 24314 1295 EVERGREEN SUPPLY customer_id A customer_name B C customer_id D customer_name E ship_to_id F 1288 INDUSTRY TECNOLOGICAL 24312 1264 Quality Hyd. and Machining 24267 1288 INDUSTRY TECNOLOGICAL 24312 1265 Northside Implement 24278 1290 PARTS UNLIMITED 24313 1266 Scott Equipment 24289 1290 PARTS UNLIMITED 24313 1267 Tiger Machinery 24300 1290 PARTS UNLIMITED 24313 1268 JR's Construction Parts, Inc. 24311 1295 EVERGREEN SUPPLY 24314 1288 INDUSTRY TECNOLOGICAL 24312 1295 EVERGREEN SUPPLY 24314 1290 PARTS UNLIMITED 24313 1295 EVERGREEN SUPPLY 24314 1295 EVERGREEN SUPPLY 24314 1295 EVERGREEN SUPPLY 24314 I pasted this example from excel. Sorry if the colums do not line up. CAn anyone tell me how I can better align the columns so that this example is better to understand? |
|
#3
|
|||
|
|||
|
WOW!!!
bj, thanks sooo much. I've been wearing myself trying to figure this out. It worked perfectly. Again, many thanks!!! Damon "bj" wrote: try =index(F:F,match(A1,D:D,0),0,1,1) in C1 and copy down "Damon Johnson" wrote: Hello Everyone, Im trying to change all the customer ids in column A to the corresponding ship to ids in column F. Theres a one to many relationship between F and A. I need a formula that will look up the values in column A. Then look for a matching number in column D. When it finds a matching number between column A and D, take the ship to id in column F that is on the same row as the matching customer id in column D and place it on the appropriate row in column C. customer_id A customer_name B C customer_id D customer_name E ship_to_id F 1288 INDUSTRY TECNOLOGICAL 1264 Quality Hyd. and Machining 24267 1288 INDUSTRY TECNOLOGICAL 1265 Northside Implement 24278 1290 PARTS UNLIMITED 1266 Scott Equipment 24289 1290 PARTS UNLIMITED 1267 Tiger Machinery 24300 1290 PARTS UNLIMITED 1268 JR's Construction Parts, Inc. 24311 1295 EVERGREEN SUPPLY 1288 INDUSTRY TECNOLOGICAL 24312 1295 EVERGREEN SUPPLY 1290 PARTS UNLIMITED 24313 1295 EVERGREEN SUPPLY 1295 EVERGREEN SUPPLY 24314 1295 EVERGREEN SUPPLY customer_id A customer_name B C customer_id D customer_name E ship_to_id F 1288 INDUSTRY TECNOLOGICAL 1264 Quality Hyd. and Machining 24267 1288 INDUSTRY TECNOLOGICAL 1265 Northside Implement 24278 1290 PARTS UNLIMITED 1266 Scott Equipment 24289 1290 PARTS UNLIMITED 1267 Tiger Machinery 24300 1290 PARTS UNLIMITED 1268 JR's Construction Parts, Inc. 24311 1295 EVERGREEN SUPPLY 1288 INDUSTRY TECNOLOGICAL 24312 1295 EVERGREEN SUPPLY 1290 PARTS UNLIMITED 24313 1295 EVERGREEN SUPPLY 1295 EVERGREEN SUPPLY 24314 1295 EVERGREEN SUPPLY So in this example what I would end up with is this; customer_id A customer_name B C customer_id D customer_name E ship_to_id F 1288 INDUSTRY TECNOLOGICAL 1264 Quality Hyd. and Machining 24267 1288 INDUSTRY TECNOLOGICAL 1265 Northside Implement 24278 1290 PARTS UNLIMITED 1266 Scott Equipment 24289 1290 PARTS UNLIMITED 1267 Tiger Machinery 24300 1290 PARTS UNLIMITED 1268 JR's Construction Parts, Inc. 24311 1295 EVERGREEN SUPPLY 1288 INDUSTRY TECNOLOGICAL 24312 1295 EVERGREEN SUPPLY 1290 PARTS UNLIMITED 24313 1295 EVERGREEN SUPPLY 1295 EVERGREEN SUPPLY 24314 1295 EVERGREEN SUPPLY customer_id A customer_name B C customer_id D customer_name E ship_to_id F 1288 INDUSTRY TECNOLOGICAL 24312 1264 Quality Hyd. and Machining 24267 1288 INDUSTRY TECNOLOGICAL 24312 1265 Northside Implement 24278 1290 PARTS UNLIMITED 24313 1266 Scott Equipment 24289 1290 PARTS UNLIMITED 24313 1267 Tiger Machinery 24300 1290 PARTS UNLIMITED 24313 1268 JR's Construction Parts, Inc. 24311 1295 EVERGREEN SUPPLY 24314 1288 INDUSTRY TECNOLOGICAL 24312 1295 EVERGREEN SUPPLY 24314 1290 PARTS UNLIMITED 24313 1295 EVERGREEN SUPPLY 24314 1295 EVERGREEN SUPPLY 24314 1295 EVERGREEN SUPPLY 24314 I pasted this example from excel. Sorry if the colums do not line up. CAn anyone tell me how I can better align the columns so that this example is better to understand? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
