Google-sheets – Find the nth occurrence of variable, look at adjacent cell and organise in an array

google sheets

I have a Google spreadsheet with two arrays: The first is a list of ~100 names (in a single column) with the adjacent column being the variable (A,B,C,D,E,F,G,H,I or J). The purpose is to group people together using the variable letters.

The second is a way of sorting those names. I have 10 columns (A–J) with 10 rows each. I'd like to put the people's names into the correct column, but in consecutive rows (individual cells).

The choice of variable for each person is subject to change.

I was thinking it would be something like:

find location of nth "A", offset 1 column to find the name next to any given variable,

and just change nth from 1st – 10th for the row value, and change "A" to "B".."J" for the column.

I've been looking at functions Address, Index, Match but I just can't seem to do it.
Any help or a nudge in the right direction would be greatly appreciated.

Best Answer

To populate group A, you can just use filter(). Let's say you want group A in Col E, then in E2 (or E1) write:

=filter(A1:A, B1:B="A")

And repeat for all the other groups you want to form. Hope that helps ?

Related Solutions

Google-sheets – How to read a multiple values in cell in each row (separated by comma) in a Google Spreadsheet

I have assumed the last two rows are incomplete rather than a guide to your requirement, so would suggest:

=if(iserror(find(C$1,$B2)),"",1)

in the column labelled aaa and the row labelled abc, copied across and down to suit.

Google-sheets – Using a dynamic OFFSET & ISBLANK() function

Here is one way to achieve the desired effect: get the value in the last non-empty cell in column A above the current row.

B2  =OFFSET(A1,-LEN(REGEXEXTRACT(JOIN("|",A$1:A1),"\|*$")),0)

(And propagate down the B column). This works under the assumption that none of your column A cells end with character | (if they are dates, they really shouldn't). Another character could be used instead of |.

What the command does:

JOIN("|",A$1:A1) joins the content of column A, above the current row, separating the values with |.
REGEXEXTRACT(...,"\|*$") extracts only the characters | at the end of the string. (This particular character needs to be escaped with a backslash in a regex.) The idea is that these characters correspond to blank cells that we must jump over.
LEN takes the length of the result, i.e., the number of blank cells at the end.
OFFSET jumps up by that many rows.

Best Answer

Related Solutions

Google-sheets – How to read a multiple values in cell in each row (separated by comma) in a Google Spreadsheet

Google-sheets – Using a dynamic OFFSET & ISBLANK() function

Related Topic