I know I just asked a question... but now I am posed with a bigger one. I need to remove duplicates out of a huge file, this will take forever manually. Let me explain:
So I have a huge column of data, the first column is the item number, second column item description, third column month formatted like this:
Item 1 Desc 1 Month 1
Item 2 Desc 2 Month 1
Item 3 Desc 3 Month 1
Item 1 Desc 1 Month 2
Item 2 Desc 2 Month 2
Item 3 Desc 3 Month 2
For some reason a lot of items were input duplicated in each month like this, they aren't sorted near each other though but this is just so you can see:
Item 1 Desc 1 Month 1
Item 1 Desc 1 Month 1
...
Item N Desc N Month 1
Then the month changes, and the process continues. There is no pattern for duplicates from month to month, seems to be random, and there is often more than one duplicate.
The idea is, the item numbers are obviously duplicated several times down the column because they reappear when the month column changes. I don't want to delete these duplicate rows because they hold valuable information. I'd like to delete duplicates only within the same month. Sorry if I'm not clear, I didn't make this spreadsheet so don't blame me for strange formatting! :P
Further columns past the format I showed contain forecast data. Ideally this is exactly what I want to happen:
Check for duplicates in item # column (only where the month column is the same)
Delete row with the least amount of data (The entire row is not duplicated, among the duplicates some rows contain forecast data for more months, I'd like to keep the rows with the most extensive forecast data and delete the others)
Here is the syntax im looking for, but don't know how to implement:
if duplicate AND same value in month column AND less row data than other dupes THEN delete
If someone could solve this for me it would be huge, otherwise I need to spend 10 hours deleting duplicates which will be EXTREMELY disheartening :p. I'll be looking at this regularly so if you need any clarification of the problem just ask.
Bookmarks