Code to cut all column values & place them under one column

[panyagak]

Hi MrExcel

I have a large Excel sheet with common row
headers but with close to 12 columns with
different values. As the row headers are the same,

I need only one column under 1st of the 12 with all column values under it.

Currently, I copy the common rows & columns & paste them downwards 11 times, then cut all values in each column & paste them downwards, again 11 times so that am left with ONLY ONE column for quick sorting. ITS TIRESOME though safer for me.

How can I simplify this with a code?

HELP!!!

 

[panyagak]

Dear Joe4.

It worked wonders, though the 1st column doesnt have its values, that notwithstanding.

Great job & God Bless

Regards
Patrick

 

[Joe4]

the 1st column doesnt have its values

It should (I tested it out myself).
Maybe there is something different.
Does the first column actually have values in it initially?
If so, exactly what rows are they in?
Are they formulas or hard-coded values?

 

[panyagak]

Joe4.

Hi. A little code modification. ( I dont want to tamper with your code)

Referring to my ealier quizz (March 15 on thread" & your "great answer"; suppose I enlarge the data range & assume Class lists are still in Columns A & Columns B to K are common, then any values in columns M to last would've to be pasted downwards all under column L, with data set within column A to K being copied & pasted downwards!!

Hope have expressed myself clear!

Regards
Patrick

HAVE LEARNT MUCH ABOUT MACROS & AM "TRIAL RECORDING & TESTING" SEVERAL ON FRIDAY!!

 

[Joe4]

suppose I enlarge the data range & assume Class lists are still in Columns A & Columns B to K are common, then any values in columns M to last would've to be pasted downwards all under column L, with data set within column A to K being copied & pasted downwards!!

Hope have expressed myself clear!

I think it would be helpful to show a "before and after" picture to see what your data looks like, and what you want your desired result to look like.
That should help clarify exactly what you are after.

 

[panyagak]

BEFORE
Class lists
A B C D E F G H I J K L M
Class 1 3.04 1.10 4.14 2.02 1.38 7.18 1.48 0.08 2.58 1.02 0.21 0.95
Class 2 1.57 0.01 1.58 0.22 2.30 7.48 3.84 1.78 3.85 1.79 2.73 0.97
Class 3 1.09 0.40 1.49 0.06 2.36 6.28 2.77 0.75 3.17 1.15 17.00 0.98
Class 4 1.24 0.09 1.33 0.30 2.57 6.96 3.54 1.45 3.63 1.54 2.47 0.98
Class 5 7.48 10.68 18.16 7.49 1.06 9.73 9.56 10.69 1.12 0.01 33.64 0.98
Class 6 1.40 0.32 1.72 0.10 2.15 6.64 2.95 0.98 3.27 1.30 8.19 0.99
Class 7 7.48 10.68 18.16 7.49 1.06 9.73 9.56 10.69 1.12 0.01 33.64 0.99
Class 8 1.40 0.76 2.16 0.07 1.94 6.91 2.73 0.71 3.49 1.47 12.25 1.00

AFTER
Class 1 3.04 1.10 4.14 2.02 1.38 7.18 1.48 0.08 2.58 1.02 0.21
Class 2 1.57 0.01 1.58 0.22 2.30 7.48 3.84 1.78 3.85 1.79 2.73
Class 3 1.09 0.40 1.49 0.06 2.36 6.28 2.77 0.75 3.17 1.15 17.00
Class 4 1.24 0.09 1.33 0.30 2.57 6.96 3.54 1.45 3.63 1.54 2.47
Class 5 7.48 10.68 18.16 7.49 1.06 9.73 9.56 10.69 1.12 0.01 33.64
Class 6 1.40 0.32 1.72 0.10 2.15 6.64 2.95 0.98 3.27 1.30 8.19
Class 7 7.48 10.68 18.16 7.49 1.06 9.73 9.56 10.69 1.12 0.01 33.64
Class 8 1.40 0.76 2.16 0.07 1.94 6.91 2.73 0.71 3.49 1.47 12.25
Class 1 3.04 1.10 4.14 2.02 1.38 7.18 1.48 0.08 2.58 1.02 0.21
Class 2 1.57 0.01 1.58 0.22 2.30 7.48 3.84 1.78 3.85 1.79 2.73
Class 3 1.09 0.40 1.49 0.06 2.36 6.28 2.77 0.75 3.17 1.15 17.00
Class 4 1.24 0.09 1.33 0.30 2.57 6.96 3.54 1.45 3.63 1.54 2.47
Class 5 7.48 10.68 18.16 7.49 1.06 9.73 9.56 10.69 1.12 0.01 33.64
Class 6 1.40 0.32 1.72 0.10 2.15 6.64 2.95 0.98 3.27 1.30 8.19
Class 7 7.48 10.68 18.16 7.49 1.06 9.73 9.56 10.69 1.12 0.01 33.64
Class 8 1.40 0.76 2.16 0.07 1.94 6.91 2.73 0.71 3.49 1.47 12.25
Class 1 3.04 1.10 4.14 2.02 1.38 7.18 1.48 0.08 2.58 1.02 0.95
Class 2 1.57 0.01 1.58 0.22 2.30 7.48 3.84 1.78 3.85 1.79 0.97
Class 3 1.09 0.40 1.49 0.06 2.36 6.28 2.77 0.75 3.17 1.15 0.98
Class 4 1.24 0.09 1.33 0.30 2.57 6.96 3.54 1.45 3.63 1.54 0.98
Class 5 7.48 10.68 18.16 7.49 1.06 9.73 9.56 10.69 1.12 0.01 0.98
Class 6 1.40 0.32 1.72 0.10 2.15 6.64 2.95 0.98 3.27 1.30 0.99
Class 7 7.48 10.68 18.16 7.49 1.06 9.73 9.56 10.69 1.12 0.01 0.99
Class 8 1.40 0.76 2.16 0.07 1.94 6.91 2.73 0.71 3.49 1.47 1.00

 

[panyagak]

NB:

have unwittingly repeated data set under "AFTER": it should one*delete one.

regards

 

[panyagak]

How it should look like:

Class 1 3.04 1.10 4.14 2.02 1.38 7.18 1.48 0.08
2.58 1.02 0.21
Class 2 1.57 0.01 1.58 0.22 2.30 7.48 3.84 1.78
3.85 1.79 2.73
Class 3 1.09 0.40 1.49 0.06 2.36 6.28 2.77 0.75
3.17 1.15 17.00
Class 4 1.24 0.09 1.33 0.30 2.57 6.96 3.54 1.45
3.63 1.54 2.47
Class 5 7.48 10.68 18.16 7.49 1.06 9.73 9.56
10.69 1.12 0.01 33.64
Class 6 1.40 0.32 1.72 0.10 2.15 6.64 2.95 0.98
3.27 1.30 8.19
Class 7 7.48 10.68 18.16 7.49 1.06 9.73 9.56
10.69 1.12 0.01 33.64
Class 8 1.40 0.76 2.16 0.07 1.94 6.91 2.73 0.71
3.49 1.47 12.25
Class 1 3.04 1.10 4.14 2.02 1.38 7.18 1.48 0.08
2.58 1.02 0.95
Class 2 1.57 0.01 1.58 0.22 2.30 7.48 3.84 1.78
3.85 1.79 0.97
Class 3 1.09 0.40 1.49 0.06 2.36 6.28 2.77 0.75
3.17 1.15 0.98
Class 4 1.24 0.09 1.33 0.30 2.57 6.96 3.54 1.45
3.63 1.54 0.98
Class 5 7.48 10.68 18.16 7.49 1.06 9.73 9.56
10.69 1.12 0.01 0.98
Class 6 1.40 0.32 1.72 0.10 2.15 6.64 2.95 0.98
3.27 1.30 0.99
Class 7 7.48 10.68 18.16 7.49 1.06 9.73 9.56
10.69 1.12 0.01 0.99
Class 8 1.40 0.76 2.16 0.07 1.94 6.91 2.73 0.71
3.49 1.47 1.00

Approximate structure from little smartphone!

 

[Joe4]

Assuming that your data start in cell A1, try this:

Sub MyCopyData()

    Dim lastRow As Long
    
    Application.ScreenUpdating = False
        
'   Find last row with data in column A
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Copy data down
    Rows("1:" & lastRow).Copy Rows(lastRow + 1)

'   Copy column M to column L for second copy
    Range(Cells(lastRow + 1, "M"), Cells(lastRow + lastRow, "M")).Copy Cells(lastRow + 1, "L")

'   Delete column M
    Columns("M:M").Delete Shift:=xlToLeft
    
End Sub

 

[panyagak]

Joe4.

There are other 8 other columns after M; so I need all values from columns M to U to all come under column L. Data range A to K being copied/pasted downwards WHILE Column M to U values being cut/pasted downwards.

I will clutter the same space i have in my Galaxy Pocket for columns M to U values.

I think its now clear.

Regards & thanks

 

[Joe4]

There are other 8 other columns after M; so I need all values from columns M to U to all come under column L.

No where did you indicate that in the question or your examples.

suppose I enlarge the data range & assume Class lists are still in Columns A & Columns B to K are common, then any values in columns M to last would've to be pasted downwards all under column L, with data set within column A to K being copied & pasted downwards!!

Please do not oversimplify your examples and leave out key details. I don't really want to waste time coming up with solutions that do not work because we weren't given all of the details!
Is there anything else I should know about?
What row will the first line of data appear on?