Group all different cells value into one column

Jan Kalop

Active Member
Joined
Aug 3, 2012
Messages
367
I have all over columns in various cells data which I would like to consolidate into one column and running consecutive rows without empty cells.

Sample In column A rows 2,5,7,13,21,25,31 data all as a digit from 2 to 7 digit in one

In column B rows 3,6,8,12,23,27 data all as a digit from 2 to 7 digit in one

How I get that all data in column C running from both column A and B without blank cells
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try:-
Code:
[COLOR="Navy"]Sub[/COLOR] MG17Jun33
[COLOR="Navy"]Dim[/COLOR] Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Rw = ActiveSheet.UsedRange.Rows.Count
[COLOR="Navy"]Set[/COLOR] Rng = Range("A:B").SpecialCells(xlCellTypeConstants)
[COLOR="Navy"]For[/COLOR] Ac = 1 To 2
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] Dn.Column = Ac [COLOR="Navy"]Then[/COLOR]
            c = c + 1
            Cells(c, 3) = Dn.Value
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 

Jan Kalop

Active Member
Joined
Aug 3, 2012
Messages
367
Try:-
Code:
[COLOR=Navy]Sub[/COLOR] MG17Jun33
[COLOR=Navy]Dim[/COLOR] Rw [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, c [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] Ac [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
Rw = ActiveSheet.UsedRange.Rows.Count
[COLOR=Navy]Set[/COLOR] Rng = Range("A:B").SpecialCells(xlCellTypeConstants)
[COLOR=Navy]For[/COLOR] Ac = 1 To 2
    [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
        [COLOR=Navy]If[/COLOR] Dn.Column = Ac [COLOR=Navy]Then[/COLOR]
            c = c + 1
            Cells(c, 3) = Dn.Value
        [COLOR=Navy]End[/COLOR] If
    [COLOR=Navy]Next[/COLOR] Dn
[COLOR=Navy]Next[/COLOR] Ac
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]
Regards Mick

do I have to create module ? and how I can run it
 

Jan Kalop

Active Member
Joined
Aug 3, 2012
Messages
367
or maybe I try to show it in different way:




in cell A1 -2345
in cell A5 -35678
in cell A17 -176552
different vale in the cells can go up to row 100


then


in cell B9 -4526
in cell B13 -245577
in cell B34 -179


different vale in the cells can go up to row 100


and now I would like to tranfer that data into one C cloumn to look like:


in cell C1 - 2345
in cell C2 - 35678
in cell C3 -176552
in cell C4 - 4526
in cell C5 - 245577
in cell C6 - 179




It is possible to do that.....please help
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,689
Messages
5,597,556
Members
414,155
Latest member
Grainne whiteside

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top