Values in columns to rows

[Pattyboy]

Hello,

I have a question about converting data in multiple columns and rows into a single row for each cell. I'm wondering if anyone knows of a macro or Excel formula that could accomplish this. I'll try to provide an example below.

As Is:
Column 1 Column 2 Column 3
Row 1: Test1 Test2
Row 2: Test3
Row 3: Test4 Test5 Test6
Row 4: Test7 Test8

To Be:
Column 1
Row 1: Test1
Row 2: Test2
Row 3: Test3
Row 4: Test4
Row 5: Test5
Row 6: Test6
Row 7: Test7
Row 8: Test8

Any assistance would be greatly appreciated!

 

[Domenic]

Assuming that A1:C4 contains the data, and that the data contains text values, try...

E1:

=COUNTIF(A1:C4,"?*")

F1, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(ROWS(F$1:F1)<=$E$1,INDEX($A$1:$C$4,SMALL(IF($A$1:$C$4<>"",ROW($A$1:$C$4)-ROW($A$1)+1),ROWS(F$1:F1)),RIGHT(SMALL(IF($A$1:$C$4<>"",(ROW($A$1:$C$4)-ROW($A$1)+1)*10^5+(COLUMN($A$1:$C$4)-COLUMN($A$1)+1)),ROWS($F$1:F1)),5)+0),"")

 

[mahmed1]

Assuming that A1:C4 contains the data, and that the data contains text values, try...

E1:

=COUNTIF(A1:C4,"?*")

F1, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(ROWS(F$1:F1)<=$E$1,INDEX($A$1:$C$4,SMALL(IF($A$1:$C$4<>"",ROW($A$1:$C$4)-ROW($A$1)+1),ROWS(F$1:F1)),RIGHT(SMALL(IF($A$1:$C$4<>"",(ROW($A$1:$C$4)-ROW($A$1)+1)*10^5+(COLUMN($A$1:$C$4)-COLUMN($A$1)+1)),ROWS($F$1:F1)),5)+0),"")

Wow thank you for this amazing formula,

If you dont mind, can you please explain why you used 10^5 and +0 and also why you used the start of the formula again but with the right function.

I would be greatful if you could break it down im ok with the first part but not sure about 10^5, +0

RIGHT(SMALL(IF($A$1:$C$4<>"",(ROW($A$1:$C$4)-ROW($A$1)+1)*10^5+(COLUMN($A$1:$C$4)-COLUMN($A$1)+1)),ROWS($F$1:F1)),5)+0),"")

Thank you

 

[Domenic]

RIGHT(SMALL(IF($A$1:$C$4<>"",(ROW($A$1:$C$4)-ROW($A$1)+1)*10^5+(COLUMN($A$1:$C$4)-COLUMN($A$1)+1)),ROWS($F$1:F1)),5)+0),"")

Basically, for each cell in A1:C4 that is not blank, the corresponding row number, multiplied by 10^5, and column number are added together. The reason that the row number is multiplied by 10^5 is to "make room" for the column number, which can have a maximum of 5 digits (16,384 columns for Excel 2007). Effectively, part of the result will refer to the row, and part will refer to the column. For example, if A1 is not blank, we get the following...

1 * 10^5 + 1 ==> 100001

As you can see, the first part in red refers to the row, while the second part in blue refers to the column. As another example, let's say that cell L20 is being evaluated and that it is not blank, then we get the following...

20 * 10^5 + 12 ==> 2000012

Again, the first part in red refers to the row and the second part in blue refers to the column. So, in your original example, for each cell in A1:C4 that is not blank, a number that represents a reference to its row and column is returned. This array of values is then passed to the SMALL function, which returns the smallest value. In turn, this value is passed to the RIGHT function, which extracts the last 5 characters. Since this function returns a text value, it is coerced into a numerical value by adding 0 to it. So, for example, "00012" becomes 12. This final result is then used by the INDEX function as it's column argument.

 

[mahmed1]

Basically, for each cell in A1:C4 that is not blank, the corresponding row number, multiplied by 10^5, and column number are added together. The reason that the row number is multiplied by 10^5 is to "make room" for the column number, which can have a maximum of 5 digits (16,384 columns for Excel 2007). Effectively, part of the result will refer to the row, and part will refer to the column. For example, if A1 is not blank, we get the following...

1 * 10^5 + 1 ==> 100001

As you can see, the first part in red refers to the row, while the second part in blue refers to the column. As another example, let's say that cell L20 is being evaluated and that it is not blank, then we get the following...

20 * 10^5 + 12 ==> 2000012

