0

So I have a spreadsheet that was generated by Google Forms. I would like to gather all "Usernames" from column B2:B and put it in into a single row, it will take the column C2:C that has the username the same as that single row and put it in a list, so basically sum up all usernames and get row C2:C and put it in a list, so there will only be one row for each username. Every time there is a new username in the B2:B column, it will make another row in a non-occupied row below the formula so it will basically gather all usernames and get the column to the right (C2:C) and put that in a list. I know this seems very confusing but if you read carefully you will understand. I will probably just end up deleting this because no one will probably understand.

Here's what I am meaning... https://i.stack.imgur.com/CZMa1.jpg (Imgur collection)

Please note I don't want to use the Timestamp column so ignore it in the formula.

Jacob Gunther
  • 351
  • 5
  • 14
  • It looks like merge gridview cells, please see this http://stackoverflow.com/questions/16147963/gridview-with-merged-cells – Tony Dong Dec 09 '16 at 00:15
  • @TonyDong Well, it helped a bit... I searched "Merge Cells in Google Sheets Formula" but I couldn't really find what I was looking for. Thanks for the help anyway! – Jacob Gunther Dec 09 '16 at 00:25

1 Answers1

0

In (say) F2 just put

=B2

and in G2 just put

=C2

Then in F3

=ArrayFormula(IF((ROW()-MAX(ROW(F$2:F2)*(F$2:F2<>"")))<COUNTIF(B$2:B$10,INDEX(F$2:F2,MAX(ROW(F$2:F2)*(F$2:F2<>""))-ROW(A$1),0)),"",INDEX(B$2:B$10,MATCH(0,COUNTIF(F$2:F2,B$2:B$10),0))))

and in G3

=ArrayFormula(INDEX(C$2:C$10,SMALL(IF(B$2:B$10=INDEX(F$2:F3,MAX(ROW(F$2:F3)*(F$2:F3<>""))-ROW(A$1),0),ROW(B$2:B$10)-ROW(A$1)),ROW()-MAX(ROW(F$2:F3)*(F$2:F3<>""))+1),0))

and pull down.

enter image description here

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37