[DigitalN]

Hey all, I need your help!

I am currently running Office 2000 (But have access to 2007) on XP and I have a bit of an issue that I hope you can help with.

Right now since a system upgrade to a new software, it requires me to do ordering for our customers by manually searching every part number by hand. Unfortunately this means using old sales statistics, which means I have to keep an old excel file around with the sales figures. Here is where I need your help:

We change vendors pretty often with part numbers, and that means I have to do a run off of what numbers that we are using every month I make the order. Normally this wouldn't be so bad but I need to keep the sales figures, and that means every month I need to manually combine the spreadsheets, sort by product number, and then delete the duplicate lines by hand so that I can have the new numbers mixed in.

I have tried a few VB scripts I found floating around, however this does not solve my issue. Since it will delete just the duplicate text and not the entire line. I have heard that Access might be able to help me out with this however I have no experience with it and cannot try it out.

If this sounds confusing at all, let me know and I will try and make it more smooth, but for now I need to get back to deleting some lines...


Thank you very much for your help!

[DigitalN]

Just to help everyone out with what I need, I have made an example file.

As you can tell I need to delete the 624 lines, and want to keep the 124 lines around.

If there is anyway to do this, let me know!

[Rollin_Again]

Can you post the original sample file and the VB script you used to delete the duplicate text. We should be able to modify the script to delete the whole line instead of just the text.

Regards,
Rollin

[DigitalN]

Rollin Again,

http://www.vbaexpress.com/kb/getarticle.php?kb_id=520

this is what I had tried, but I found that when it was doing the deletion it would only delete the text and not the row that is necessary to be deleted.


Another thing that could be done for me, is that when merging the two files together, that if going from my first example, if bringing 124 lines into the 624 sheet (which is how it needs to be done every time) when there is a 624 line present with the same part number, it automatically fills the rest of the line with the data from the 124 sheet.

Thanks for your time!

[Rollin_Again]

Can you please post the original file as it appears before you ran the script?

Regards,
Rollin

[DigitalN]

Rollin,

I only tried it on the example file that they provided on that link and discovered it did not work for my application.

The file I want it to work on is attached to my second post of the thread, if you need me to re-host it somewhere else I can do this also.

[DigitalN]

Okaayyy so I have figured out a way to make the duplicated go away...

(writing this for later so I can reference back )

In Excel 2007 on the ribbon, go to data, and then remove duplicates. Select part number and let it do its thing.
In my case I need to be in the file with all the 124 numbers, and add the 624 numbers in at the bottom, that way it deleted the 624 assuming there was duplicates.


Now the only issue I am having, (I am unsure if there is a way to fix this) but I need to know if it is possible that when it is done this process, if there is a line in 124 that didn't have a duplicate in the 624 line then it would delete itself?

That way I wouldn't have stock that is not supposed to be ordered from that vendor being ordered, since it has changed over to a new one. I am unsure how to go about this though.