Previous Topic Next Topic

Help with comparing/deleting columns

Closed
Dryst999 Posts 3 Registration date Thursday December 13, 2012 Status Member Last seen December 16, 2012 - Dec 13, 2012 at 11:14 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Dec 17, 2012 at 07:34 PM
Hello,

I will be eternally grateful if anyone can help me with the following scenario. I have 4 columns

Vendor_Name Vender_Code Internal_Name Internal_Code
Smith, John 1234 Smith, John 56789



I need to do 1 to 1 mapping for the vendor codes and internal codes. To do this I need to match the vendor name with our internal name. The problem is, we have 60000 vendor names/codes and only 3000 Internal Codes. I do not want to go row by row and delete/add manually.

Is there a way to compare the names in column A and C, if A and C both exist keep the data. If the value in column A does not exist in column C, then delete the data in A/B for that row?

2 responses

Answer 1 / 2
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Dec 13, 2012 at 12:44 PM
What you ask can be done. However, i think it may be of more benefit to you
if you have on one sheet the internal vendor name (lets say sheet name is internal) and ids and other sheet extenal name and id (lets say sheet name is external)

Then on sheet 2 (extenal) you can use vlookup to match the names with internal

on Sheet2
to get internal id you can use
=If(iserror(VLOOKUP(a2, 'Internal'!A:B, 2, false)), "", VLOOKUP(a2, 'Internal'!A:B, 2, false))
0
Answer 2 / 2
Dryst999 Posts 3 Registration date Thursday December 13, 2012 Status Member Last seen December 16, 2012
Dec 15, 2012 at 05:51 PM
Thanks for much for your help, i'm having an issue running this formula, it keeps returning an error.

I've got my internal names/ID's on one sheet, and external names/ID's on sheet two. Sheet one is named Internal, sheet two is named External. What is the proper way to run this formula on sheet two, I just highlighted both columns on sheet two and tried to run the formula against them.
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Dec 16, 2012 at 09:11 AM
see this sample book
http://speedy.sh/RhdEg/Dryst999-Vlookup.xls
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Dec 16, 2012 at 09:14 AM
Lets say on both sheets, name is in column A and id is column B.
In column C of Extenal named sheet you need to enter
=If(iserror(VLOOKUP(a2, 'Internal'!A:B, 2, false)), "", VLOOKUP(a2, 'Internal'!A:B, 2, false))

and in Internal Sheet's column C, you can enter
=If(iserror(VLOOKUP(a2, 'External'!A:B, 2, false)), "", VLOOKUP(a2, 'External'!A:B, 2, false))
0
Dryst999 Posts 3 Registration date Thursday December 13, 2012 Status Member Last seen December 16, 2012
Dec 16, 2012 at 09:08 PM
Rizvisa, that works, thanks! I found one issue with the data that i'm not sure we can do anything about. Our vendor will sometimes have the middle initial included with the name, and vice versa. When the names don't match exactly, this formula doesn't work. Is there anyway to fix that?
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Dec 17, 2012 at 07:34 PM
Some one who is extra sharp with formula may be able to do it. One approach might be using custom formula. But would depends on data layout
0

Similar discussions

Can't remove threat with Windows Defender
Babarali4 -
ravida_1351 -

2 responses
My Snapchat is not deleting
arpzz44 -
HelpiOS -

1 response
Comparing columns and returning value
ll -
kittu -

35 responses
Should I delete my TikTok account and start over?
Frznrth -
najaf123 -

5 responses