Help with comparing/deleting columns

[Closed]
Report
Posts
3
Registration date
Thursday December 13, 2012
Status
Member
Last seen
December 16, 2012
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
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 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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))
Posts
3
Registration date
Thursday December 13, 2012
Status
Member
Last seen
December 16, 2012

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.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
see this sample book
http://speedy.sh/RhdEg/Dryst999-Vlookup.xls
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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))
Posts
3
Registration date
Thursday December 13, 2012
Status
Member
Last seen
December 16, 2012

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?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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