How to put many columns into one single colulm sorted by the first to last coloum

M

mickeb

New Member
Joined
Jan 15, 2011
Messages
26
Hello im new here and new into VB coding!


here is a picture what i want to do
output.jpg



Basiclly im looking for a for a fuction that selects the left side (raw data all cells and columns) and order them into a single column. I got a function like this but this is not perfect...
=INDEX($A$2:$B$11;MOD(ROWS(D$1:D1)-1;ROWS($A$2:$B$11))+1;INT((ROWS(D$1:D1)-1)/ROWS($A$2:$B$11))+1)
Click to expand...

The ranged selection need to be picked automaticly after N counted firms (ex. based on a other cell colums which state how many companies exists using "countA" or similar instead of a static: $A$2:$B$11 more like something like $A$2:[$endColumn$endRow) for the colums it could count amount of YEARS. The second is if its a blank observation it needs to return a blank not a zero as its now.

To make example the above code would be wrong since looking at the picture the firm 12 is at row 13 so the range would be $A2:$B13 instead of $B$11. More on, $B would be the letter which is in year 2020 (the ending year)

If i could just get a custom function where i could specify n firms and n years eg. function ColumnsToSingleColumn(FIRMS#;YEAR#) would be great.


I hope u understand the question. if not let me know so i can be more clear.
best regards
Mike
 
Sort by date Sort by votes

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
mickeb,

If my calculations are correct, you may want to consider using Microsoft Access.


Worksheet STARTDATA now contains 311 Firms, and 9 Years/Periods.

Worksheet item_1 now contains 312 rows of raw data.


Worksheet STATISTICS_OUTPUT has 5 title rows:

For each year you will need:
311 * 312 = 97,032 rows

For 9 years you will need:
311 * 312 * 9 = 873,288 rows

If your current dataset contained 12 years, with the same number of rows, you would go over the 1,048,576 rows for Excel 2007.

And, your workbook is setup for 30 years/periods!
 
Upvote 0
Hello sorry the 312 is not correct it should just be

amount of companies = 311 as u see in STARTDATA multiply with X years in this sample i got 9 years (2001-2009)

so this would be a total of 2799 (311*9) observations for example item 1.

(its per item, not to combine all items!)

so for example 30 years would (ex. 2001-2029) and 311 firms would be
311*30) 9330 observation on a single column ( or 9330 rows)

then the companies report items, (item1,2,3,4,5)
for example firm 1 on row 6 got item 1 but on years 2002-2009

Basiclly as u showned me the macro before but modify it to output data on diffrent sheet instead of same ,got a lot of confusing for me when im trying to modfy it.


it is correct, item 1, 2,3,4,5 contain 312 of data but the first row that containing data is just a title row which should be not included in the loop.


Really dont understand how u get the 311 * 312 * 9

since its just 311*9 per item then u switch column to next one, same as the first macro u made but on diffrent sheet basiclly

Im trying to modify ur macro here is my results so far... its not done its hard more me :(

Code: 
Sub test()
Dim raw_columns As Long, raw_rows As Long, raw_totals As Long, a As Long, start_row As Long, n_columns As Long, n_rows As Long, n_totals As Long

'calculate total obs. -5 and -7 is just to get the right amount of years and firms, this case 311 firms and 9 years
raw_columns = Worksheets("STARTDATA").Cells(5, Columns.Count).End(xlToLeft).Column
raw_rows = Worksheets("STARTDATA").Cells(Rows.Count, 2).End(xlUp).Row
raw_totals = raw_columns * raw_rows
n_columns = raw_columns - 7
n_rows = raw_rows - 5
n_totals = n_columns * n_rows


start_row = 6

'display statistics for item 1 combine all into a single column (column 1) based on latest year this case 2009 down to 2001
Worksheets("STATISTICS_OUTPUT").Cells(5, 1).Value = "item"
For a = n_columns To 6 Step -1
    Worksheets("STATISTICS_OUTPUT").Cells(start_row, [B]1[/B]).Resize(n_rows).Value = Worksheets("item_1").Cells(6, a).Resize(n_rows).Value
    start_row = start_row + n_rows
    Next a

'display statistics for item 2 combine all into a single column (column 2) based on latest year this case 2009 down to 200
Worksheets("STATISTICS_OUTPUT").Cells(5, 1).Value = "item"
For a = n_columns To 6 Step -1
    Worksheets("STATISTICS_OUTPUT").Cells(start_row, [B]2[/B]).Resize(n_rows).Value = Worksheets("item_2").Cells(6, a).Resize(n_rows).Value
    start_row = start_row + n_rows
    Next a


End Sub

The code above code works but is not correctly displaying item 1... aswell as item 2
Moreover, as u see i havent done years or firms into single colulm yet, dont know how to do it aswell. but im keep working

Just want to say thanks again for really helping me wow never expected something like that! :)
 
Last edited:
Upvote 0
Ok i did ok now!
Code: 
Sub test()
Dim raw_columns As Long, raw_rows As Long, raw_totals As Long, a As Long, start_row As Long, n_columns As Long, n_rows As Long, n_totals As Long

'calculate total obs.
raw_columns = Worksheets("STARTDATA").Cells(5, Columns.Count).End(xlToLeft).Column
raw_rows = Worksheets("STARTDATA").Cells(Rows.Count, 2).End(xlUp).Row
raw_totals = raw_columns * raw_rows
n_columns = raw_columns - 7
n_rows = raw_rows - 5
n_totals = n_columns * n_rows


'BEGIN OUTPUT
'Make headlines
Worksheets("STATISTICS_OUTPUT").Cells(5, 1).Value = "Observations"
Worksheets("STATISTICS_OUTPUT").Cells(5, 2).Value = "COMPANY"
Worksheets("STATISTICS_OUTPUT").Cells(5, 3).Value = "INDUSTRY1"
Worksheets("STATISTICS_OUTPUT").Cells(5, 4).Value = "MAJOR"
Worksheets("STATISTICS_OUTPUT").Cells(5, 5).Value = "INDUSTRY2"
Worksheets("STATISTICS_OUTPUT").Cells(5, 6).Value = "item 1"
Worksheets("STATISTICS_OUTPUT").Cells(5, 7).Value = "item 2"
Worksheets("STATISTICS_OUTPUT").Cells(5, 8).Value = "item 3"
Worksheets("STATISTICS_OUTPUT").Cells(5, 9).Value = "item 4"
Worksheets("STATISTICS_OUTPUT").Cells(5, 10).Value = "item 3"




start_row = 6
For a = n_columns + 1 To 1 Step -1
    'OBS howto make this?
    'FIRM NAMES howto make this
    'INDUSTRY1
    'MAJOR
    'INDUSTRY2
    Worksheets("STATISTICS_OUTPUT").Cells(start_row, 6).Resize(n_rows).Value = Worksheets("item_1").Cells(6, a).Resize(n_rows).Value
    Worksheets("STATISTICS_OUTPUT").Cells(start_row, 7).Resize(n_rows).Value = Worksheets("item_2").Cells(6, a).Resize(n_rows).Value
    Worksheets("STATISTICS_OUTPUT").Cells(start_row, 8).Resize(n_rows).Value = Worksheets("item_3").Cells(6, a).Resize(n_rows).Value
    Worksheets("STATISTICS_OUTPUT").Cells(start_row, 9).Resize(n_rows).Value = Worksheets("item_4").Cells(6, a).Resize(n_rows).Value
    Worksheets("STATISTICS_OUTPUT").Cells(start_row, 10).Resize(n_rows).Value = Worksheets("item_5").Cells(6, a).Resize(n_rows).Value
    
    start_row = start_row + n_rows
    Next a
End Sub

Thanks again for helping just got to figure out the more static variables to order, any idea !:)
 
