google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-querygs-vlookup

How to add and highlight rows on input of particular column in Google Sheet

In the attached google sheet.

Google Sheet - https://docs.google.com/spreadsheets/d/1KxqaI-GYWur0Knt_bShI0GURucqU_cawu_sCoG_8Xlc/edit?usp=sharing

I need to execute the following two operations on input.

  1. Add same number of rows against which ID if any user put the value as 1 in column K.

For Example, If I input 1 in column K for ID = ID_3 which has 5 rows, I want to append that five rows below where the last instance of ID_3 and in appended rows all the values should be the same as ID_3 except column C which will now be ID_3_Append, column K and O which should be blank for appended rows.

  1. If someone input value as 1 in column M, We need to check for which ID it belongs and look for that id in column E, F, and G and highlight rows with red color if ID against which user has provided input as 1 is available in column E, F, and G.

  2. If someone adds value as 1 in change type, I need to update the column B to I in the Test_1 sheet from Put_list but want to keep the Mark Status for common ID (if it has any Mark Status) between earlier Test_1andPut_list` unchanged. Also, we need to highlight the dependent rows accordingly.

Once we update column B to I we need to change the Name from 'Call' to 'Put'.

Solution

  • use:

    =ARRAYFORMULA(QUERY({QUERY({Test_1!A:O; 
 IFERROR(IF(IFNA(VLOOKUP(Test_1!C2:C, SORT({Test_1!C2:C, Test_1!K2:K}, 2, 0), 2, 0))=1, 
 {Test_1!A2:A, Test_1!B2:B, Test_1!C2:C&"_Append", Test_1!D2:J, Test_1!X2:X*0, 
  Test_1!L2:L, Test_1!X2:Y*0, Test_1!O2:O}, 
 {"","","","","","","","","","","","","","",""}), 
 {"","","","","","","","","","","","","","",""})}, 
 "where Col1 is not null and not Col3 matches '"&
 TEXTJOIN("|", 1, "×", UNIQUE(IF(IFNA(VLOOKUP(Test_1!C2:C, 
 SORT({Test_1!C2:C, Test_1!N2:N}, 2, 0), 2, 0))=1, Test_1!C2:C, )))&"' order by Col3", 1);
  
 IF(LEN(TEXTJOIN("|", 1, 
 UNIQUE(IF(IFNA(VLOOKUP(Test_1!C2:C, SORT({Test_1!C2:C, Test_1!N2:N}, 2, 0), 2, 0))=1, Test_1!C2:C, ))))>0, 
 QUERY({IF(Put_list!A2:A="",,IFERROR(Put_list!A2:A*1, "Put")), Put_list!A2:H, 
 IFNA(VLOOKUP(Put_list!B2:B&"♦"&COUNTIFS(Put_list!B2:B, Put_list!B2:B, ROW(Put_list!B2:B), "<="&ROW(Put_list!B2:B)), 
 {Test_1!C2:C&"♦"&COUNTIFS(Test_1!C2:C, Test_1!C2:C, ROW(Test_1!C2:C), "<="&ROW(Test_1!C2:C)), Test_1!J2:O}, {2,3,4,5,6,7}, 0))}, 
 "where Col3 matches '"&TEXTJOIN("|", 1, 
 UNIQUE(IF(IFNA(VLOOKUP(Test_1!C2:C, SORT({Test_1!C2:C, Test_1!N2:N}, 2, 0), 2, 0))=1, Test_1!C2:C, )), 
 UNIQUE(IF(IFNA(VLOOKUP(Test_1!C2:C, SORT({Test_1!C2:C, Test_1!N2:N}, 2, 0), 2, 0))=1, Test_1!C2:C&".+", )))&"'"), 
 {"","","","","","","","","","","","","","",""})}, "where Col1 is not null order by Col3", 1))

    enter image description here

    enter image description here

    step-by-step transcript:

we start with array of {C, K} columns that we sort based on K column so if K column contains 1 then it will be moved up                                                 
this is convinient for VLOOKUP coz it will always look for 1st unique value eg. exactly wat we need if our array is sorted                                                  
so we vlookup C values to match our sorted array and return column K for all same IDs                   2 stands for 2nd column from sorted array and 0 stands for "exact match"                                
if no match is found vlookup will output #N/A error so we wrap it into IFNA - then if no match is found vlookup will output empty rows                                                  
then we put this into IF statement... if our vlookup outputs 1, we output our columns (again in {array} form                                                    
if our vlookup outputs 0 then we output array of 15 empty cells in a row {"","","", .....}                  this is because we use arrays {} and all ranges in arrays needs to be of same size                              
                    our table has 15 columns so if some error would happen the we would get error in one single cell aganist our 15 columns                                 
                    this way we avoid "array_literal error" having {15 columns; 15 cells in a row which is also 15 columns}                             
                    ; semicolon puts these two arrays/ranges under each other while , comma will put then next to each other                                
so if vlookup results in 1 then we assemble our array with ranges... A, B columns are same then we append to C column the phrase "_Append" with &                                                   
D:J columns are same... then we force column of zeros by multipling random empty column (X) with 0                      etc.                            
then again we use {"","","", ....} within IFERROR to deal with any possible error at this point                                                 
next we put all written above under our whole range Test_1!A:O and we wrap it into QUERY where we state to filter out all empty rows where Col1 is empty and 2nd condition of our query states                                                  
that Col3 of our array cant match our regex pattern which we assemble with TEXTJOIN formula                                                 
    | stands for "or" in regex. 1 in textjoin means "all non empty cells". then we use × (unique symbol) in case textjoin would output empty cell on its own resulting in some error somewhere                                              
    again we perform same vlookup, same ifna and same IF but now we output only C column and we are interested only into UNIQUE values                                              
then within the query we sort / order by Col3 our table and 1 at the end stands for "header rows"