Identifying Values in a Range After Integrating Two Columns

[aryaden]

My original excel worksheet looks like:

Book1345.xlsm
	A	B	C	D	E	F
1	Category	Description	1	2	3	4
2			a	b	c	d
3	Basketball	AAA	X			X
4	Basketball	BBB		X	X
5	Soccer	CCCC				X
6	Soccer	DDDD				X
7	Soccer	EEEE	X		X
8	Tennis	FFF		X		X
9	Tennis	GGG		X		X
10
Original_1

I am then using this VBA code: integrates the "Category" and "Duties" columns

Sub Integrate()

    Dim lr As Long
    Dim r As Long
    Dim lc As Long
  
    Application.ScreenUpdating = False
  
'   Find last row with data in column A
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
'   Loop through data backwards
    For r = lr To 3 Step -1
'       Check to see if column A is not equal to row above it
        If (Cells(r, "A") <> "") And (Cells(r, "A") <> Cells(r - 1, "A")) Then
'           Insert blank row
            Rows(r).Insert
'           Copy value to column B
            Cells(r, "B") = Cells(r + 1, "A")
'           Copy formatting
            Cells(r + 1, "A").Copy
            Range(Cells(r, "B"), Cells(r, lc)).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
                SkipBlanks:=False, Transpose:=False
            Application.CutCopyMode = False
        End If
    Next r
  
'   Delete column A
    Columns("A:A").Delete
  
'   Autofit columns
    Cells.EntireColumn.AutoFit
      
    Application.ScreenUpdating = True
  
End Sub

To transform the original worksheet into this:

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

I am now trying to incorporate code into the VBA that will change the output to this: For example B6 , D6, and E6 have Xs in them because B7-B9, D7-D9 and E7-E9 all have at least one X in that range

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

Currently I am entering the formula "=IF(COUNTIF(B4:B11, "X"), "X", " ")" into each cell of the category rows (3, 6, and 10 which are Basketball, Soccer, Tennis in example) to get to my goal output. Is there anyway to do this within the initial VBA I am using?

I have multiple worksheets where this "range" changes. For this example rows 4-5 is the Basketball Range whereas in another worksheet the range may be rows 4-7. I am trying to come up with a macro that can add in the Xs regardless of the data range. I am an intern who has many workbooks of data like this to sort and having a macro would help a lot.

Thank you so much!