The best would be if in "Original" only remains on out of the dublicates and that records is recorded in "Doubles"
So basically if there are dublicate records in "Original" keep one of them in "Original" and put one of them into "Double" so I know it is that particular record.
Perhaps clear its price to 0,00
As the Price is incorrect or not known for this particular "Article"
So to understand it.
Following situation and prefered outcome.
I received an "Article Pricelist" in Excel. With following Columns "Artikelnummer", "Beschreibung", Preis", "Von Datum"
Many of those records include dublicates.
In the first step I delete all dublicate Records with "RemoveDuplicates on Excel.
perhaps I will include that in the code too?
However, as there are records having the same "Artikelnummer" , "Beschreibung", but different "Preis" or "Von Datum"
I need those records to be included but only once.
Maybe a simpler aproach would be to change those records the price to 0,00 and don't bother about sending it to a new sheet?
And then remove the dublicates again.
So for instance those remaining dublicates are:
Artikelnummer | Bezeichnung | Preis | Von Datum |
11142 | Article with Certain Description | 20,33 | 01.01.2022 |
11142 | Article with Certain Description | 18,33 | 01.01.2022 |
11142 | Article with Certain Description | 19,33 | 01.01.2022 |
11142 | Article with Certain Description | 22,35 | |
1112222 | Another Description | 12,33 | 01.01.2022 |
1112222 | Another Description | 123,35 | 01.01.2022 |
Now I like to have in my "Original"
11142 | Article with Certain Description | 0,00 | 01.01.2000 |
1112222 | Another Description | 0,00 | 01.01.2000 |
1021201 | Price and Description where correct an
all dublicates where removed in the
first step by RemoveDuplicates | 155,32 | 01.01.2022 |
Thinking of changing the "Preis" to 0,00 and the Date to something other then the original perhabs 01.01.2000 so I can differentiate from the others.
Then I import those Records to Access with no Dublicates in "Artiklenummer"
So the "double" Worksheet was for me to visualize the duplicate records and is not really needed if only one record will remain in the "Original" Worksheet with "Preise" set to 0,00 and perhabs the Date changed to "01.01.2000" of all those who are not beeing removed by RemoveDuplicates.
But only one record should remain as shown above.
Sorry for changing it from the original logic but I guess that would be the perfect aproach to this and would be fantastic if that could be worked out by code.
Many thanks for your help!