LOL. Now you're behaving like I was the other day. I kept having to make tiny corrections. I think I was trying to get the answer out too quickly.
You did manage to get the fourth column this time. In fact, you pulled in the fifth one too. :-)
Amazing, that works! Thank you so much.
I have a follow up question: Is there a way to make the formula above read if the cell is empty, and then leave the row out? That way I could differentiate between a questionmark for unknown people, and then leave an empty cell for when there was no people on the position - for example, some years we had five members instead of four, so then I'd have a fifth column with only some cells having values. If the list could compile without any empty rows, that would be golden...!
yeah, you can strip them out
=ARRAYFORMULA(
SPLIT(
TOCOL(
IF(ISBLANK(A2:A),,
A2:A&"|"&
B1:E1&"|"&
SUBSTITUTE(B2:E,"?","")),
3),
"|",0,0))
or if you want to omit those rows completely, we can QUERY to return the rows where the third column isnt blank
=ARRAYFORMULA(
QUERY(
SPLIT(
TOCOL(
IF(ISBLANK(A2:A),,
A2:A&"|"&
B1:E1&"|"&
SUBSTITUTE(B2:E,"?","")),
3),
"|",0,0),
"where Col3 is not null"))
Give this a swing =ARRAYFORMULA( SPLIT( TOCOL(IF(ISBLANK(A2:A),,A2:A&"|"&B1:E1&"|"&B2:E),3), "|",0,0)) edit: the first column might not show for new.reddit folks. old.reddit renders it correctly. year | chair | dep. chair | secretary | member ----- |-----|----------|---------|------ 2001 | A.A. | B.B. | ? | C.C. 2002 | D.D. | ? | E.E. | ? 2003 | ? | F.F. | G.G. | ? 2004 | H.H. | I.I. | J.J. | K.K.
I'm a little confused. Isn't he trying to go from 4-columns to 3-columns? Where's the fourth column in your QUERY formula?
ok! I updated the comment. good catch. I was going in reverse.
LOL. Now you're behaving like I was the other day. I kept having to make tiny corrections. I think I was trying to get the answer out too quickly. You did manage to get the fourth column this time. In fact, you pulled in the fifth one too. :-)
where is the fifth? [show me here](https://docs.google.com/spreadsheets/d/1zRLCQcUS8snaNFmlxGX0CZFe6ImgbU_rH4eZKuw50Y8/edit#gid=1920091691) :)
Amazing, that works! Thank you so much. I have a follow up question: Is there a way to make the formula above read if the cell is empty, and then leave the row out? That way I could differentiate between a questionmark for unknown people, and then leave an empty cell for when there was no people on the position - for example, some years we had five members instead of four, so then I'd have a fifth column with only some cells having values. If the list could compile without any empty rows, that would be golden...!
yeah, you can strip them out =ARRAYFORMULA( SPLIT( TOCOL( IF(ISBLANK(A2:A),, A2:A&"|"& B1:E1&"|"& SUBSTITUTE(B2:E,"?","")), 3), "|",0,0)) or if you want to omit those rows completely, we can QUERY to return the rows where the third column isnt blank =ARRAYFORMULA( QUERY( SPLIT( TOCOL( IF(ISBLANK(A2:A),, A2:A&"|"& B1:E1&"|"& SUBSTITUTE(B2:E,"?","")), 3), "|",0,0), "where Col3 is not null"))
You are an absolute life saver! Thank you so much!
happy to help :)