LaVOZs

The World’s Largest Online Community for Developers

'; google sheets - Merge Columns without deleting or affecting the rows - LavOzs.Com

Im seeking some experienced advice, as I have been working on a little project to automate how we gather data in my office using Google Sheets(Please note that I can't use add-ons). I'm encountering difficulties in finding a way to merge columns that have the same name, but without deleting/merging the row(because im pulling stats for the different tasks employees handle).

In the example you can see that column A has names that repeat because each individual completes one or more tasks, so my goal would be to find a way to automatically merge the repeating names in column A without affecting the rest of the columns.

I believe it is important to know that the table auto-populates as im currently using a filter function, because I paste all my data in the excel and it filters only my agents names.

Here is the formula that im using in c26:

=FILTER(A3:G22,ARRAYFORMULA(ISNUMBER(MATCH(A3:A22,{"Mary";"Jason";"Ana";"Jen";"Ben";"Helen";"Dan";"Richard";"Breg"},0))))

Please tell me that there is a way to do this!

Here is a link to the example Doc That I made https://docs.google.com/spreadsheets/d/1RsCeHfzzbRsUDnj6UmmCdfzjryp-2xW09UTCx_qfIpA/edit?usp=sharing

Here you can do it, but can't merge by formula:

= ARRAYFORMULA ( 
    ifna ( 
      vlookup (
        ifna ( 
          sort (
            { row(A4:A22) * 
              len ( vlookup ( A4:A22,
                  {"Ana";"Jen";"Ben";"Helen";"Dan";"Richard";"Breg"},1,0)
              )^0}
            ,1,true
          ),""
        )
        , { row(A4:A22), 
            left(A4:A22, 1000 * 
                transpose (
                   split (join("","1," & rept("0,",countif(A4:A22,unique(A4:A22))-1)),",",true,true))
                ),
            B4:G22
          },{2,3,4,5,6,7,8},false
      ),""
    )
  )

enter image description here

Another formula with the same result:

= ARRAYFORMULA ( 
     ifna(vlookup (
          sort (
            { row(A4:A22) * 
              len ( vlookup ( A4:A22,
                  {"Ana";"Jen";"Ben";"Helen";"Dan";"Richard";"Breg"},1,0)
              )^0}
            ,1,true
          ), 
          { row(A4:A22),
            if(transpose(split(join(
               "","1," & rept("0,",COUNTIF(A4:A22,unique(A4:A22))-1)),",",true,true))=1,
               A4:A22,""
            ),B4:G22
          },{2,3,4,5,6,7,8},false),""
     )
  )

After a little bit of discussion on the sheet, this query() will display only one name per agent, but still list all the rows for that agent.

=ARRAYFORMULA(ARRAY_CONSTRAIN(QUERY({Sheet1!A1:G22,IF(COUNTIFS(Sheet1!A1:A22,Sheet1!A1:A22,ROW(Sheet1!A1:A22),"<="&ROW(Sheet1!A1:A22))>1,"",Sheet1!A1:A22)},"select Col8,Col2,Col3,Col4,Col5,Col6,Col7,Col1 where Col1 matches '"&TEXTJOIN("|",TRUE,A2:A)&"' order by Col1 label Col8'Name'",3),9^99,7))
Related
How do I merge two dictionaries in a single expression?
How to merge a specific commit in Git
JPA EntityManager: Why use persist() over merge()?
How to join (merge) data frames (inner, outer, left, right)
How do you merge two Git repositories?
How to merge two arrays in JavaScript and de-duplicate items
Merge / convert multiple PDF files into one PDF
How to undo a git merge with conflicts
What is the difference between `git merge` and `git merge --no-ff`?