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

[mickeb]

Hello im new here and new into VB coding!


here is a picture what i want to do

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)

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

 

[mickeb]

modified the book2, was some error in the columns, its ok now

 

[hiker95]

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!

 

[mickeb]

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

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!

 

[mickeb]

Ok i did ok now!

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 !

 

[hiker95]

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.

 

[hiker95]

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?

 

[mickeb]

Thanks i solved it! will post the macro shortly.

 

[mickeb]

here is macro i used in the end.

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