Combining Arrays and Removing Null Values

[rappinkapc]

I want to create a file where data from economics experiments can be entered on several different worksheets, and then a combined on a different worksheet. For example:

Worksheet 1 (Session 1, Round 1)
A
1 Price
2 15
3 14
4 13
5
6
7
etc.
Worksheet 2 (Session 1, Round 2)
A
1 Price
2 20
3 15
4 25
5 40
6
7
etc.


Worksheet 3 (The one I want to be able to create)
A B C
1 Session Round Price
2 1 1 15
3 1 1 14
4 1 1 13
5 1 2 20
6 1 2 15
7 1 2 25
8 1 2 40

The problem is, I don't know how many rows there will be in each worksheet (it varies each time the experiment is run). I am having trouble with two things. First, I can't figure out how to combine the "price" arrays into one array. Second, I can't figure out how to only display the non-null values (e.g. if I define my array for sheet 1 as A2:A40, and there are only 3 values, zeros will be displayed for the remaining values). I hope I am clear. If not, let me know what else I can clarify.

Thanks.

 

[venkat1926]

suppose your data is in sheet 1 columns A,B,C,D. you want to combine columns B,C,D into one column
use this macro (modify to suit you)

Sub test()
Dim rng As Range, c As Range
Dim dest As Range
Columns("G:G").Delete
Set rng = Range(Range("b1"), Range("B1").End(xlDown).End(xlToRight))

For Each c In rng
Set dest = Cells(Rows.Count, "g").End(xlUp).Offset(1, 0)
dest = c
Next
Range(Range("g2"), Range("g2").End(xlDown)).Cut
Range("g1").Select
ActiveSheet.Paste

End Sub

regarding having only non null values sort it descding the null values will come down.

the sample sheet 1 is given below (the resutl is in column G)

******** language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js">*********>

Book1
	A	B	C	D	E	F	G
1	1	5	4	8			5
2	2	2	2	5			4
3	3	2	6	7			8
4	4	3	6	8			2
5	5	8	8	9			2
6	6	6	3	4			5
7							2
8							6
9							7
10							3
11							6
12							8
13							8
14							8
15							9
16							6
17							3
18							4
Sheet1