Google Sheets Merge rows based on duplicates and update cell value

0
votes

Hey Guys I have tried to find a couple ways to do this and am teaching myself as much as i can but i got great help for another question hope i can get some assistance here as well.

Snip of my sheet

I am wanting column C to check for duplicates merge the duplicate rows and change cell data for Status to say "complete".

Any suggestions, solutions or pointing in the right direction would be amazing

EDIT Editable link. https://docs.google.com/spreadsheets/d/1EbiSXidEB-IxrrPkoyDPQfPtcgThBb1fZWH_ePi4F7Q/edit?usp=sharing Thanks guys

3
google-sheetsgoogle-sheets-formula
Very possible, but tough to demonstrate in the abstract. Please share a (preferably editable) sample sheet. - MattKing
added an editable link :) thanks for the help. - CubbyG4M3R
Sheet is not accessible... - MattKing
fixed i think sorry the first one was gsuite - CubbyG4M3R

3 Answers

1
votes

to merge based on 3rd column try simple SORTN function:

=SORTN(A2:D, 9^9, 2, 3, 0)
0
votes

I made a new tab called MK.Idea and put the following formula in E1. Does this get you what you're after?

=ARRAYFORMULA({D1;IF((LEFT(D2:D)="C")*ISEVEN(COUNTIFS(C2:C,C2:C,A2:A,"<="&A2:A)),"Complete",D2:D)})
0
votes

Here the formula, it will do although the data are not consecutive:

=arrayformula({Sheet1!A1:D1;Query({SORT({row(Sheet1!A2:A9),Sheet1!A2:C9},4,true,2,true),if(transpose(split(join(",","1," & rept("0,",QUERY(Sheet1!C2:C9,"select Count(C) group by C label count(C) ''")-1) ),",",true,true))=1,query(Sheet1!C2:D9,"Select D order by C"),"Complete")},"Select Col2, Col3, Col4, Col5 order by Col1")})

Another way:

={Sheet1!A1:D1;FILTER({Sheet1!A2:C,if(MOD(COUNTIFS(Sheet1!C2:C,Sheet1!C2:C,row(Sheet1!C2:C),"<=" & ROW(Sheet1!C2:C)),2)=1,Sheet1!D2:D,"Complete" )},Sheet1!C2:C<>"")}