SOLVED
Home

Substituting data from one column with required data from another column

%3CLINGO-SUB%20id%3D%22lingo-sub-871411%22%20slang%3D%22en-US%22%3ESubstituting%20data%20from%20one%20column%20with%20required%20data%20from%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-871411%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20day%20everyone.%20I%20have%20one%20more%20question%20which%20I%20battle%20to%20solve.%20I%20have%20a%20column%20with%20various%20supplier%20stock%20codes%20(for%20example%20C-010%2C%20C-101%2C%20C-102).%20What%20I%20want%20is%20to%20create%20a%20column%20where%20those%20stock%20codes%20will%20be%20substituted%20with%20my%20own%20stock%20codes%20C-010%20with%20G-15%2C%20C-101%20with%20G-20%2C%20C-102%20with%20G-25.%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20anything%20I%20can%20use%20to%20speed%20up%20the%20process%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20228px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F133252iE8E9691B07A37FD0%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%2224-09-2019%2012-31-46%20p-m-.jpg%22%20title%3D%2224-09-2019%2012-31-46%20p-m-.jpg%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EStock%20table%20with%20supplier's%20codes%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20153px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F133251i42C32C4043688539%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%2224-09-2019%2012-27-04%20p-m-.jpg%22%20title%3D%2224-09-2019%2012-27-04%20p-m-.jpg%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EMy%20table%20where%20I%20have%20allocated%20my%20own%20stock%20number%20to%20each%20supplier's%20code%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-871411%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-871513%22%20slang%3D%22en-US%22%3ERe%3A%20Substituting%20data%20from%20one%20column%20with%20required%20data%20from%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-871513%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F413321%22%20target%3D%22_blank%22%3E%40AlexeyNZ%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20try%20this...%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DCHOOSE(MATCH(N2%2C%7B%22C-010%22%2C%22C-101%22%2C%22C-102%22%7D%2C0)%2C%22G-15%22%2C%22G-20%22%2C%22G-25%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20it%20would%20be%20better%20to%20create%20a%20lookup%20table%20and%20use%20the%20VLookup%20formula%20to%20get%20the%20substituted%20value%20like%20this...%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DVLOOKUP(N2%2C%24U%242%3A%24V%244%2C2%2C0)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EWhere%20%3CSTRONG%3E%24U%242%3A%24V%244%3C%2FSTRONG%3E%20is%20the%20Lookup%20Table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERefer%20to%20the%20attached%20for%20more%20details.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-878053%22%20slang%3D%22en-US%22%3ERe%3A%20Substituting%20data%20from%20one%20column%20with%20required%20data%20from%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-878053%22%20slang%3D%22en-US%22%3EBrilliant%2C%20thanks%20a%20lot!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-878195%22%20slang%3D%22en-US%22%3ERe%3A%20Substituting%20data%20from%20one%20column%20with%20required%20data%20from%20another%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-878195%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F413321%22%20target%3D%22_blank%22%3E%40AlexeyNZ%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20welcome!%20Glad%20it%20worked%20as%20desired.%3C%2FP%3E%3CP%3EThanks%20for%20the%20feedback!%3C%2FP%3E%3C%2FLINGO-BODY%3E
AlexeyNZ
Occasional Contributor

Good day everyone. I have one more question which I battle to solve. I have a column with various supplier stock codes (for example C-010, C-101, C-102). What I want is to create a column where those stock codes will be substituted with my own stock codes C-010 with G-15, C-101 with G-20, C-102 with G-25. 

Is anything I can use to speed up the process?

 

24-09-2019 12-31-46 p-m-.jpgStock table with supplier's codes24-09-2019 12-27-04 p-m-.jpgMy table where I have allocated my own stock number to each supplier's code

3 Replies
Solution

@AlexeyNZ 

You may try this...

=CHOOSE(MATCH(N2,{"C-010","C-101","C-102"},0),"G-15","G-20","G-25")

 

But it would be better to create a lookup table and use the VLookup formula to get the substituted value like this...

=VLOOKUP(N2,$U$2:$V$4,2,0)

Where $U$2:$V$4 is the Lookup Table.

 

Refer to the attached for more details.

 

 

@AlexeyNZ 

You're welcome! Glad it worked as desired.

Thanks for the feedback!

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies