Excel Problem - Please help

by staffjam

Posted: 13 years ago 5 replies

PROGRAMMING

I'm not sure if this is the correct place to post this question - but i'm hoping there is an excel guru here who may be able to help (it's Excel 07)

I'm currently trying to merge two spreadsheets i have - one has about 40,000 (List A) entries and the other 10,000 (List B)
What i would like to do is merge the two lists and any duplicates will be removed - but ONLY from List A - i would like List B to remain intact.

List A is pretty rough information and List B is work i have already done - so i just want the duplicates from List A to be deleted.

I have tried labelling them both and then using the remove duplicates - but excel doesn't discriminate - it just takes them from both of the lists.

Does anyone have any ideas what i need to do here??

Thanks for any help you can offer.

#excel #problem

tryinhere 13 years ago

sure that will be easy enough to do give me 2 seconds i will rustle up some code for you.

create a spread sheet

in col c (paste your column a data)
in col e (paste your column b data) (start in the same row as col c)

in col b paste this formula

=IF(ISERROR(MATCH(C5,$E$5:$E$37,0)),"","X")

edit the E$5:$E$37 section to the col e data size for example if your starting at row 1 and end at row 12345 edit those numbers

drag that formula to the bottom the data in col c

all of the cells in col b with an X are duplicates of col e

you can now copy n paste special all of that page / take column e and cut it and paste to the bottom of col c

sort cols b and c together to place the X at the top then delete those rows and what you have left is what you asked for

a long winded way but easiest way besides me doing it.
- [ 1 ] Thanks
Signature

| > Choosing to go off the grid for a while to focus on family, work and life in general. Have a great 2020 < |
{{ DiscussionBoard.errors[3163328].message }}
staffjam 13 years ago

Thanks for the help - sadly i just can't get it to work properly - the x's are appearing by the side of sites that aren't duplicates.
- Thanks
- 1 reply
Signature

â€œNever try to teach a pig to sing; it wastes your time and it annoys the pig.â€ Robert Heinlein
{{ DiscussionBoard.errors[3163542].message }}
- tryinhere 13 years ago
  
  Originally Posted by staffjam
  
  Thanks for the help - sadly i just can't get it to work properly - the x's are appearing by the side of sites that aren't duplicates.
  
  buddy i will send you my email in a pm and sort it in 2 seconds for you
  Pete
  
  or reversve the formula and re do it / try \/ that one / edit before dragging down as before
  
  =IF(ISERROR(MATCH(C5,$E$5:$E$37,0)),"X","")
  
  Thanks
  
  Signature
  
  | > Choosing to go off the grid for a while to focus on family, work and life in general. Have a great 2020 < |
  
  {{ DiscussionBoard.errors[3163561].message }}

Ambius

13 years ago

I don't think you need any fancy formulas, just a little procedural logic.

open sheet A. scroll to the bottom and create a new row with ############# (just to show where sheet A ends)

import sheet B below sheet A

use excel remove duplicates tool

now manually delete all of sheet B rows (below the ###########)

now import B again. all the duplicate values from A are gone and all the correct values from B are present.

wasn't that fun?
give me "thanks" if it worked.