I have this data in google sheets and the name can be more or less based on the data I want to convert it into separate column.

Name	Note
Josh	anything1
Mike	anything2
Peter	anything3
Nicole	anything4
Josh	anything5
Mike	anything6
Peter	anything7
Nicole	anything8
Josh	anything9
Mike	anything10
Peter	anything11
Nicole	anything12
Josh	anything13
Mike	anything14
Peter	anything15
Nicole	anything16
Josh	anything17
Mike	anything18
Peter	anything19
Nicole	anything20
Josh	anything21
Mike	anything22
Peter	anything23
Nicole	anything24
Josh	anything25
Mike	anything26
Peter	anything27
Nicole	anything28
Peter	anything29
Nicole	anything30
Peter	anything31
Nicole	anything32
Peter	anything33
Nicole	anything34
Peter	anything35
Nicole	anything36

I have this data in google sheets and the name can be more or less based on the data I want to convert it into separate column.

Name	Note
Josh	anything1
Mike	anything2
Peter	anything3
Nicole	anything4
Josh	anything5
Mike	anything6
Peter	anything7
Nicole	anything8
Josh	anything9
Mike	anything10
Peter	anything11
Nicole	anything12
Josh	anything13
Mike	anything14
Peter	anything15
Nicole	anything16
Josh	anything17
Mike	anything18
Peter	anything19
Nicole	anything20
Josh	anything21
Mike	anything22
Peter	anything23
Nicole	anything24
Josh	anything25
Mike	anything26
Peter	anything27
Nicole	anything28
Peter	anything29
Nicole	anything30
Peter	anything31
Nicole	anything32
Peter	anything33
Nicole	anything34
Peter	anything35
Nicole	anything36

This will be the last result.

Name	Note	Name	Note	Name	Note	Name	Note
Josh	anything1	Mike	anything2	Peter	anything3	Nicole	anything4
Josh	anything5	Mike	anything6	Peter	anything7	Nicole	anything8
Josh	anything9	Mike	anything10	Peter	anything11	Nicole	anything12
Josh	anything13	Mike	anything14	Peter	anything15	Nicole	anything16
Josh	anything17	Mike	anything18	Peter	anything19	Nicole	anything20
Josh	anything21	Mike	anything22	Peter	anything23	Nicole	anything24
Josh	anything25	Mike	anything26	Peter	anything27	Nicole	anything28
				Peter	anything29	Nicole	anything30
				Peter	anything31	Nicole	anything32
				Peter	anything33	Nicole	anything34
				Peter	anything35	Nicole	anything36

Your help will be much appreciated.

I tried using the 4 filter function but its result was not dynamic first I define the name then filter function come with result.

• google-sheets
• google-sheets-formula
• formula

Share Improve this question Follow edited Feb 21 at 6:04 HSHO asked Feb 21 at 5:29 HSHOHSHO 53744 silver badges2020 bronze badges

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

Using Filter Dynamically and Stack it

Your Filter approach is correct, dynamically running through it you use iterators of Google Sheets like Reduce like what I have used. Add a Choosecols to remove the additional array added on by the Reduce function. Sequence is just for generating the number of columns that was supposed to be in the array.

Try this approach

=CHOOSECOLS(REDUCE(,UNIQUE(A2:A), LAMBDA(a,c, IFNA(HSTACK(a,FILTER(A2:B, A2:A = c)),""))), SEQUENCE(1, COUNTA(UNIQUE(A2:A))*2, 2, 1))

Result:

Josh	anything1	Mike	anything2	Peter	anything3	Nicole	anything4
Josh	anything5	Mike	anything6	Peter	anything7	Nicole	anything8
Josh	anything9	Mike	anything10	Peter	anything11	Nicole	anything12
Josh	anything13	Mike	anything14	Peter	anything15	Nicole	anything16
Josh	anything17	Mike	anything18	Peter	anything19	Nicole	anything20
Josh	anything21	Mike	anything22	Peter	anything23	Nicole	anything24
Josh	anything25	Mike	anything26	Peter	anything27	Nicole	anything28
				Peter	anything29	Nicole	anything30
				Peter	anything31	Nicole	anything32
				Peter	anything33	Nicole	anything34
				Peter	anything35	Nicole	anything36

Reference:

Reduce Function

To avoid a thousand blank rows getting added, use tocol(), like this:

=reduce(torow(æ, 2), unique(tocol(A2:A, 1)), lambda(a, c, 
  ifna(hstack(a, filter(A2:B, A2:A = c)))
))

See tocol().