Delete entire row based on criteria

[chrisellis250]

Hi all,

Attached is some sample data, to show how my data looks.

Basically, there is often data that are "repeats". So for example on sheet "How it currently looks" I have highlighted the pairs, or groupings if more than 2, where the following criteria is met:
- Same number as per column AC
- Call event is "Data" as per column J
- The minutes and seconds are the same as the previous row of data for the same number, or the next row of data for the same number, where they are exactly 1hr apart
- The value in Column AE is H3

For example, as shown in the attached, rows 13 and 18 are highlighted orange in the time, as the times are exactly one hour apart (same minutes and seconds) with the call event being "Data" for both, the value in AC is the same and the operator in AE is H3 - so these values are to be deleted.

Similarly, the data in rows 20 and 26 meet the criteria, so does the data in rows 59, 62, 82, 113, 125 and 143 - where they are all the same minutes and seconds, but where the one before or after is one hour apart.

Ultimately, if the criteria above is met then the rows are to be deleted. Anyone have any ideas how to achieve the above?

Many thanks,

Chris

[MarvinP]

Hi chris,

I think your answer lies in creating a helper column with a CountIFS() formula in it. The range of the Counts hold the top row with a "$" (like C$1:C2) so you are counting from the top. You would build up your 4 criteria in this formula. You would add a second Countifs() to add those that match an hour.

Then you would need some VBA deleting rows with this helper column >1, but starting from the bottom and moving up using a Step of -1.

Hope this helps.

[PCI]

See next code, check and double check.
To be lanched from the sheet to manage

Please Login or Register  to view this content.

[chrisellis250]

Hello PCI,

Massive thanks for your assistance, it works very well. There is one more piece of criteria, which needs to be included with everything else.

This criteria is the following:

- The Cell IDs in column M needs to match, where the data above or below it changes then they do not need to be deleted
- For example if Row 1 showed 12345 in Column M, then the next two rows where the original criteria is met and where the value in Column M was 5678 for both rows, then these 2 rows would be deleted but NOT the first row

If you understand what I mean?

[Sintek]

Another Option...

Please Login or Register  to view this content.

If you understand what I mean?

No please elaborate referencing sample files data...

[PCI]

Let's Use your previous example to explain your new need.
In row 13 and 18 cells in column "C" are highlighted because it fit to the previous needs.
Then you want to add an additional criteria about column "M".
Because value in column "M" is 555562 for the 2 rows we have to delete the 2 rows
Now if for row 18 in column "M" the value is different to 555562 then we have to delete only row18 and keep row 13.
Is it what you want ???

[chrisellis250]

Hi PCI,

So based on feedback I have received after testing, the criteria is now the following:

- Where the number in column AD is the same
- Where the operator in column AE is "H3"
- Where the Call Type in column J is "Data"
- Where the minutes & seconds in Column C are the same

BUT

- If the Cell Identity in Column M changes, then the call is not to be deleted

I have attached another example. Where the cell in column C is green then these are not to be deleted and where they are orange these are to be deleted.

So for example, Call Number 1 to Call Number 7, they meet all of the original criteria BUT the Cell ID in column M has changed from 995462 to 995527 - so these are NOT to be deleted.

Then from Call Number 7 to Call Number 8, they meet all of the original criteria AND the Cell ID in Column M is the same as with call number 7, so call number 8 IS to be deleted but call number 7 is to NOT be deleted, as it is based on a repeat AFTER the occurrence of this cell ID in call number 7.

The same applies to call number 9, but not call number 11 as the Cell ID in column M has changed.

If that makes sense?

[PCI]

See next code
It is not exactly the same result but check

Please Login or Register  to view this content.