Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Not Sure Which Function to Use for This
Hello Everyone,
I'm not sure what function to use to accomplish this; I have 4 columns; A B C D OLD ID ID REF LIST NEW ID RETURN VALUE 01224 01224 1 1 01225 01225 2 2 01226 01226 3 3 01227 01227 4 4 01227 01228 5 4 01227 01229 6 4 01228 01230 7 5 01228 01231 8 5 01229 01232 9 6 Column A is an old list of ship to address IDs for our customers. Each customer can have more than one ship to address, hence the multiple ship to IDs. Column B is just a reference list of customer IDs. Its used to match the old list of customer ship to ids in column A to the new ship to ids in column C. Example; old id 01224 is now new id 1, old id 01225 is now new id 2 and so on. What I need is a formula/function that looks at the numbers in column A and then determines if theres a matching number anywhere in column B. If the value in A1 = a value in B1:B5, then I want the formula to return the matching value in column C to column D.(see example above for desired result) I need to come up with some type of formula/function, because I have over 10,000 old IDs in a list to change into new IDs and don't want to have to to this line by line. Thanks for any help or alternative suggestions you may have. |
#2
|
|||
|
|||
Sorry, I'm using Excel 2003 on WinXP SP2
Thanks, Damon Johnson "tada991" wrote: Hello Everyone, I'm not sure what function to use to accomplish this; I have 4 columns; A B C D OLD ID ID REF LIST NEW ID RETURN VALUE 01224 01224 1 1 01225 01225 2 2 01226 01226 3 3 01227 01227 4 4 01227 01228 5 4 01227 01229 6 4 01228 01230 7 5 01228 01231 8 5 01229 01232 9 6 Column A is an old list of ship to address IDs for our customers. Each customer can have more than one ship to address, hence the multiple ship to IDs. Column B is just a reference list of customer IDs. Its used to match the old list of customer ship to ids in column A to the new ship to ids in column C. Example; old id 01224 is now new id 1, old id 01225 is now new id 2 and so on. What I need is a formula/function that looks at the numbers in column A and then determines if theres a matching number anywhere in column B. If the value in A1 = a value in B1:B5, then I want the formula to return the matching value in column C to column D.(see example above for desired result) I need to come up with some type of formula/function, because I have over 10,000 old IDs in a list to change into new IDs and don't want to have to to this line by line. Thanks for any help or alternative suggestions you may have. |
#3
|
|||
|
|||
One way
Put in D2: =IF(ISNA(MATCH(A2,B:B,0)),"",INDEX(C:C,MATCH(A2,B: B,0))) Copy down -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- tada991 wrote in message ... Sorry, I'm using Excel 2003 on WinXP SP2 Thanks, Damon Johnson "tada991" wrote: Hello Everyone, I'm not sure what function to use to accomplish this; I have 4 columns; A B C D OLD ID ID REF LIST NEW ID RETURN VALUE 01224 01224 1 1 01225 01225 2 2 01226 01226 3 3 01227 01227 4 4 01227 01228 5 4 01227 01229 6 4 01228 01230 7 5 01228 01231 8 5 01229 01232 9 6 Column A is an old list of ship to address ID's for our customers. Each customer can have more than one ship to address, hence the multiple ship to ID's. Column B is just a reference list of customer ID's. It's used to match the old list of customer ship to id's in column A to the new ship to id's in column C. Example; old id 01224 is now new id 1, old id 01225 is now new id 2 and so on. What I need is a formula/function that looks at the numbers in column A and then determines if there's a matching number anywhere in column B. If the value in A1 = a value in B1:B5, then I want the formula to return the matching value in column C to column D.(see example above for desired result) I need to come up with some type of formula/function, because I have over 10,000 old ID's in a list to change into new ID's and don't want to have to to this line by line. Thanks for any help or alternative suggestions you may have. |
#4
|
|||
|
|||
Put in D2:
=IF(ISNA(MATCH(A2,B:B,0)),"",INDEX(C:C,MATCH(A2,B: B,0))) Oops, missed out these lines: Any IDs in col A unmatched with col B (if any) will return blanks: "" Its also assumed that col B (ID REF LIST) does not contain any duplicate items -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#5
|
|||
|
|||
THANKS MAX!!!
That did it! You've just saved me 3 days work!!!!!! Damon "Max" wrote: Put in D2: =IF(ISNA(MATCH(A2,B:B,0)),"",INDEX(C:C,MATCH(A2,B: B,0))) Oops, missed out these lines: Any IDs in col A unmatched with col B (if any) will return blanks: "" Its also assumed that col B (ID REF LIST) does not contain any duplicate items -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#6
|
|||
|
|||
Glad it worked !
Thanks for the feedback -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- tada991 wrote in message ... THANKS MAX!!! That did it! You've just saved me 3 days work!!!!!! Damon |
#7
|
|||
|
|||
On Mon, 17 Jan 2005 07:57:06 -0800, "tada991"
wrote: Hello Everyone, I'm not sure what function to use to accomplish this; I have 4 columns; A B C D OLD ID ID REF LIST NEW ID RETURN VALUE 01224 01224 1 1 01225 01225 2 2 01226 01226 3 3 01227 01227 4 4 01227 01228 5 4 01227 01229 6 4 01228 01230 7 5 01228 01231 8 5 01229 01232 9 6 Column A is an old list of ship to address ID’s for our customers. Each customer can have more than one ship to address, hence the multiple ship to ID’s. Column B is just a reference list of customer ID’s. It’s used to match the old list of customer ship to id’s in column A to the new ship to id’s in column C. Example; old id 01224 is now new id 1, old id 01225 is now new id 2 and so on. What I need is a formula/function that looks at the numbers in column A and then determines if there’s a matching number anywhere in column B. If the value in A1 = a value in B1:B5, then I want the formula to return the matching value in column C to column D.(see example above for desired result) I don't understand your description of what you want. I am having trouble reconciling "if there’s a matching number anywhere in column B" with "If the value in A1 = a value in B1:B5" I'm not sure about the significance of B1:B5. If the B1:B5 was a misstatement, and you really want to generate a value based on the OLD ID appearing anywhere in the ID REF LIST column, then, assuming your values are in columns A:C, the formula: =VLOOKUP(A2,$B:$C,2,0) entered in D2 and copy/dragged down as far as necessary would seem to do what you describe. You may shorten the range reference for the table to something like $B$2:$C$10001 (encompassing only your 10,000 entries) if the formula above runs too slowly or if have possibly confounding data below the last table entry. The formula will return #N/A if there is no matching entry in Column B. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to list unique values | Excel Worksheet Functions | |||
I cant use englisch function names in a swedich version of excel | Excel Discussion (Misc queries) | |||
Find a Function to use accross different worksheets | Excel Worksheet Functions | |||
change function variable prompts?? | Excel Worksheet Functions | |||
Counting Function Dilemma | Excel Worksheet Functions |