Convert cell in each column as new row for the same

Hello Everyone

here I'm trying to let each cell in new column related for particular row (ID) be in new row for the same row ID , here is the example:

A 1 0 1 1
B 0 0 0 1
C 1 0 1 0
D 0 1 1 1

The required results:

A 1
A 0
A 1
A 1
B 0
B 0
B 0
B 1
C 1
C 0
C 1
C 0
D 0
D 1
D 1
D 1

The excel sheet is huge, it has more the 100 row and almost 8000 columns

How can I do that !

Thanks in advance

The word is ROW not raw.

VBA or formula solution?

VBA solution is probably easier, as it's not clear if all rows contain the same number of columns or not.

Please Login or Register  to view this content.

I will try it

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

I had hoped that by putting it in bold and red... that you would have noticed Point 2 in my post... and acted on it. So this is a bit of a guess.

A7:
=IFERROR(INDEX($A$1:$A$3,MATCH(0,INDEX(--(COUNTIF($A$6:A6,$A$1:$A$3)=4),0),0)),"")

B7:
=IFERROR(INDEX($B$1:$E$3,1+INT((ROWS($1:1)-1)/4),1+MOD((ROWS($1:1)-1),4)),"")

C7:
=IF(A7="","","Column "&1+MOD((ROWS($1:1)-1),4))

Thanks Glenn

it solves this issue by formula, how can modified the VBA code to do same task !

Please Login or Register  to view this content.

Cheers

No idea. I don't do VBA. Didn't you state a preference for a formula??
You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

Originally Posted by blask

Please I need to add col number beside, for example:

A 1 0 1 1
B 0 0 0 1
C 1 0 1 0
D 0 1 1 1

The result:

A 1 col1
A 0 col2
A 1 col3
A 1 col4
B 0 col1
B 0 col2
B 0 col3
B 1 col4
C 1 col1
C 0 col2
C 1 col3
C 0 col4
D 0 col1
D 1 col2
D 1 col3
D 1 col4

What i need to add to the previous VBA to do so, instead can I add any word rather than "col",


Cheers

Change this part of my code so it looks like this

Please Login or Register  to view this content.

Please note for future reference that you are required to enclose code within code tags - I have done it for you this time, but expect you to do it yourself another time. Thanks!

Untested but try this (I'm no VBA expert).

Please Login or Register  to view this content.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.