Upvote 0
mickeb,

I have exceeded the normal amount of time I allocate for solving problems/requests from web sites like MrExcel.com.


Click on the Post Reply button, and just enter the word BUMP, and click on the Submit Reply button, and someone else will assist you.


Or, you could send a Private Message to MickG, and ask MickG to have a look at the workbook that you supplied a link to.
 
Upvote 0
mickeb,

Could you manually complete worksheet STATISTICS_OUTPUT, for just FIRM 1, TYPE 5, for item_1, for year 2009?

And, then post your workbook again, with worksheet STATISTICS_OUTPUT completed for the above?
 
Upvote 0
here is macro i used in the end.
Code: 
Sub Output1()
Dim raw_columns As Long, raw_rows As Long, raw_totals As Long, a As Long, start_row As Long, n_columns As Long, n_rows As Long, n_totals As Long, obs_start As Long, b As Long

'calculate total obs.
raw_columns = Worksheets("CNAME").Cells(5, Columns.Count).End(xlToLeft).Column
raw_rows = Worksheets("CNAME").Cells(Rows.Count, 2).End(xlUp).Row
raw_totals = raw_columns * raw_rows
n_columns = raw_columns - 1
n_rows = raw_rows - 5
n_totals = n_columns * n_rows

'DISPLAY MACRO TOTALS IN STARTDATA
Worksheets("STARTDATA").Cells(7, 6).Value = n_columns
Worksheets("STARTDATA").Cells(9, 6).Value = n_rows
Worksheets("STARTDATA").Cells(11, 6).Value = n_totals


