Macro to remove duplicates from several collums, add specified text and sort

[PTLS]

Hi!

Can anyone help me to make a macro that do the following:

1. Add the specified text "'0" at the cells in collum C that has one number less than those who start at 1.
2. Remove duplicates from the four collums so i'm only left with unique customernumbers along with their names and the date?
3. Sort the rows from the earliest date first.

Collum A = Lastname
Collum B = First and middle name
Collum C = Customernumber (those with 8 numbers has to have 9 numbers and start with "'0")
Collum D = Date

Look at attached file for the example.
The original file contains over 100.000 rows.

[Richard Buttrey]

Do you have Excel 365 or an earlier version? Please update your profile with whatever it is.

Does each of the customer number and date need to be unique. This isn't a particularly good example since there are no duoploicate customer numbers.

Please upload a workbook which does have duplicates and manually add the results you expect. For instance if there were two or more rows with the same customer number but different dates, does that count as two unique records or just one, and if one which of the dates would you expect to see

[PTLS]

Do you have Excel 365 or an earlier version? Please update your profile with whatever it is.

Does each of the customer number and date need to be unique. This isn't a particularly good example since there are no duoploicate customer numbers.

Please upload a workbook which does have duplicates and manually add the results you expect. For instance if there were two or more rows with the same customer number but different dates, does that count as two unique records or just one, and if one which of the dates would you expect to see

Hi!

Yeah, I use Excel 365(updated my profile now).

The customer number needs to be unique, not the date. The same name and customer number can often be found in 2018, aswell as 2019, 2020, 2021 and so on.
In the updated workbook I added you can see an example in red text.

Please Login or Register  to view this content.

remove the oldest date, keep the newest date as the new woorkbook shows.

[sintek]

100 000 Rows

Give this a go...

Please Login or Register  to view this content.

[PTLS]

Supposed to delete this post, don't know how. sorry

[PTLS]

100 000 Rows

Give this a go...

Please Login or Register  to view this content.

Thanks man!
It works, but my workPC doesn't handle this many rows sadly. Excel crashes when i run the macro.
I dont really need the names. If i delete collum A and B with the names, can you perhaps make a macro that does the same job but just with customernumbers and the dates?

[sintek]

Strange...Runs 1.16 seconds with 100 000 rows this side...

[PTLS]

Strange...Runs 1.16 seconds with 100 000 rows this side...

Can be my laptop that don't handle it?
Could you make me a macro that does the same job without name and lastname? Added an example as attachment

[sintek]

Please Login or Register  to view this content.

[PTLS]

It still crashes. I get the message "Excel is not responding".
Even when I tried doing it with only 15.000 rows.

[sintek]

No need to quote entire posts..It just clutters the thread...Upload said sample with 15000 rows...
Sure someone will solve for you...

[PTLS]

Thanks for help tho!

Yeah, hopefully someone will

[sintek]

Try this quick...

Please Login or Register  to view this content.

[PTLS]

Crashes when I try on all rows, but it works when i around 40.000 rows.

[sintek]

Upload an equivalent sample to your file ... Only way to test alternatives...

[jindon]

PTLS,

Try

Please Login or Register  to view this content.

[PTLS]

It worked!! I just had to wait 30min or so while it stood at "Excel is not responding".
Thanks alot for the help :D

[PTLS]

Thanks jindon, but the latest marco from sintek worked

[sintek]

Thanks jindon, but the latest marco from sintek worked

If you had to wait 30 min for mine to work then ... No...jindon's must be split seconds...

Edit...I just ran 135000+ rows with Post 13 code and it took 0.57 seconds