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

 

[hiker95]

mickeb,

Welcome to the MrExcel forum.


You are posting a picture. This means that if this was a problem where one needed to use your data, anyone trying to help you would have to enter the data manually. That makes no sense and I doubt that you would get any answer.

Please attach screenshots of your workbook or a sample workbook that accurately portrays your current workbook on one sheet, and what it should look like 'After' on another sheet. This makes it much easier to see exactly what you want to do, as well as shows us whether there is a consistent number of rows between tables and such.

Here are three possible ways to post small (copyable) screen shots directly in your post:

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

or
RichardSchollar’s beta HTML Maker -...his signature block at the bottom of his post

or
Borders-Copy-Paste


Or, you can upload your workbook to www.box.net and provide us with a link to your workbook.

 

[mickeb]

here is excel file to show you how im thinking

http://dev.apub.se/Book1.xlsx

 

[hiker95]

mickeb,

Not finding the workbook.

You can upload your workbook to www.box.net and provide us with a link to your workbook.

 

[mickeb]

Ok i try again!

http://dl.dropbox.com/u/8387406/Book1.xlsx

 

[MickG]

Try this:-
Results start on sheet (2). "A1"
NB:- The Code assumes that cell "A1 of your data is the cell with Header "Company/Year" in it

[COLOR=navy]Sub[/COLOR] MG15Jan27
[COLOR=navy]Dim[/COLOR] ColRng [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] ac [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] Col [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] nRng [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] rw [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] Hds [COLOR=navy]As[/COLOR] Variant
n = 1
Hds = Array("Obs", "Comp", "Year", "Item X")
[COLOR=navy]Set[/COLOR] ColRng = Range(Range("A1"), Cells(1, Columns.Count).End(xlToLeft))
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & rows.Count).End(xlUp))
ReDim ray(1 To Rng.Count * ColRng.Count, 1 To 4)
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Col [COLOR=navy]In[/COLOR] ColRng
    [COLOR=navy]If[/COLOR] Col.Column < 5 [COLOR=navy]Then[/COLOR]
        ray(1, Col.Column) = Hds(Col.Column - 1)
    [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Set[/COLOR] nRng = Rng.Offset(1, Col.Column).Resize(Rng.Count - 1)
    rw = 0
[COLOR=navy]If[/COLOR] Col.Column < 6 [COLOR=navy]Then[/COLOR]
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] nRng
        n = n + 1
        rw = rw + 1
        ray(n, 1) = n - 1: ray(n, 2) = Rng(rw + 1)
        ray(n, 3) = ColRng(1, Col.Column + 1): ray(n, 4) = Dn
    [COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Col
Sheets("Sheet2").Range("A1").Resize(n + 1, 4) = ray
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[hiker95]

mickeb,


Sample data before the macro:

Excel Workbook
	A	B	C	D	E	F	G
1	ITEM X
2	COMPANY / YEAR	2001	2002
3	Firm 1	100	200
4	Firm 2	300	300
5	Firm 3	300	300
6	Firm 4	200	600
7	Firm 5		500
8	Firm 6	100	200
9	Firm 7	200	300
10	Firm 8	500	18
11	Firm 9	500
12	Firm 10	300	500
13
14
15
16
17
18
19
20
21
22
23
Sheet1

After the macro:

Excel Workbook
	A	B	C	D	E	F	G
1	ITEM X
2	COMPANY / YEAR	2001	2002		Obs	YEAR	ITEM X
3	Firm 1	100	200		1	2002	200
4	Firm 2	300	300		2	2002	300
5	Firm 3	300	300		3	2002	300
6	Firm 4	200	600		4	2002	600
7	Firm 5		500		5	2002	500
8	Firm 6	100	200		6	2002	200
9	Firm 7	200	300		7	2002	300
10	Firm 8	500	18		8	2002	18
11	Firm 9	500			9	2002
12	Firm 10	300	500		10	2002	500
13					11	2001	100
14					12	2001	300
15					13	2001	300
16					14	2001	200
17					15	2001
18					16	2001	100
19					17	2001	200
20					18	2001	500
21					19	2001	500
22					20	2001	300
23
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReorgData()
' hiker95, 01/15/2011
' http://www.mrexcel.com/forum/showthread.php?t=521539
Dim LR As Long, LC As Long, NR As Long, n As Long, a As Long
Application.ScreenUpdating = False
Worksheets("Sheet1").Activate
LC = Cells(2, Columns.Count).End(xlToLeft).Column
If LC > 3 Then
  LR = Cells(Rows.Count, 5).End(xlUp).Row
  Range(Cells(1, 4), Cells(LR, LC)).Clear
End If
LR = Cells(Rows.Count, 1).End(xlUp).Row
LC = Cells(2, Columns.Count).End(xlToLeft).Column
Range(Cells(2, LC + 2), Cells(2, LC + 3)) = [{"Obs","YEAR"}]
Cells(2, LC + 4) = Range("A1").Value
n = LR - 2
NR = 3
For a = LC To 2 Step -1
  Cells(NR, LC + 3).Resize(n).Value = Cells(2, a).Value
  Cells(NR, LC + 4).Resize(n).Value = Cells(3, a).Resize(n).Value
  NR = NR + n
Next a
n = (LR - 2) * (LC - 1)
Cells(3, LC + 2) = 1
Cells(4, LC + 2).Formula = "=E3+1"
Cells(4, LC + 2).AutoFill Destination:=Range(Cells(4, LC + 2), Cells(4 + n - 2, LC + 2))
With Range(Cells(4, LC + 2), Cells(4 + n - 2, LC + 2))
  .Value = .Value
End With
Application.ScreenUpdating = True
End Sub

Then run the ReorgData macro.

 

[mickeb]

Thanks Hiker95 and MickG works nice for now, wonder if its hard to separte the subrutine into 3 categories?

one order observations 1 to counted rows in sample

second subrutine order year and companies, in other words, single row into repeated single row (repeat the year or sample of firms until it reaches total observations.

Third, select the array of items and paste them from diffrent colulms into one column


Why 3 subrutines u ask? because then i can have more like a generic method to sort any kind of data fast.

If u can do, all this subrutines can report into sheet2 and each subrutine need to paste after the next available free column.

so for example if i already used subrutine 1 (total observation ordering), it adds to sheet2 and column A,

Then next used subrutine should paste to A if its empty else to B and so on, check for the last unavaible colulmn and put it after that

hope its not much to ask. Im really enyoning learning vba

 

[hiker95]

mickeb,

I re-wrote my macro so that the results would be in a new worksheet Results.


Can you post another workbook:

1. with Sheet1 containing just your raw data (without any additional rows or instructions).

2. with Sheet2 or Results with what the raw data should look like after your latest request.

 

[mickeb]

hello thanks again for responding. really appricieted

here is the new file

notice that the items and companies and years begining to show at row 5 not row 1 as before. this is the final excelsheet i work with.

http://dl.dropbox.com/u/8387406/book2.xlsx