'display statistics

start_row = 6
obs_start = 1

'Make headlines
Worksheets("OUTPUT1").Cells(5, 1).Value = "Observations"
Worksheets("OUTPUT1").Cells(5, 2).Value = "COMPANY"
Worksheets("OUTPUT1").Cells(5, 3).Value = "INDUSTRY1"
Worksheets("OUTPUT1").Cells(5, 4).Value = "MAJOR"
Worksheets("OUTPUT1").Cells(5, 5).Value = "INDUSTRY2"
Worksheets("OUTPUT1").Cells(5, 6).Value = "YEAR"
Worksheets("OUTPUT1").Cells(5, 7).Value = "item 1"
Worksheets("OUTPUT1").Cells(5, 8).Value = "item 2"
Worksheets("OUTPUT1").Cells(5, 9).Value = "item 3"
Worksheets("OUTPUT1").Cells(5, 10).Value = "item 4"
Worksheets("OUTPUT1").Cells(5, 11).Value = "item 5"

'OBSERVATIONS c1
For b = 1 To n_totals Step 1
    
    Worksheets("OUTPUT1").Cells(start_row, 1).Value = obs_start
    start_row = start_row + 1
    obs_start = obs_start + 1
    Next b


start_row = 6

'ADD REST
For a = n_columns + 1 To 1 Step -1
    'FIRMS c2
    Worksheets("OUTPUT1").Cells(start_row, 2).Resize(n_rows).Value = Worksheets("CNAME").Cells(6, a).Resize(n_rows).Value
    'INDUSTRY1 c3
    Worksheets("OUTPUT1").Cells(start_row, 3).Resize(n_rows).Value = Worksheets("INDUSTRY1").Cells(6, a).Resize(n_rows).Value
    'MAJOR c4
    Worksheets("OUTPUT1").Cells(start_row, 4).Resize(n_rows).Value = Worksheets("MAJOR").Cells(6, a).Resize(n_rows).Value
    'INDUSTRY2 c5
    Worksheets("OUTPUT1").Cells(start_row, 5).Resize(n_rows).Value = Worksheets("INDUSTRY2").Cells(6, a).Resize(n_rows).Value
    'YEAR c6
    Worksheets("OUTPUT1").Cells(start_row, 6).Resize(n_rows).Value = Worksheets("item_1").Cells(5, a).Value
    'item 1 c7
    Worksheets("OUTPUT1").Cells(start_row, 7).Resize(n_rows).Value = Worksheets("item_1").Cells(6, a).Resize(n_rows).Value
    'item 2 c8
    Worksheets("OUTPUT1").Cells(start_row, 8).Resize(n_rows).Value = Worksheets("item_2").Cells(6, a).Resize(n_rows).Value
    'item 3 c9
    Worksheets("OUTPUT1").Cells(start_row, 9).Resize(n_rows).Value = Worksheets("item_3").Cells(6, a).Resize(n_rows).Value
    'item 4 c10
    Worksheets("OUTPUT1").Cells(start_row, 10).Resize(n_rows).Value = Worksheets("item_4").Cells(6, a).Resize(n_rows).Value
    'item 5 c11
    Worksheets("OUTPUT1").Cells(start_row, 11).Resize(n_rows).Value = Worksheets("item_5").Cells(6, a).Resize(n_rows).Value
    
    start_row = start_row + n_rows
    
    Next a
    
    
    


End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
Increasing the Column Input Range on VBA Macro where Data is to be transposed into rows
Replies
0
Views
495
Nicw24
N
R
  • Solved
How to independently sort multiple columns of this SEQUENCE output without effecting the positions of the columns and of the blanks?
2
Replies
15
Views
459
Rnkhch
R
J
  • Solved
Filter a listbox containing 12 columns using a single searchbox
Replies
8
Views
774
Anthony47
A
Guybrush Threepwood
  • Question Question
Using Filter for Criteria in Multiple Columns and a Specific Row Range?
Replies
0
Views
409
Guybrush Threepwood
Guybrush Threepwood
G
  • Question Question
Improve calculation speed for this worksheet with INDIRECT/FILTER functions
Replies
0
Views
310
GeneBF
G

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

Share this page

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
Back
Top