Identifying and denoting based on conditional data

[aryaden]

I am quite new to VBA and I am having trouble some trouble creating a macro for this task. Using the example sheet I provided, I need to identify when the column has a "X" in one of the text boxes under each sport listed (Basketball, Soccer, Tennis) and if there is an "X" for that day, I need to put an "X" in the row that the sport is listed in. (I have included an expected output sheet).

I am trying to apply this logic to a much larger excel sheets and I need to do this for about 40 sheets so making a macro would be ideal. The difficulty is that the sports listed differ from sheet to sheet (some have baseball/football and etc.) and the number of text boxes (AAAA, BBBB, CCCC... in the example) under each sport also differ. So I am trying to create a general macro to do this.

This is my example sheet:

Example Sheet.xlsm
	A	B	C	D	E	F	G
1	Category_Description	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
2		1	1	1	1	1	1
3	Basketball
4	AAAA	X	X		X
5	BBBB		X			X
6	Soccer
7	CCCC				X		X
8	DDDD		X	X	X	X
9	EEEE		X				X
10	Tennis
11	FFFF		X	X
12	GGGG	X					X
Sheet4

I need the output to be:

Example Sheet.xlsm
	A	B	C	D	E	F	G
1	Category_Description	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
2		1	1	1	1	1	1
3	Basketball	X	X		X	X
4	AAAA	X	X		X
5	BBBB		X			X
6	Soccer		X	X	X	X	X
7	CCCC				X		X
8	DDDD		X	X	X	X
9	EEEE		X				X
10	Tennis	X	X	X			X
11	FFFF		X	X
12	GGGG	X					X
Sheet2

 

[ranman256]

you really shouldn use this layout. Instead enter data as:
BASEBALL, MONDAY, AAAA, X
BASEBALL, WEDNESDAY, AAAA,""
TENNIS, TUESDAY, FFFF,X
etc..

then run a pivot table to summarize the data.
but YOUR way, formula cell B3 would be: =IF(COUNTA(B4:B5)>0,"x","")