Column To Rows

[Bee008]

Hello all!

Thank you for taking the time to read my problem! I did post this before, however, I have since ran into some issues using the formula that the answers used when it came to carrying the formula down (so thank you very much to those who previously replied- I really appreciate your time).

I have a project I'm working on, but it's really quite big! This section is a small part of it.


What I need to do is to move the data which is in the columns (A-F) into the rows (which I have highlighted in orange). As you'll see, in my example I have shown which data needs to go where.

If anyone can solve this for me and also please explain how it is done, that would be amazing- because I need to record a Macro (as I have 60 of these spreadsheets to do).



(Furthermore, I have a couple of other little bits & bobs on various other parts of the project that I could do with some help with. I am more than happy to pay anyone for their time with this- so if you know anywhere where I can get this service- or anyone who may be interested- please let me know!!)


Bee

example.xlsx

[Glenn Kennedy]

In H2, drag across to I2 and down:
=OFFSET($A$1,(ROWS($1:1)-1)*6,COLUMNS($A:A)-1,,)

in J2, copy down:
=OFFSET($A$1,(ROWS($1:1)-1)*6+1,5,,)

in K2, drag across to M2 and copy down:
=OFFSET($A$1,(ROWS($1:1)-1)*6+COLUMNS($A:B),4,,)

Happy to explain, if you think it gives you what you want....

[ThirdFret]

Hello all!

If anyone can solve this for me and also please explain how it is done, that would be amazing- because I need to record a Macro (as I have 60 of these spreadsheets to do).

I've attached an example of a set of formulas that will provide the desired result.

Now, for the explanation...

First of all, simply inputting a set of formulas while you are recording a macro will likely not produce the desired result, unfortunately. Fortunately, you might be able to copy and paste the set of formulas from one sheet to the next and it should work fine (easier than entering the formulas again and simpler than learning VBA this afternoon).

The formulas I've provided work by using the INDEX function, which returns a cell within a given range by essentially offsetting a number of rows and columns inside that reference. The Name section returns the correct name by looking at the current "Face" value, looking for that number within the column B using MATCH, which returns the position of the correct match within an array. This combination of the two functions is telling Excel to return the cell from column F that is the same number of rows down as the first time each specific "Face" value appears, +1 extra row.

The same logic is applied to FOK, Fam, and TOT by looking in column E for the match of "Face" in column B +2, +3, and +4.

Hope that helps your understanding!

[cbatrody]

Hi,

Another way,

Try the following in J2: (array entered, need to be confirmed by pressing CTRL+SHIFT+ENTER)

=IFERROR(INDEX(F$1:F$390,SMALL(IF(F$1:F$390<>"",ROW(F$1:F$390)),ROW(A2))),"")

In H2:
=IFERROR(INDEX(A$1:A$390,MATCH($J2,$F$1:$F$390,0)),"")

In I2:
=IFERROR(INDEX(B$1:B$390,MATCH($J2,$F$1:$F$390,0)),"")

In K2:
=IFERROR(INDEX(E$1:E$390,MATCH($J2,$F$1:$F$390,0)+1),"")

In L2:
=IFERROR(INDEX(E$1:E$390,MATCH($J2,$F$1:$F$390,0)+2),"")

In M2:
=IFERROR(INDEX(E$1:E$390,MATCH($J2,$F$1:$F$390,0)+3),"")

drag these cells down the rows.

See the attached file.

(Only J2 is array entered, rest all are normal formula)

[ThirdFret]

(Furthermore, I have a couple of other little bits & bobs on various other parts of the project that I could do with some help with. I am more than happy to pay anyone for their time with this- so if you know anywhere where I can get this service- or anyone who may be interested- please let me know!!)

I know you can purchase commercial services through the website if you'd like a little more personalized help. I found a link to an FAQ directly related to commercial services.
http://www.excelforum.com/the-water-...-services.html
Hope that helps!

[José Augusto]

Hi

Try this array formula (1 line) for range H2:J2

Please Login or Register  to view this content.

and this array formula (1 line) for the columns K2:M2

Please Login or Register  to view this content.

Copy down
See the file example (3)(1).xlsx

Regards

[Bee008]

Hi there ThirdFret,

Thanks so much for pointing out that simply recording a macro of me inputting formulas will not work --> you've saved me a lot of wasted time there!

Your explanation makes total sense, thank you. HOWEVER, when trying to 'copy and paste the set of formulas', it doesn't seem to be working.
I've been trying to use your formulas on my real data, copying it over from your workbook, and it keeps saying #N/A.

Any ideas??
Apologies for being such an airhead when it comes to Excel!

(attached is one of my files --> each of the 60 are separate worksheets)P1_Original_Output.xlsxP1.xlsx

[Bee008]

I've attached an example of a set of formulas that will provide the desired result.

