Search code examples
exceldelete-row

Query to remove rows based on value of a column

I'm having a file with data as showing in the screenshot:

enter image description here

Need to build a query that will remove all rows with the same search ref, provided at least one row has a non-zero value. From the particular screenshot, all highlighted rows should be deleted, since there is at least one entry with Order_Placed=1.

Does anyone have an idea on whether same feasible?

Have applied a workaround with filtering out the rows with Order_Placed=1 and then vlookup the whole table, but would like a query like solution

Thank you in advance for your assistance!

Solution

  • Power Query Solution

    Lets take a sample data like yours

    enter image description here

    • select your data
    • Data tab click get & transform then from table
    • a power query window will be opened

    enter image description here

    • create 2 new columns say dat and dummy by group by like this

    enter image description here

    • expand dat to get like this

    enter image description here

    • close and load data back to excel

    enter image description here

    Now you can easily filter out data by dummy column.