Taking data from columns and rows to create only rows whilst keeping multiple ID reference

Hi

I'm not sure if the title really explains the problem, but here goes:

Attached is a subset of data (only a very small subset as there are actually 70 rows per case). There are two sheets. The first sheet "example of raw data" is a sample of what I have extracted for this particular exercise. The second sheet "what I want" is how I want to make it look. So what I want to do is:

For each case (i.e. ID) take the 6 response entries (i.e. 1 or 0) for each case from OneSpan.ACC in the first sheet and populate B1T1 to B1T6 in the second sheet (the first value going to B1T1, the 2nd to B1T2 etc). Then move to column TwoSpan.ACC in the first sheet and populate B2T1, B2T2 etc in the second sheet with those value, etc.

Obviously the issue is that in sheet 1, for each case, there are seven columns (hence B1,B2,B2 etc in sheet 2) plus six rows in each (hence T1,T2,T3 etc in sheet 2) so a simple transpose won't work. I have tried formulas but it becomes too complicated as I don't seem to be able to build a logical flow for the cell references as we are not referencing data all in one row or all in one column.

See attached. sample for Sean2.xlsx
Hope this is what you wanted.

Hi jewelsharma.

Thank you!. Yes, this is what I want. Do I just copy that code across all cells in the second sheet? I have 6 files with 92 cases in each and 70 cases per record.

Hope it's that simple!

Rebecca

So long as the structure of all other cases and files is exactly same as this, it should work fine.
Good luck

Thank you so much! Life saver!

Hello, again!
Quick question. I am applying this formula to the other files. In a couple of them there are a different number of blank rows between subjects (id). In the file you so kindly helped me with, there were 3 blnk rows at the start of each new subject. In some of the other files, there are 6 blank rows. Are you able to tell me what I would need to change in the formula to account for this. I cave cut and paste the formula below if that is easier.
Thank you once again.

=OFFSET('Raw Data'!$A$1,MATCH($B2,'Raw Data'!$C:$C,0)+((--RIGHT(LEFT(C$1,2),1)-1)*6)+1 + (--RIGHT(C$1,1)),(--RIGHT(LEFT(C$1,2),1)+2))

try the following formula:

Formula:

Please Login or Register  to view this content.

Basically, the 4 above = No. of blanks rows - 2. Initially it was 1, as your sample had 3 blank rows. The rest of the formula should remain unchanged.

HTH!

Hi!

I don't suppose you fancy helping me out again do you? I tried myself but I just can't work it out. It's a transpose problem again similar to the last. I, again, have 6 response entries per item for each case (subject). Except this time, I want to do the transposing within the same sheet. In the attached you can see I have manually transposed the first 9 cases taking the first 6 rows for the specific subject in column M and transposing them across columns M to R. The taking the first 6 rows for that subject in column S and transposing across S:X etc up to column AQ (transposing AQ:AW). I have 6 files with 92 cases in each (again) so was wondering if you (or anyone!) can help me with the formula.