Home > Mobile >  How to Combine the Sort, Filter and IFS Formula for Assigning Unique Id
How to Combine the Sort, Filter and IFS Formula for Assigning Unique Id

Time:06-07

Please help me write the correct formula in Google Sheets to achieve the following goal:

I want to sort data found in specific rows within a column, and then, depending on the order they are sorted in, assign a Unique ID to that row plus the 3 rows that follow. This results in a group of 4 rows that are sorted together. The specific rows to sort are 2, 6, 10, 14, 18, 22 (every 4th row). Formula in question will be in cell B2. Please note, we are NOT sorting by the Unique IDs as they are written. Rather, we are assigning a Unique Id depending on the sorted Data.

I have attempted formulas from these tutorials, with no success?

  • enter image description here


    update 1:

    =ARRAYFORMULA({"ID "&ROUNDUP(SEQUENCE(COUNTA(C4:C))/4), 
     QUERY(SORT(C4:D, IF(D4:D="",, VLOOKUP(ROW(D4:D), 
     IF(ISNUMBER(D4:D), {ROW(D4:D), D4:D}), 2, 1)), 1), 
     "where Col1 is not null", )})
    

    enter image description here

    enter image description here

  • Related