Previous Topic Next Topic

Help with comparing/deleting columns

Closed
Dryst999 Posts 3 Registration date Thursday 13 December 2012 Status Member Last seen 16 December 2012 - 13 Dec 2012 à 11:14
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 - 17 Dec 2012 à 19:34
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?
Related:

2 responses

Réponse 1 / 2
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
13 Dec 2012 à 12:44
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))
Réponse 2 / 2
Dryst999 Posts 3 Registration date Thursday 13 December 2012 Status Member Last seen 16 December 2012
15 Dec 2012 à 17:51
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.
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
16 Dec 2012 à 09:11
see this sample book
http://speedy.sh/RhdEg/Dryst999-Vlookup.xls
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
16 Dec 2012 à 09:14
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))
Dryst999 Posts 3 Registration date Thursday 13 December 2012 Status Member Last seen 16 December 2012
16 Dec 2012 à 21:08
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?
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
17 Dec 2012 à 19:34
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

Similar discussions

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

5 responses
Compare values in two columns and return the value from third
ramdubai -
keshav -

11 responses
Convert data with rows into columns
pooja.bs -
TrowaD -

2 responses
Match value in 2 separate columns and return a value from a 3rd
JD_Silver -
TrowaD -

1 response
Comparing columns and returning value
ll -
kittu -

35 responses