I have been using this below formula to remove duplicates based on 2 columns and further condition are that;

If column B is blank then the row remains same and kept.

If Col B is not blank, then remove duplicates based on col A and Col B together.

Always keep the last row while removing duplicate not the first.

But my formula is not working i do not know why.

=ArrayFormula(sort(unique(sort({'raw data'!A2:B37,vlookup('raw data'!A2:A37&'raw data'!B2:B37,sort({'raw data'!A2:A37&'raw data'!B2:B37,'raw data'!C2:H37},row('raw data'!H2:H37),0),{2,3,4,5,6,7},0)},row('raw data'!A2:A37),0)), 
Array_Constrain(row('raw data'!A2:A37),counta(query(unique(sort({'raw data'!A2:B37,vlookup('raw data'!A2:A37&'raw data'!B2:B37,sort({'raw data'!A2:A37&'raw data'!B2:B37,'raw data'!C2:H37},row('raw data'!H2:H37),0),{2,3,4,5,6,7},0)},row('raw data'!A2:A37),0)),"select Col1")),1),0))

Your help will be greatly appreciated.

Sheet Link

CodePudding user response：

Try:

=arrayformula(query({
filter({row('raw data'!A:A),'raw data'!A:H},countifs('raw data'!A:A&'raw data'!B:B,'raw data'!A:A&'raw data'!B:B,row('raw data'!A:A),">="&row('raw data'!A:A))=1,'raw data'!B:B<>"");
filter({row('raw data'!A:A),'raw data'!A:H},'raw data'!B:B="",'raw data'!A:A<>"")
},"select Col2,Col3,Col4,Col5,Col6,Col7,Col8 where Col2 !='' order by Col1",1))