Now, for the explanation...

First of all, simply inputting a set of formulas while you are recording a macro will likely not produce the desired result, unfortunately. Fortunately, you might be able to copy and paste the set of formulas from one sheet to the next and it should work fine (easier than entering the formulas again and simpler than learning VBA this afternoon).

The formulas I've provided work by using the INDEX function, which returns a cell within a given range by essentially offsetting a number of rows and columns inside that reference. The Name section returns the correct name by looking at the current "Face" value, looking for that number within the column B using MATCH, which returns the position of the correct match within an array. This combination of the two functions is telling Excel to return the cell from column F that is the same number of rows down as the first time each specific "Face" value appears, +1 extra row.

The same logic is applied to FOK, Fam, and TOT by looking in column E for the match of "Face" in column B +2, +3, and +4.

Hope that helps your understanding!

Hi there ThirdFret,

Thanks so much for pointing out that simply recording a macro of me inputting formulas will not work --> you've saved me a lot of wasted time there!

Your explanation makes total sense, thank you. HOWEVER, when trying to 'copy and paste the set of formulas', it doesn't seem to be working.
I've been trying to use your formulas on my real data, copying it over from your workbook, and it keeps saying #N/A.

Any ideas??
Apologies for being such an airhead when it comes to Excel!

(attached is one of my files --> each of the 60 are separate worksheets)P1.xlsxP1_Original_Output.xlsx

[Bee008]

I know you can purchase commercial services through the website if you'd like a little more personalized help. I found a link to an FAQ directly related to commercial services.
http://www.excelforum.com/the-water-...-services.html
Hope that helps!

Thank you!

[Bee008]

Hi,

Another way,

Try the following in J2: (array entered, need to be confirmed by pressing CTRL+SHIFT+ENTER)

=IFERROR(INDEX(F$1:F$390,SMALL(IF(F$1:F$390<>"",ROW(F$1:F$390)),ROW(A2))),"")

In H2:
=IFERROR(INDEX(A$1:A$390,MATCH($J2,$F$1:$F$390,0)),"")

In I2:
=IFERROR(INDEX(B$1:B$390,MATCH($J2,$F$1:$F$390,0)),"")

In K2:
=IFERROR(INDEX(E$1:E$390,MATCH($J2,$F$1:$F$390,0)+1),"")

In L2:
=IFERROR(INDEX(E$1:E$390,MATCH($J2,$F$1:$F$390,0)+2),"")

In M2:
=IFERROR(INDEX(E$1:E$390,MATCH($J2,$F$1:$F$390,0)+3),"")

drag these cells down the rows.

See the attached file.

(Only J2 is array entered, rest all are normal formula)

Seems to work upon first looks- will test it out on my real data thank you!

[Bee008]

Hi,

Another way,

Try the following in J2: (array entered, need to be confirmed by pressing CTRL+SHIFT+ENTER)

=IFERROR(INDEX(F$1:F$390,SMALL(IF(F$1:F$390<>"",ROW(F$1:F$390)),ROW(A2))),"")

In H2:
=IFERROR(INDEX(A$1:A$390,MATCH($J2,$F$1:$F$390,0)),"")

In I2:
=IFERROR(INDEX(B$1:B$390,MATCH($J2,$F$1:$F$390,0)),"")

In K2:
=IFERROR(INDEX(E$1:E$390,MATCH($J2,$F$1:$F$390,0)+1),"")

In L2:
=IFERROR(INDEX(E$1:E$390,MATCH($J2,$F$1:$F$390,0)+2),"")

In M2:
=IFERROR(INDEX(E$1:E$390,MATCH($J2,$F$1:$F$390,0)+3),"")

drag these cells down the rows.

See the attached file.

(Only J2 is array entered, rest all are normal formula)

When I drag these cells down, the only formula that copies down is for J. The other columns are just copying what is physically written in the cell being copied down (not copying the formula down). It has done the same in your example...

?

[ThirdFret]

...it keeps saying #N/A.

Your problem is a little bit more complex than your original test data made it appear. It looks like there are occasionally more than 3 trials for each? I'm going to assume that the order is what's important, but it looks like you might want to compare similarly answered tests against each other? Yes/No tests vs scaled tests?

I'm attaching an example that works for how your data is organized, but it doesn't distinguish between types of tests, merely the order that they appear. Let me know if this works for you.

[Bee008]

Your problem is a little bit more complex than your original test data made it appear. It looks like there are occasionally more than 3 trials for each? I'm going to assume that the order is what's important, but it looks like you might want to compare similarly answered tests against each other? Yes/No tests vs scaled tests?

I'm attaching an example that works for how your data is organized, but it doesn't distinguish between types of tests, merely the order that they appear. Let me know if this works for you.

ThirdFret,

