Help! How to delete record from two columns if vlookup returns value??

robyboy123

New Member
Joined
Jul 20, 2010
Messages
10
Well hy everyone!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p> </o:p>
I desperate need help on my xls file. Here’s the deal. I have two columns containing id numbers. I used vlookup in the third column to find an id from the first column, and the range is the second column. Now if the vlookup finds the id, it returns the same id found in the search range (second column), otherwise it returns N/A, and that’s fine for me..<o:p></o:p>
<o:p> </o:p>
Here is the actual problem. A must now define a function which will compare the 1. and the 3. column, and if they match it should delete it from both the first and second column. <o:p></o:p>
I defined this =IF(ISNA(C578);A578;REPLACE(A578;1;40;""))<o:p></o:p>
Now I know this is not an elegant way of performing this task, and also I don’t know how to force excel to delete the same id number from the second column too ?? <o:p></o:p>
I’m worried about the "" … will this enter a space ( char(32)) in the row ? This could be a problem because I have to export the data back, and the space (blank) sign might be a problem. <o:p></o:p>
<o:p> </o:p>
So to clear things out, a have to delete the identical id’s form the first and second column. <o:p></o:p>
<o:p> </o:p>
Thnx in advance :) <o:p></o:p>
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

John Davis

Well-known Member
Joined
Sep 11, 2007
Messages
3,457
Well hy everyone!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p> </o:p>
I desperate need help on my xls file. Here’s the deal. I have two columns containing id numbers. I used vlookup in the third column to find an id from the first column, and the range is the second column. Now if the vlookup finds the id, it returns the same id found in the search range (second column), otherwise it returns N/A, and that’s fine for me..<o:p></o:p>
<o:p> </o:p>
Here is the actual problem. A must now define a function which will compare the 1. and the 3. column, and if they match it should delete it from both the first and second column. <o:p></o:p>
I defined this =IF(ISNA(C578);A578;REPLACE(A578;1;40;""))<o:p></o:p>
Now I know this is not an elegant way of performing this task, and also I don’t know how to force excel to delete the same id number from the second column too ?? <o:p></o:p>
I’m worried about the "" … will this enter a space ( char(32)) in the row ? This could be a problem because I have to export the data back, and the space (blank) sign might be a problem. <o:p></o:p>
<o:p> </o:p>
So to clear things out, a have to delete the identical id’s form the first and second column. <o:p></o:p>
<o:p> </o:p>
Thnx in advance :) <o:p></o:p>
Looks like you're doing everything manually. You could copy and paste special on Column C, replace N/A with "" and then sort on Column C, then A. That would bring all you're dupe to the top. This will at least get you a bump.

BTW "" this is the Null sign a space would be indicated by " "
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,785
Messages
5,513,430
Members
408,952
Latest member
Dram16

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top