Transposing(?) data from row into multiple shorter columns

[Tao86]

Hello,

I am attempting to create a training book for work. There are sheets for the coaches to log hours in 15 minute increments using a number to show where in the process the trainee is, 1-4 specifically. There is also a cumulative sheet where everything from the various daily sheets would be tracked. But it would be in a shorter 4 cell column that goes multiple rows, and I am unable to change the formatting for those sheets due to offline documents. I am trying to figure out a formula that would autofill the cumulative sheet. It would need to be able to skip blank cells. Also sometimes the numbers do drop back down if the coach thinks the trainee for whatever reason isn't retaining things as well that day so it would need to accurately reflect the numbers listed in the rows, not be an average.

I have tried transpose, index, lookup, and probably more that I've forgotten. Is it possible to do this using a formula or do I have to do VBA?

 

[arun_eagertolearn]

Hi

Requesting some clarifications

Can you please let know

Which of the tables is the summary and which is the individual coach's entry
What do the four rows in the second table signify

1,2,3,4 etc in the tables are the trainees, I assume

 

[Tao86]

Hi

Requesting some clarifications

Can you please let know

Which of the tables is the summary and which is the individual coach's entry
What do the four rows in the second table signify

1,2,3,4 etc in the tables are the trainees, I assume

The top table would be a coach's entry for the day with each 4 boxes essentially being an hour. The bottom table would be a cumulative tracker, and again 4 cells would be an hour. Basically how much time on that particular task the trainee has managed. The numbers represent how well the trainee understands the task.

 

[arun_eagertolearn]

Hi Tao

Sorry for the late reply
My understanding is
The four cells in bottom (cumulative) table which make an hour
1,2,3,4,5,6,7 in the bottom (cumulative) table signify hours

Basis this, please find below

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1			Coach entry
2			15 min	15 min	15 min	15 min	15 min	15 min	15 min	15 min	15 min	15 min	15 min	15 min	15 min	15 min	15 min	15 min	15 min
3	For trainee 1	task 1	2	2					2	3			2	3			2	3
4		task 2									2				2				2
5		task 3			2	2
6		task 4					2	2				2				2
7		task 5
8
9
10			Cum tracker
11		Hours		1	2	3	4	5	6	7
12	For trainee 1	task 1(3 hr min)		2	2
13				2	3
14	Task name	task 1		2	2
15	row no	3		3	3
16	Last entry col	Z	Index Helper
17				1	5	9
18				2	6	10
19				3	7	11
20				4	8	12
Sheet1

Cell Formulas
Range	Formula
D12:F15	D12	=IFERROR(VALUE(MID(TEXTJOIN("",,INDIRECT("C"&$B$15&":"&$B$16&$B$15)),D17,1)),"")
B14	B14	=LEFT(B12,4)&" "&IFERROR(MID(B12,6,2)*1,MID(B12,6,1)*1)
B15	B15	=ROW(B3)-1+MATCH(B14,B3:B7,0)
F17:F20	F17	=E17+4

 

[Tao86]

I don't understand how you got that formula. Could you explain it to me? I also don't understand why the other formulas are included?

For the first formula, the iferror part skips the blank cells correct? I'm not familiar with any of the other formulas.

 

[arun_eagertolearn]

Hi Tao

First, the task no (task 1, task 2 etc) have been identified, along with the row in which the tasks are there
These are the formulae in B14 and B15 respectively

Next, for a specific task (task 1 in this case), INDIRECT takes input from B15 (the relevant row no, 2 in this case) to find the cells

The cells as identified by INDIRECT have been concatenated using TEXTJOIN, so that the blank entries get skipped

The MID section of the formula counts the order in which the numbers appear (skipping the blanks- which has been done by TEXTJOIN)

The index helper (row 17 to 20) gives the input for which entry has to be taken---> 1,2,3....

VALUE is for converting this into a number
In case an entry has not been made (eg: we are looking for the 9th 15 min slot, but the corresponding entry is blank), VALUE will return an error
In order to avoid that errror, IFERROR has been put

Hope this helps