Again, the first part in red refers to the row and the second part in blue refers to the column. So, in your original example, for each cell in A1:C4 that is not blank, a number that represents a reference to its row and column is returned. This array of values is then passed to the SMALL function, which returns the smallest value. In turn, this value is passed to the RIGHT function, which extracts the last 5 characters. Since this function returns a text value, it is coerced into a numerical value by adding 0 to it. So, for example, "00012" becomes 12. This final result is then used by the INDEX function as it's column argument.

Thank you

Just to clarify, i could have used any number to multiply by the row however we multiplied by 10^5 just incase we had more columns? So if the range was A1:J100 (row * 10^5+columns) will be able to handle that as there would be room for column right? (I hope im on the right track).

I have seen this same example for Columns * (10^5+row)
(16,384 columns for Excel 2007)
Whats the maximum For Rows?

So 10^5 is the maximum i can use for columns and also for rows. So just incase we had large data we used 10^5, if i had used 10^6 that wouldnt work right?

10^5 for maximum rows/columns
2^15 is 1 more than the maximum number of characters allowed in a cell
What is 9.99999999999999e+307 used for?

I see quite a few tips and numbers to calculate rows/columns.

Thank you

 

[Pattyboy]

Thank you Domenic! This is a HUGE help, and completely answers my question. I really didn't think this was going to be possible, so I really appreciate your assistance.

 

[Domenic]

Thank you

You're very welcome!

Just to clarify, i could have used any number to multiply by the row however we multiplied by 10^5 just incase we had more columns? So if the range was A1:J100 (row * 10^5+columns) will be able to handle that as there would be room for column right? (I hope im on the right track).

Yes, that's right...

Whats the maximum For Rows?

For Excel 2007, the maximum number of rows is 1,048,576.

So 10^5 is the maximum i can use for columns and also for rows. So just incase we had large data we used 10^5, if i had used 10^6 that wouldnt work right?

Yes, it would also work. However, since the maximum number of columns consists of 5 digits, there's no need to do so.

What is 9.99999999999999e+307 used for?

Have a look at Aladin's explanation in the following thread...

http://www.mrexcel.com/forum/showthread.php?t=102091

 

[mahmed1]

You're very welcome!



Yes, that's right...



For Excel 2007, the maximum number of rows is 1,048,576.



Yes, it would also work. However, since the maximum number of columns consists of 5 digits, there's no need to do so.



Have a look at Aladin's explanation in the following thread...

http://www.mrexcel.com/forum/showthread.php?t=102091

Once gain thank you for your help.

Quick question, i sometimes find it confusing when to put parenthesis around a function i.e sometimes you might see row(a2)-row(a1) and sometimes you enter it like (row(a2)-row(a1)) etc..

Sometimes there is a huge formula with loads of parenthesis around functions ie index((small(if((row(a2)-row(a1)) etc...

I probably have not given a clear explanation but what i wanted to know is, whats the best and easiest way to break down a formula when you have loads of parenthesis around functions and when there is a huge data and also when i combine normal formulas or Huge formulas myself, what advice will you give in order for me to know exactly where to put parenthesis and how to make it easy to put a formula together and also break down the formula.

Your Tips and advice are much appreciated.

Thank You

 

[Domenic]

Sometimes parenthesis are added to control the order of operations. In the following example, the parenthesis in red means that the subtraction and addition takes place before the multiplication...

(ROW($A$1:$C$4)-ROW($A$1)+1)*10^5

Sometimes parenthesis are added to help distinguish between different sets of operation. In the following example, the parenthesis in blue are not need, however they help to distinguish between the different set of operations...

(ROW($A$1:$C$4)-ROW($A$1)+1)*10^5+(COLUMN($A$1:$C$4)-COLUMN($A$1)+1)

As far as learning is concerned, you'll find this Board to be a great place to learn, with many talented people who are more than willing to share their knowledge and expertise.

 

[mahmed1]

Sometimes parenthesis are added to control the order of operations. In the following example, the parenthesis in red means that the subtraction and addition takes place before the multiplication...

(ROW($A$1:$C$4)-ROW($A$1)+1)*10^5

Sometimes parenthesis are added to help distinguish between different sets of operation. In the following example, the parenthesis in blue are not need, however they help to distinguish between the different set of operations...

(ROW($A$1:$C$4)-ROW($A$1)+1)*10^5+(COLUMN($A$1:$C$4)-COLUMN($A$1)+1)

As far as learning is concerned, you'll find this Board to be a great place to learn, with many talented people who are more than willing to share their knowledge and expertise.

Thank You again for your advise.

I would never have thought of the (10^5) trick or 2^15 etc..

How do you guys know straight away when to use what numbers or is it a case of just practice and the scenario?

What type of scenarios would you advise that we would know straight away that we could use the big num 9.99e on this scenario or 2^15 or 10^(rows 1;2;3;4) etc....

Im just trying to learn some tips of you guys. I must say you guys are amazing. Love it

Thank you