Delete Duplicated Row, based upon number of Cells where data is available.

[JamieW]

Hi All,

Please see attached Spreadsheet.

Normally, I would try to solve a VBA problem myself and post if I cannot find the answer; however in this case I do not even know how to get started.

Looking in the attached.

1. I have done a COUNTIF Formula (Column AT), to identify if there is a duplicate in Column 'O'.
2. In Column AU, I have counted the number of times relevant data is apparent in a row.
3. Looking at Rows 17 & 18, you can see a duplicate Container Number. I want to remove the one that has the lowest number in Column AU. In this case, Row 18.
4. The data may not always be sorted by Container Number and these could be split all over.

If anyone could assist on this then I would be grateful. This needs to be completed using a Macro; as this is completed daily and would be labour intensive if completed manually.

Thanks,
J

[JBeaucaire]

Assuming only 1000 rows of data to deal with for now, here's a fast manual process that will still keep you in visual control.

1) Put this array formula in AV2:

=IF(AT2=1, "Keep", IF(MAX(IF($O$2:$O$1000=O2,$AU$2:$AU$1000, 0))=AU2, "Keep", "Delete"))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

If the array is properly activated and you then COPY THAT CELL DOWN column AV, all rows will say "Keep" except row18. If you get that result, you're good to go on activating the array.

2) Scan visually down the data and make sure you're pleased with the results... and only see "delete" on the rows that should go away.

3) Filter column AV for "Delete" only

4) Delete all visible rows.

[JamieW]

Thanks Jerry,

Appreciate the help; however other aspects of the data cleansing are completed by Macro and I wanted to be able to tie this all together within the Macro. IF I don't get any other responses then I will look at coding your formula and process into the Macro.

[JBeaucaire]

I would code that exact process into a macro.

Please Login or Register  to view this content.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.