Google Sheets - transpose column data in groups into rows

Question

I am trying to transpose data from column A to single rows. The original data has 3 rows for each name. But there could be either 1 or several jobs for each day. Each day needs to be treated separately, but this maybe best handled by manually adding at the beginning of each day.

This is for a fortnightly timesheet, therefore the number of rows in unpredictable.

The 1st image is my original data. the 2nd is the desired end result.

The data is to transposed to any blank rows in columns b, c, d, e, as long as there are no blank rows, I can then reference them with my formulas. to place the information into the appropriate cells within the timesheet. I already have working formulas to do this.

the transpose section is what I need help with

Here is a link to my file https://docs.google.com/spreadsheets/d/1PhuFXDB2H1c9ua6szjhJEgJR91yXHhZAmn_2UGOBvIo/edit?usp=sharing

Please share a link to your sheet or to a copy of your sheet containing at least as much data as is shown in your images above. This will allow the volunteers here to have a starting point from which to work. — Erik Tyler, Jul 18 '21 at 04:57
Kris, what you show in your post image for your original data setup does not match what is actually in your sheet. Your post shows the original data in TWO columns, with spaces and labels in Column A, whereas your sheet shows only the Column-B data from your image jammed together without labels or spaces into a single Column A. Please either edit your sheet to match your post image, or replace your post image with an image that matches your sheet setup. Please also indicate clearly in your sheet where the output is supposed to go. — Erik Tyler, Jul 18 '21 at 05:27
I also notice that in your post, your image shows the times as real numerical time values in separate cells with clear labels ("start time" and "end time" to the left), whereas your actual sheet shows the times as a single string, e.g., "6:30 - 8:45". So in short, what you show in your post doesn't structurally match what you have in your sheet very well at all. — Erik Tyler, Jul 18 '21 at 05:38
I see you have now changed both images in the post to something entirely different. I had worked out a solution to the problem as originally posted, and I can't invest more time into an entirely new solution. But you now show that you DO NOT want any dates in your final output (i.e., the date is crossed out). This doesn't seem to make sense, as it will render listed times useless not knowing the date. For the sake of other volunteers who may choose to invest in this moving forward, please carefully think through what you actually want and depict it accurately both here and in the sheet. — Erik Tyler, Jul 18 '21 at 06:29
In addition, you have still not indicated where you want the output to go at all. And the location for the output is not evident on your sheet. Again, for the sake of other volunteers who may read this (and to prevent this post from being closed due to lack of clarity), you must be clear and thorough about your goals. — Erik Tyler, Jul 18 '21 at 06:32

score 4 · Accepted Answer · answered Jul 18 '21 at 10:06

4

try:

=ARRAYFORMULA({QUERY(IF(REGEXMATCH(A12:A, "\d+:\d+.*"), VLOOKUP(ROW(A12:A), 
 IF(IF(IFERROR(RIGHT(A12:A, 4)*1)>2000, A12:A, )<>"", {ROW(A12:A), 
 IF(IFERROR(RIGHT(A12:A, 4)*1)>2000, A12:A, )}), 2, 1), ), "where Col1 is not null"),
 QUERY(IF((IFERROR(RIGHT(A12:A, 4)*1)>2000)+(REGEXMATCH(A12:A, "\d+:\d+.*")),, A12:A), 
 "where Col1 is not null skipping 2"), 
 QUERY(QUERY(IF((IFERROR(RIGHT(A12:A, 4)*1)>2000)+(REGEXMATCH(A12:A, "\d+:\d+.*")),, A12:A), 
 "where Col1 is not null offset 1", 0), "skipping 2"), 
 FILTER(A12:A, REGEXMATCH(A12:A, "\d+:\d+.*"))})

answered Jul 18 '21 at 10:06

player0

124,011
12
67
124

thank you for your help when i put your formula into cell B13 i get an error message saying that i am missing an open parentheses then #ref! error in cell – Kris12 Jul 18 '21 at 22:10
@Kris12 can you share a copy of your sheet? – player0 Jul 18 '21 at 22:13
https://docs.google.com/spreadsheets/d/1PhuFXDB2H1c9ua6szjhJEgJR91yXHhZAmn_2UGOBvIo/copy hope this is correct as i said i am new to google sheets and i have never done this before – Kris12 Jul 19 '21 at 06:18
@Kris12 see: https://docs.google.com/spreadsheets/d/1DUBvpiVOUgfCvwXXuaRPyvgW9kYBzsE8ye6pXZikSGc/edit#gid=0 – player0 Jul 19 '21 at 06:43
1

Figured out what i was doing wrong! The problem was that I already had some data in one of the cells, therefore your formula would not work. it is perfect thank you so much for your help. your solution works perfectly – Kris12 Jul 19 '21 at 07:50

Aresvik · Answer 2 · 2021-07-18T10:19:26.243

3

If you want a solution with a formula in one cell only, try:

=arrayformula({"date","name","job type","start - end time";query(split(flatten(query(iferror(datevalue(A12:A),),"where Col1 is not null",0)&split(flatten(split(textjoin(char(9999),1,if(regexmatch(to_text(A12:A),":+.*-+"),A12:A&char(9998),if(iferror(datevalue(A12:A),)<>"",char(10001),A12:A))),char(10001))),char(9998))),char(9999)),"where Col2 is not null",0)})

edited Jul 18 '21 at 10:19

answered Jul 18 '21 at 10:09

Aresvik

4,484
1
5
18

thank you for you help. when i put your formula into B12 I get a #Value! error – Kris12 Jul 18 '21 at 21:59
See this sheet. I think the issue with your document is to do with the date format? https://docs.google.com/spreadsheets/d/1bV73k48FhmG6NXyJb93NXfjTTyVxAtwgmZs54QzyaYU/copy – Aresvik Jul 19 '21 at 07:56
thank you for your efforts, your sheet works perfectly. but i still get the following error when I place the formula in my sheet with dates dd/mm/yyy There is obviously some difference with my sheet. Error is #Value! popup error message "In Array_Literal, an array Literal was missing values for one or missing rows" – Kris12 Jul 19 '21 at 10:01

score 0 · Answer 3 · answered Jul 18 '21 at 09:18

in put B12 :

=IFERROR(if(FIND("2021",A12)>=1,0,""),B11+1)

in put C12 :

=if(B12=0,A12,C11)

in D12 :

=if(mod(B12,3)=1,TRANSPOSE(A12:A14),"")

and drag downwards. Then in F12 put :

=FILTER(C:F,F:F<>"")

and drag downwards.

Idea : use transpose() with a conditional counter. use (F12)filter to remove blank.

Please if it works/understandable/not.

Google Sheets - transpose column data in groups into rows

3 Answers3

Linked