Selecting cells for every fourth column in a single row

%3CLINGO-SUB%20id%3D%22lingo-sub-2005367%22%20slang%3D%22en-US%22%3ESelecting%20cells%20for%20every%20fourth%20column%20in%20a%20single%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2005367%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EDoes%20anyone%20know%20how%20to%20get%20values%20evenly%20spaces%20across%20on%20row%20(so%20in%20the%20same%20row%20but%20different%20columns%20(every%204th%20column))%20selected%20and%20copied%20so%20that%20they%20now%20appear%20below%20each%20other%20in%20one%20column%3F%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20analyse%20the%20data%20of%20a%20large%204%20part%20survey.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20surveys%20each%20contain%2065%20items%20(from%20A2%3AA66).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEach%20participant%20answered%204%20surveys%2C%20so%20that%20the%20answers%20of%20one%20person%20are%20displayed%20in%20the%20Excel-sheet%20as%3A%3C%2FP%3E%3CP%3Eperson%201%20%3D%20B2%3AB66%2C%20C2%3AC66%2C%20D%3A2%3AD66%2C%20%26amp%3B%20E2%3AE66.%3C%2FP%3E%3CP%3EPerson%202%20%3D%20F2%3AF66%2C%20G2%3AG66%2C%20H2%3AH66%2C%20%26amp%3B%20I2%3AI66.%3C%2FP%3E%3CP%3EPerson%203%20%3D%20J2%3AJ66%20....%20etc%20for%20a%20total%20of%20128%20respondents.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%2066th%20row%20all%20the%20average%20scores%20of%20that%20column%20are%20found.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20been%20trying%20to%20calculate%20the%20difference%20scores%20of%20the%20averages%20of%20the%20last%20two%20surveys%20by%20extracting%20all%20the%20data%20points%20corresponding%20to%20what%20for%20person%201%20would%20be%20D66%20%26amp%3B%20E66%20and%20for%20the%20next%20people%20would%20be%20H66%20%26amp%3B%20I66%20in%20one%20list%20and%20then%20simply%20subtracting%20the%20column%20from%20the%20other.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20seen%20how%20this%20can%20be%20done%20for%20evenly%20spaced%20values%20in%20one%20column%20but%20I%20cannot%20figure%20out%20how%20to%20do%20it%20when%20the%20values%20are%20evenly%20spaced%20cells%20in%20one%20row%20instead.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20I%20am%20a%20bit%20time%20pressed%20I%20would%20rather%20not%20do%20this%20manually%20so%20if%20anyone%20knows%20how%20to%20get%20values%20evenly%20spaces%20across%20on%20row%20(so%20in%20the%20same%20row%20but%20different%20columns%20(every%204th%20column))%20selected%20and%20copied%20so%20that%20they%20now%20appear%20below%20each%20other%20in%20one%20column%2C%20i'd%20be%20immensely%20grateful!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2005367%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2005413%22%20slang%3D%22en-US%22%3ERe%3A%20Selecting%20cells%20for%20every%20fourth%20column%20in%20a%20single%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2005413%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F907883%22%20target%3D%22_blank%22%3E%40OskarONL%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20copy%20the%20row%20and%20transpose%20it%20to%20columns%3CBR%20%2F%3Ehight%20the%20row%20--%26gt%3B%20%3CSTRONG%3ECopy%3C%2FSTRONG%3E%20--%26gt%3B%20go%20the%20place%20where%20you%20want%20to%20paste%20it%2C%20and%20%3CSTRONG%3Eright%20click%3C%2FSTRONG%3E%20--%26gt%3B%20%3CSTRONG%3Especial%20paste%3C%2FSTRONG%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20the%20window%20that%20will%20appear%20tick%20the%20%3CSTRONG%3Etranspose%3C%2FSTRONG%3E%20option%20on%20the%20right%20down%20of%20the%20windows.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20all%20the%20cells%20in%20the%20row%20have%20value%20(B66%2CC66%2CD66)%20or%20just%20the%20last%20cell%20E66%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi All,

 

Does anyone know how to get values evenly spaces across on row (so in the same row but different columns (every 4th column)) selected and copied so that they now appear below each other in one column?

 

I am trying to analyse the data of a large 4 part survey. 

 

The surveys each contain 65 items (from A2:A66). 

 

Each participant answered 4 surveys, so that the answers of one person are displayed in the Excel-sheet as:

person 1 = B2:B66, C2:C66, D:2:D66, & E2:E66.

Person 2 = F2:F66, G2:G66, H2:H66, & I2:I66.

Person 3 = J2:J66 .... etc for a total of 128 respondents. 

 

In the 66th row all the average scores of that column are found. 

 

I have been trying to calculate the difference scores of the averages of the last two surveys by extracting all the data points corresponding to what for person 1 would be D66 & E66 and for the next people would be H66 & I66 in one list and then simply subtracting the column from the other.

 

I've seen how this can be done for evenly spaced values in one column but I cannot figure out how to do it when the values are evenly spaced cells in one row instead. 

 

As I am a bit time pressed I would rather not do this manually so if anyone knows how to get values evenly spaces across on row (so in the same row but different columns (every 4th column)) selected and copied so that they now appear below each other in one column, i'd be immensely grateful!!

3 Replies

Hi @OskarONL 

 

You can copy the row and transpose it to columns
hight the row --> Copy --> go the place where you want to paste it, and right click --> special paste  

On the window that will appear tick the transpose option on the right down of the windows.

 

Do all the cells in the row have value (B66,C66,D66) or just the last cell E66?

Please edit your post & upload the sample data along with expected output, help us to understand your need to fix it !

@OskarONL 


=TRANSPOSE(FILTER(B2:SR66,MOD(COLUMN(B2:SR66)-2,4)=0,""))

where =0 determent what column you want (0,1,2,3)

But, good luck in convincing yourself that your data is right. You wont see any identification of participant or question nr

This gives the questionGroup/participant (if that's in row one). But not the question number

=TRANSPOSE(FILTER(A1:SR66,MOD(COLUMN(A1:SR66)-2,4)=0,""))