Delete Row Based on Row Position

[syedbokhari]

Hi Guys,

I have the row number of some values in a sheet (column E) see picture.

I need to delete the row where the corresponding value sits.

I have used match to give me this as you can see from the picture.

The issue is that the value sometimes can be present in the look up column (column A).

Upon deleting the ROW the match will provide an NA which is perfectly fine as this will indicate there is only one unique value in column A.

Currently the value in column E can also be in A ...

That will indicate that this is the only value present and the corresponding value isn't present anymore.

The final result should look like this.

The data set is much larger then this so I have just shortened it to make it easier (+6000 Rows).

I tried writing this in VBA but my skills aren't up to scratch as I'm still a beginner.

If you guys could help that would be awesome and I would be most grateful. Unfortunately I can't send food across the internet otherwise I would of provide you guys with some lemon cake.

Thanks,

Sy

 

[pizzaboy]

Name your columns like this:

PERSON ID / RELATIVE ID / MATCH / DELETE


Fill data for PERSON ID & RELATIVE ID like you already have.

MATCH COLUMN: =IF(IFERROR(MATCH([@[RELATIVE ID]],[PERSON ID],0),0)>0,MAX([@[PERSON ID]],[@[RELATIVE ID]]),"")
DELETE COLUMN: =IF([@MATCH]=[@[PERSON ID]],"DELETE","")


On any of the formula cells, you can click into the cell entry thing at the top, excel will highlight which cells and columns are getting used so you have an idea of whats happening.

You can rename the columns any way you like after, Excel will update the formulas accordingly.