Remove duplicates

[Kifsif]

These are data collected from web sites. I copy data on a special list and then just juxtapose prices with model names.

Later users will:
1. Add new model names. It will be done from the factory address list which is updated but which contains both old and new models together. Anyway, a user will just take the whole model names column and add it to a column A from below.
2. Combine their data again by adding from below. They will just copy entire rows and paste their data underneath.

My difficulty is how to remove duplicates? But! If the rows contain data, the programm should show a notification, quit the procedure and place the cursor at the model name for manual solution.

I have tried to solve this problem via a macros, but failed.
But I just don't know how to organize a cycle which repeats while there are some duplicates to be removed automatically.

Hope for your assistance and thank you in advance.

[Charles]

Kifsif,

Welcome to the forum.
What is your criteria. Is it in column A ?
In your attachment I can see duplicates in Column B and C.

[Kifsif]

Kifsif,

Welcome to the forum.
What is your criteria. Is it in column A ?
In your attachment I can see duplicates in Column B and C.

Sorry for being not that precise.
Critera is in column A.

Columns B and C contain precious information which I have to preserve.

[Charles]

Hi,

See if the following code works. Paste the code to a module and test on a copy of your data.

Please Login or Register  to view this content.

[Kifsif]

Thank very much you for your assistance.

Unfortunately it is not what I'm looking for.

I can see:
1. If you try copying the last model name (KDC3047GY) and paste it again in the cell below.
Both rows with KDC3047GY will disappear.
2. If I add a duplicate KDC3047GY 2251 2251, that is I change the data in rows from 2250 to 2251,
this macros removes both rows and no KDC3047GY is left at all.
3. I chronologically collect data in the columns. I mean that indefinite number of columns can be added to the right. But you set the autofilter only on columns A-D.
4. Will it work in the MS Office 2003? At home I have the version 2007, so I can't check.

By the way, is there nothing of use in my algorythm? I suppose I just can not make the cycle repeat indifinitely while the ciriterion is true. Maybe you can help me in this?

[Charles]

Hi,

The code looks at column A and if it see a dup it will delete the dup. Can you provide an example of what you are referring too. That is a sheet with the data that has the dup and a sheet that shows you want it should be after the dup was deleted.
The code I provide only looks at column A and it may need to check the other columns as well.

[Kifsif]

I tried to explain in the attached file.

[Charles]

Hi,

I had an error in the code that I posted. However see if this attachment is what you want. I renamed your original "Table" sheet to "Table1" and in the new "Table" sheet I added what I think the format will be of your data. Per the lines at Row 74 of the original "Table" worksheet.
In the new Table sheet you will see a button. If you click on it I think the code will produce the result you want.

[Kifsif]

Unfortunately it doesn't work.
1. If you duplicate DDX5026 and place 1 in J22 or in J2, the entire row will be deleted whereas I need that the whole row be taken into account.
2. If you try to press the button once more, an error occurs.

[Charles]

Hi,

I see what your are saying, however, if the Model exist then why would you want to re-enter it and then add the data associated with it. Would it be simpler if you checked to see if the Model existed and if so add the data for it. This would stop the Duplications. I attached a copy with code that when you click the "Click" button a userform will appear. The form will have a drop down for the "Model" when you start to type the Model it will show you the closet match to that Model you can now click the Down Arrow and it will display th Models that may or may not be the one you want. If you see the Model then select it and the fill the "Date" and "Count". Click the "Enter" button and this will send the data to the Model selected.
If the Model does not exist what ever value you have typed in for Model will be populated to the last row in column A. If you have a Date and Count they too will be add to the worksheet. If however you do not have the "Date" and "Count" populated the code will just add the Model to the last row in column A and exit the sub.

[Marcol]

Is this formula based approach any use to you?

To see it work
1/. Copy your final table to Sheet1

2/. In K2

Please Login or Register  to view this content.

Drag/Fill Down
3/. In L2

Please Login or Register  to view this content.

Drag/Fill Down then Across to Column S.

4/. Now sort the copied table by Column A then by Column B

5/. Toggle Undo/Redo to see the difference sorting makes.

Just a thought to avoid VBa