How to integrate two columns in VBA

[aryaden]

I have two columns of data I need to integrate and I was running into some trouble

This is my example sheet:

Example Sheet.xlsm
	A	B
1	Category	Description
2
3	Basketball	AAAA
4	Basketball	BBBB
5	Soccer	CCCC
6	Soccer	DDDD
7	Soccer	EEEE
8	Tennis	FFFF
9	Tennis	GGGG
Sheet1

and I need the output to be:

Example Sheet.xlsm
	A
1	Category_Description
2
3	Basketball
4	AAAA
5	BBBB
6	Soccer
7	CCCC
8	DDDD
9	EEEE
10	Tennis
11	FFFF
12	GGGG
Sheet4

 

[JackDanIce]

Why not pivot the data without using VBA?

Other.xlsm
	A	B	C	D
1	Category	Description
2	Basketball	AAAA		Row Labels
3	Basketball	BBBB		Basketball
4	Soccer	CCCC		AAAA
5	Soccer	DDDD		BBBB
6	Soccer	EEEE		Soccer
7	Tennis	FFFF		CCCC
8	Tennis	GGGG		DDDD
9				EEEE
10				Tennis
11				FFFF
12				GGGG
13
14
15
16
17
Sheet5

 

[Fluff]

How about

Sub aryaden()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, nr As Long
   
   Ary = Range("A3:B" & Range("A" & Rows.Count).End(xlUp).Row).Value2
   ReDim Nary(1 To UBound(Ary) * 2, 1 To 1)
   With CreateObject("scripting.dictionary")
      For r = 1 To UBound(Ary)
         If Not .Exists(Ary(r, 1)) Then
            .Add Ary(r, 1), Nothing
            nr = nr + 2
            Nary(nr - 1, 1) = Ary(r, 1)
            Nary(nr, 1) = Ary(r, 2)
         Else
            nr = nr + 1
            Nary(nr, 1) = Ary(r, 2)
         End If
      Next r
   End With
   Sheets("sheet4").Range("A3").Resize(nr).Value = Nary
End Sub

 

[aryaden]

How about

Sub aryaden()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, nr As Long
  
   Ary = Range("A3:B" & Range("A" & Rows.Count).End(xlUp).Row).Value2
   ReDim Nary(1 To UBound(Ary) * 2, 1 To 1)
   With CreateObject("scripting.dictionary")
      For r = 1 To UBound(Ary)
         If Not .Exists(Ary(r, 1)) Then
            .Add Ary(r, 1), Nothing
            nr = nr + 2
            Nary(nr - 1, 1) = Ary(r, 1)
            Nary(nr, 1) = Ary(r, 2)
         Else
            nr = nr + 1
            Nary(nr, 1) = Ary(r, 2)
         End If
      Next r
   End With
   Sheets("sheet4").Range("A3").Resize(nr).Value = Nary
End Sub

Thank you! That helps, I will try to implement that

 

[Fluff]

You're welcome

 

[aryaden]

I just tried it and is there anyway to modify the code so the output is this?

Book1345.xlsm
	A	B	C	D
1	Description	1	2	3
2
3	Basketball
4	AAA	X
5	BBB		X	X
6	Soccer
7	CCCC		X
8	DDDD
9	EEEE	X		X
10	Tennis
11	FFF		X
12	GGG		X
13
14
Sheet3

Currently this is what my output is using the code you posted:

Book1345.xlsm
	A	B	C	D	E
1	Category	Description	1	2	3
2
3	Basketball	AAA	X
4	AAA	BBB		X	X
5	BBB	CCCC		X
6	Soccer	DDDD
7	CCCC	EEEE	X		X
8	DDDD	FFF		X
9	EEEE	GGG		X
10	Tennis
11	FFF
12	GGG
13
14
15
Sheet1

and the initial data sheet looks like this:

Book1345.xlsm
	A	B	C	D	E
1	Category	Description	1	2	3
2
3	Basketball	AAA	X
4	Basketball	BBB		X	X
5	Soccer	CCCC		X
6	Soccer	DDDD
7	Soccer	EEEE	X		X
8	Tennis	FFF		X
9	Tennis	GGG		X
Sheet5

I really appreciate the help! I am an intern and I have multiple workbooks with 20-30 sheets each to process and this would save me a lot of time!

 

[aryaden]

You're welcome

I worked on it and tried to edit your code, but I still cannot get the contents to the right to stay intact and for the formatting to stay the same (like the example I posted above at 4:46pm) I am trying to get "basketball", "soccer" and "tennis" to become their own rows so "AAA-GGG" and their corresponding Xs stay intact. Is there anyway to make this adding of rows to happen?

Thanks in advance for any help!
Best Regards,
Arya

 

[Fluff]

The reason the code does not do what you want, is that you never asked for that to happen & you only showed one column of data in your op.
You should never simplify your request, as it is unlikely to work when you do.

As this is now a totally different question from your op, you need to start a new thread. Thanks

 

[aryaden]

Thanks! I will be sure to start a new thread for the question.

Best Regards

 

[aryaden]

The reason the code does not do what you want, is that you never asked for that to happen & you only showed one column of data in your op.
You should never simplify your request, as it is unlikely to work when you do.

As this is now a totally different question from your op, you need to start a new thread. Thanks

Is it ok if I add the code you provided in the new thread? I am fairly new to the forum and was unsure if that was acceptable.

Best Regards