Question Excel

[Jimmy]

Evening Folks

I need some advice on excel 2003. I'm currently helping a team at work find and delete duplicates from there sheets they use, find them isn't the issue but getting rid of them in bulk is.

What we have is a workbook with about 7-8 tabs each with account numbers on, over all there is 26k accounts on the workbook, what happens is when the team needs to work an account they copy the number from the row and paste it to the bottom while leaving the the old one at the top (Retarded i know) and continue this for each account they work creating the duplicates. All i have is that the duplicates i need to delete are at the top there is also no date to say when each one was worked ant idea's on how to delete them on mas rather than go trough each one?

any help is much appricated.

Ps sorry for any shit grammer

 

[Bahumat]

Tell them it's their problem for using such a retarded method.

 

[Zenith.UK]

The easy way to delete en mass would be to just delete the XLS but I think that's kind of defeating the object.

From what you say, they paste the most recent ones to the bottom. That means the earlier ones are the ones you want to delete/get rid of? You don't need the date, but you do need the cell location.
CTRL+F, enter the search term (account number), make sure "Seach in" is set to workbook, hit "Find All". You'll get a list in order of all the cells that contain that piece of information. Copy the last one of each sheet into a new spreadsheet. It would be easier to copy the last instance of each account number rather than delete all the other instances. Once you're sure all the account numbers are accounted for in the new spread sheet, you can ditch the original file.

 

[Jimmy]

Thanks Zenith but im trying to avoid that way as there are 26k accounts will take a while

 

[Gahn]

Thanks Zenith but im trying to avoid that way as there are 26k accounts will take a while

No matter the Tech/Software, the only way to be sure there are no Duplicates is to reiterate all rows.
Ofc that wouldn't be a real problem if you had a Relational Database behind as Database and T-Sql to write a Select, with Excel .. well .. they gonna take many coffe breaks ^^

Some1 more versed than me in Excel Vba is free to slap me in the wrist

 

[Zenith.UK]

I didn't have the time to type it earlier, but maybe you could get someone versed in VBA to script the search for unique account numbers and output the results to a new file.
It's beyond my easy fix, and I've not done any VBA for a number of years.

 

[Jupitus]

Clue - insert a column to the left, type 1,2,3 in descending cells in it, then autofill right down to the bottom... this will act as a sorting order column so although you don't have a date you can manage without... I'll ponder some more...

 

[TdC]

refuse to do it, and force them to migrate to a RDBMS. accuse them of having small willies, sleep with their wives, do anything. make them use a database. it's better. you know it. they know it.

 

[dysfunction]

Put a number index in one column ie give each line a unique number.

Sort by the account number

In a new column have a formula that says:
=if(a6=a5,"duplicate","")

Filter on "duplicate"
Select visible cells alt+;
Delete the rows

Resort by the number index

Done.

 

[dysfunction]

Put a number index in one column ie give each line a unique number.

Sort by the account number

In a new column have a formula that says:
=if(a6=a5,"duplicate","")

Filter on "duplicate"
Select visible cells alt+;
Delete the rows

Resort by the number index

Done.

Actually the if statement should be a5=a6 so that the very last account is not the duplicate. (Assuming column a has the account number).

Either that or sort by account and have largest index number at the top.

 

[Jupitus]

Filter on "duplicate"
Select visible cells alt+;
Delete the rows

That is an excellent shortcut!