Sorry for confusing the matter, I accidentally attached TWO files in my reply! One of which was an original file, which should not have been attached (i.e. that is what the software spits out- I then use a Macro to remove all the bits I don't need).
The actual data files are to the same organisation as the example file I included. Here it is, 'cleaned-up file' --> P1.xlsx

So the original answer you gave, was fantastic because my data is organised in the same way .
My only issue was copying and pasting it from your example workbook to my own. As this seemed to create a problem '#N/A'

[ThirdFret]

...my data is organised in the same way .

Bee, the difference between the data in your original example and in the P1 data is that some of the events have more "experiments" than others. I've highlighted the cells I'm referring to in yellow in the attached workbook. For example, on Event 36 there are seven rows with data included! In the attached file I have a solution that works, given you only have 3 or 4 events per trial, but I'm not sure the result it gives is actually useful to you.

I don't see any way to easily identify which event should be matched to which. There are duplicates in the event names as well:

ex. Trial P1, Event 36, has three named "Faces2.036.jpg)" all with different answers, two named "Faces2.036.jpg)1" with the same answer, and one named "Faces2.036.jpg)2".

Hopefully my explanation helps you understand why I'm a little confused. If my solution works for you that's great!

[Bee008]

Bee, the difference between the data in your original example and in the P1 data is that some of the events have more "experiments" than others. I've highlighted the cells I'm referring to in yellow in the attached workbook. For example, on Event 36 there are seven rows with data included! In the attached file I have a solution that works, given you only have 3 or 4 events per trial, but I'm not sure the result it gives is actually useful to you.

I don't see any way to easily identify which event should be matched to which. There are duplicates in the event names as well:

ex. Trial P1, Event 36, has three named "Faces2.036.jpg)" all with different answers, two named "Faces2.036.jpg)1" with the same answer, and one named "Faces2.036.jpg)2".

Hopefully my explanation helps you understand why I'm a little confused. If my solution works for you that's great!

ThirdFret! You are of course correct!!! I'm sorry, I see where the confusion is. The file had not been fully 'cleaned-up'. (The reason there are extra responses, i.e. for event 36, is because the responder has clicked extra buttons/responses- i.e. they double clicked the button 'yes'. It will be another task of mine to go through each spreadsheet and remove any extra responses). Sorry I'd missed that.

Each participant (i.e. P1) should have the same number of responses per face; they should have only 4 responses per face.
Thus, it should look like the original example I gave ... I hope ha! P1(UPLOADTHISONE).xlsx

Sorry for confusing you! (Can I go with the excuse 'it's late here in England'...?

[cbatrody]

Hi Bee,

See the attached file, I could achieve the desired result by adding few helper columns. I have worked on your latest attachment in post #15.

In G2:
=IF(F2="","",F2&B2)

In H2:
=IF(AND(E2="name",F2=""),VLOOKUP("*", F2:F$396, 1,FALSE)&B2,LOOKUP(2,1/(F$1:F2<>""),F$1:F2)&B1)

In K2: (array entered)
{=IFERROR(INDEX(G$1:G$396,SMALL(IF(G$1:G$396<>"",ROW(G$1:G$396)),ROW(A2))),"")}

In L2:
=IFERROR(INDEX(A$1:A$396,MATCH($K2,$H$1:$H$396,0)),"")

In M2:
=IFERROR(INDEX(B$1:B$390,MATCH($K2,$H$1:$H$390,0)),"")

In N2: (array entered)
{=IFERROR(INDEX(F$1:F$390,SMALL(IF(F$1:F$390<>"",ROW(F$1:F$390)),ROW(A2))),"")}

In O2:
=IFERROR(INDEX(E$1:E$390,MATCH($K2,$H$1:$H$390,0)+1),"")

In P2:
=IFERROR(INDEX(E$1:E$390,MATCH($K2,$H$1:$H$390,0)+2),"")

In Q2:
=IFERROR(INDEX(E$1:E$390,MATCH($K2,$H$1:$H$390,0)+3),"")

[ThirdFret]

See the attached file. The reason the names are not coming through is when you were scrubbing the data it seems you offset the names from their name rows (highlighted in yellow).

No need to upload another fixed sheet, the range of formulas I've posted should be able to be posted in any column of any sheet (the headers have to be in row 1 and the formulas have to start from row 2). Just try pasting the set of formulas into another of your already cleaned data sets.

[Bee008]

See the attached file. The reason the names are not coming through is when you were scrubbing the data it seems you offset the names from their name rows (highlighted in yellow).

No need to upload another fixed sheet, the range of formulas I've posted should be able to be posted in any column of any sheet (the headers have to be in row 1 and the formulas have to start from row 2). Just try pasting the set of formulas into another of your already cleaned data sets.

PERFECT!

Thank you SO much :D
I am very grateful for the help