Excel Dynamic Array Make 2 Cols of Data

[Stephen_IV]

I have data below that if there is data in Column C, I need to make a new line so that there are 2 columns worth of data.

From this:

VBA Disipline Make 1 Column with Pri and Sec Sanctions.xlsm
	A	B	C
1	SCHOOLID	SANCTION_TYPE_1	SANCTION_TYPE_SECONDARY
2	150211	1071
3	154011	1065
4	154011	1020	1000
5	154011	1090
6	154011	1071
7	150211	1078
8	150211	1070
9	154011	1054
10	150211	1020
11	150211	1070
12	154011	1061
13	150211	1010
14	154011	1070
15	154011	1076
16	154011	1070
17	154011	1076
18	154011	1076
19	154011	1065	1000
Sheet2

To This:

VBA Disipline Make 1 Column with Pri and Sec Sanctions.xlsm
	G	H
1	SCHOOLID	SANCTION
2	150211	1071
3	154011	1065
4	154011	1020
5	154011	1000
6	154011	1090
7	154011	1071
8	150211	1078
9	150211	1070
10	154011	1054
11	150211	1020
12	150211	1070
13	154011	1061
14	150211	1010
15	154011	1070
16	154011	1076
17	154011	1070
18	154011	1076
19	154011	1076
20	154011	1065
21	154011	1000
Sheet2

 

[Georgiboy]

Is the below what you are after?

Book1
	A	B	C	D	E	F	G	H
1	SCHOOLID	SANCTION_TYPE_1	SANCTION_TYPE_SECONDARY				SCHOOLID	SANCTION_TYPE
2	150211	1071					154011	1020
3	154011	1065					154011	1065
4	154011	1020	1000
5	154011	1090
6	154011	1071
7	150211	1078
8	150211	1070
9	154011	1054
10	150211	1020
11	150211	1070
12	154011	1061
13	150211	1010
14	154011	1070
15	154011	1076
16	154011	1070
17	154011	1076
18	154011	1076
19	154011	1065	1000
Sheet1

Cell Formulas
Range	Formula
G2:H3	G2	=FILTER(A2:B19,C2:C19<>"")
Dynamic array formulas.

 

[Akuini]

With VBA:

Sub Stephen_IV()
Dim i As Long, j As Long, n As Long
Dim va, vb

va = Range("A2", Cells(Rows.Count, "A").End(xlUp)).Resize(, 3)
n = WorksheetFunction.CountA(Range("C:C"))
ReDim vb(1 To UBound(va, 1) + n, 1 To 2)

For i = 1 To UBound(va, 1)
    j = j + 1
    vb(j, 1) = va(i, 1)
    vb(j, 2) = va(i, 2)
    
    If va(i, 3) <> "" Then
            j = j + 1
            vb(j, 1) = va(i, 1)
            vb(j, 2) = va(i, 3)
    End If
Next

Range("G2").Resize(j, 2) = vb
End Sub

 

[Stephen_IV]

if there is data in the SANCTION_TYPE_1 and SANCTION_TYPE_SECONDARY then I would need a new line to handle the SANCTION_TYPE_SECONDARY like below:

If I had

VBA Disipline Make 1 Column with Pri and Sec Sanctions.xlsm
	A	B	C
1	SCHOOLID	SANCTION_TYPE_1	SANCTION_TYPE_SECONDARY
2	154011	1020	1000
Sheet3

Then I am looking for this

VBA Disipline Make 1 Column with Pri and Sec Sanctions.xlsm
	A	B
1	SCHOOLID	SANCTION
2	154011	1020
3	154011	1000
Sheet3

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F
1	SCHOOLID	SANCTION_TYPE_1	SANCTION_TYPE_SECONDARY
2	150211	1071			150211	1071
3	154011	1065			154011	1065
4	154011	1020	1000		154011	1020
5	154011	1090			154011	1000
6	154011	1071			154011	1090
7	150211	1078			154011	1071
8	150211	1070			150211	1078
9	154011	1054			150211	1070
10	150211	1020			154011	1054
11	150211	1070			150211	1020
12	154011	1061			150211	1070
13	150211	1010			154011	1061
14	154011	1070			150211	1010
15	154011	1076			154011	1070
16	154011	1070			154011	1076
17	154011	1076			154011	1070
18	154011	1076			154011	1076
19	154011	1065	1000		154011	1076
20					154011	1065
21					154011	1000
Sheet5

Cell Formulas
Range	Formula
E2:F21	E2	=HSTACK(TOCOL(IF(B2:C100<>"",A2:A100,1/0),2),TOCOL(B2:C100,1))
Dynamic array formulas.

 

[Stephen_IV]

Thanks, Fluff perfect!!!!! Thank you also Akuini! Much appreciated!

 

[Fluff]

Glad we could help & thanks for the feedback.