I'm very new to excel macros and I have a daunting task ahead of me.
I need to convert multiple columns of information into a single
column. It's too tedious to do by cut-and-paste and will take 6+ more
hours and I was hoping someone could tell me how I can write or
customize a macro to do it for me. Below is an example of what I need
to do.

Original Data:

1 2 3 4 5 6
1 y y n n y n
2 n y y y n y
3 y y n n n n
4 y y n n y y
5 n y y y y n
6 n n n y n y

Needs to turn into
1 1 y
2 1 n
3 1 y
4 1 y
5 1 n
6 1 n
1 2 y
2 2 y
3 2 y
4 2 y
5 2 y
6 2 n
1 3 n
2 3 y
3 3 n
4 3 n
5 3 y
6 3 n

etc etc.

Column A and B are the headers and will not need to be created.
Manually I'd select the y and n data and paste it underneath the last
filled row in the y & n column, then cut again and paste again. I
have so much information it'll take me more than 6 hours doing that by
hand. Is there a macro anyone can suggest to help?

Thanks in advance!

-Zhe

Columns A and B are headers and should be ignored.

And each cell in row 1 (column C to column ???) gets copied over for each of the
values. That value in row 1 goes in column A of the new sheet and the values
under it get copied to column B of the new sheet.

And the last cell that gets copied is the last cell that is used in that column.

So if my before data looked like:

asdf asdf $C$1 $D$1 $E$1 $F$1 $G$1 $H$1
asdf asdf $C$2 $D$2 $E$2 $F$2 $G$2 $H$2
asdf asdf $C$3 $E$3 $F$3 $G$3 $H$3
asdf asdf $C$4 $E$4 $F$4 $G$4 $H$4
asdf asdf $F$5 $G$5 $H$5
asdf asdf $G$6 $H$6
asdf asdf $H$7

Then after I was done, it would look like:

$C$1 $C$2
$C$1 $C$3
$C$1 $C$4
$D$1 $D$2
$E$1 $E$2
$E$1 $E$3
$E$1 $E$4
$F$1 $F$2
$F$1 $F$3
$F$1 $F$4
$F$1 $F$5
$G$1 $G$2
$G$1 $G$3
$G$1 $G$4
$G$1 $G$5
$G$1 $G$6
$H$1 $H$2
$H$1 $H$3
$H$1 $H$4
$H$1 $H$5
$H$1 $H$6
$H$1 $H$7

If yes to all that, then try this:

Option Explicit
Sub testme()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim FirstRow As Long
Dim FirstCol As Long
Dim LastCol As Long
Dim iCol As Long
Dim RngToCopy As Range
Dim DestCell As Range

Set CurWks = Worksheets("Sheet1")
Set NewWks = Worksheets.Add

With NewWks
Set DestCell = .Range("A1")
End With

With CurWks
FirstCol = 3 'skip columns A:B
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

FirstRow = 2 'skip row 1

For iCol = FirstCol To LastCol
Set RngToCopy = .Range(.Cells(FirstRow, iCol), _
.Cells(.Rows.Count, iCol).End(xlUp))
DestCell.Resize(RngToCopy.Rows.Count, 1).Value _
= .Cells(1, iCol).Value
RngToCopy.Copy _
Destination:=DestCell.Offset(0, 1)
Set DestCell = DestCell.Offset(RngToCopy.Rows.Count)
Next iCol
End With

End Sub




Abor wrote:

I'm very new to excel macros and I have a daunting task ahead of me.
I need to convert multiple columns of information into a single
column. It's too tedious to do by cut-and-paste and will take 6+ more
hours and I was hoping someone could tell me how I can write or
customize a macro to do it for me. Below is an example of what I need
to do.

Original Data:

1 2 3 4 5 6
1 y y n n y n
2 n y y y n y
3 y y n n n n
4 y y n n y y
5 n y y y y n
6 n n n y n y

Needs to turn into
1 1 y
2 1 n

3 1 y
4 1 y
5 1 n
6 1 n
1 2 y
2 2 y
3 2 y
4 2 y
5 2 y
6 2 n
1 3 n
2 3 y
3 3 n
4 3 n
5 3 y
6 3 n

etc etc.

Column A and B are the headers and will not need to be created.
Manually I'd select the y and n data and paste it underneath the last
filled row in the y & n column, then cut again and paste again. I
have so much information it'll take me more than 6 hours doing that by
hand. Is there a macro anyone can suggest to help?

Thanks in advance!

-Zhe

--

Dave Peterson

I need to convert multiple columns of information into a single
column.

Original Data:

1 2 3 4 5 6
1 y y n n y n
2 n y y y n y
3 y y n n n n
4 y y n n y y
5 n y y y y n
6 n n n y n y

Needs to turn into
1 1 y
2 1 n
3 1 y
4 1 y
5 1 n
6 1 n
1 2 y
2 2 y
3 2 y
4 2 y
5 2 y
6 2 n
1 3 n
2 3 y
3 3 n
4 3 n
5 3 y
6 3 n

Today seems to be "convert multiple columns to one column" day.

I started with the original data in Sheet1 and the following works for me.

In Sheet2!A1, put
=MOD(ROW()+5,6)+1

In Sheet2!B1, put
=INT((ROW()-1)/6)+1

In Sheet2!C1, put
=OFFSET(Sheet1!$A$1,A1,B1)

Select Sheet2!A1:C1 and extend downward to row 36.

Modify as needed.

Check my previous post two row above of this thread, adjust to suit


"Abor" wrote:

I'm very new to excel macros and I have a daunting task ahead of me.
I need to convert multiple columns of information into a single
column. It's too tedious to do by cut-and-paste and will take 6+ more
hours and I was hoping someone could tell me how I can write or
customize a macro to do it for me. Below is an example of what I need
to do.

Original Data:

1 2 3 4 5 6
1 y y n n y n
2 n y y y n y
3 y y n n n n
4 y y n n y y
5 n y y y y n
6 n n n y n y

Needs to turn into
1 1 y
2 1 n
3 1 y
4 1 y
5 1 n
6 1 n
1 2 y
2 2 y
3 2 y
4 2 y
5 2 y
6 2 n
1 3 n
2 3 y
3 3 n
4 3 n
5 3 y
6 3 n

etc etc.

Column A and B are the headers and will not need to be created.
Manually I'd select the y and n data and paste it underneath the last
filled row in the y & n column, then cut again and paste again. I
have so much information it'll take me more than 6 hours doing that by
hand. Is there a macro anyone can suggest to help?

Thanks in advance!

-Zhe



.