Create list of cells from one column that matches one criteria

[mborschm]

Hi All!

I work for a university and have done an audit on subjects and the specific skills required for that subject. I have a table that shows the subject's code, the skill required and when during the term their subject should receive support (before an assessment item that requires that skill).

A simplified version is below:

Subject Code	Report Writing	Essay Writing	Oral Presentations
BUS100	Week 2	Week 5
ITC105		Week 3	Week 7
HRM304	Week 11		Week 2

Essentially, I want Excel to look for all the instances where "week 2" is mentioned, and return a list like this: (colour coded above for clarity)

Week 2

BUS100, HRM304

Ideally with a count of the subjects that need to be attended that week. The blanks would mean that that skill is not relevant to the subject. I would then do that process for all the weeks.

The subject codes are the first column, so I don't know is some kind of advanced VLOOKUP could work here? Hope my explanation makes sense. I just want to see visually which subjects correlate to which weeks so I can plan for them.

I'm quite unfamiliar with Excel terms so please explain as you would to a beginner haha

Thank you!

 

[RasGhul]

maybe this?

Book1
	A	B	C	D
1	Subject Code	Report Writing	Essay Writing	Oral Presentations
2	BUS100	Week 2	Week 5
3	ITC105		Week 3	Week 7
4	HRM304	Week 11		Week 2
5
6	Week	Count	Subject1	Subject2
7	Week 1
8	Week 2	2	BUS100	HRM304
9	Week 3	1	ITC105
10	Week 4
11	Week 5	1	BUS100
12	Week 6
13	Week 7	1	ITC105
14	Week 8
15	Week 9
16	Week 10
17	Week 11	1	HRM304
Sheet1

Cell Formulas
Range	Formula
B7:B17	B7	=SUMPRODUCT(($B$2:$B$4=A7)+($C$2:$C$4=A7)+($D$2:$D$4=A7))
C7,C9:C17,C8:D8	C7	=TRANSPOSE(FILTER($A$2:$A$4,($B$2:$B$4=A7)+($C$2:$C$4=A7)+($D$2:$D$4=A7),""))

 

[Fluff]

Hi & welcome to MrExcel.
Another option

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I
1	Subject Code	Report Writing	Essay Writing	Oral Presentations
2	BUS100	Week 2	Week 5				Week 1
3	ITC105		Week 3	Week 7			Week 2	2	BUS100, HRM304
4	HRM304	Week 11		Week 2			Week 3	1	ITC105
5							Week 4
6							Week 5	1	BUS100
7							Week 6
8
Primary

Cell Formulas
Range	Formula
H2,H5,H7,H6:I6,H3:I4	H2	=LET(m,MMULT(--($B$2:$D$4=G2),SEQUENCE(COLUMNS($B$2:$D$2),,,0)),s,SUM(m),IF(s=0,"",CHOOSE({1,2},s,TEXTJOIN(", ",,FILTER($A$2:$A$4,m>0,"")))))

 

[mborschm]

Thank you both very much!!! Either of those would work really well, thanks again

 

[RasGhul]

Thanks for the feedback

 

[Fluff]

Glad we could help & thanks fro the feedback.

 

[Kay10]

Hi & welcome to MrExcel.
Another option

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I
1	Subject Code	Report Writing	Essay Writing	Oral Presentations
2	BUS100	Week 2	Week 5				Week 1
3	ITC105		Week 3	Week 7			Week 2	2	BUS100, HRM304
4	HRM304	Week 11		Week 2			Week 3	1	ITC105
5							Week 4
6							Week 5	1	BUS100
7							Week 6
8
Primary

Cell Formulas
Range	Formula
H2,H5,H7,H6:I6,H3:I4	H2	=LET(m,MMULT(--($B$2:$D$4=G2),SEQUENCE(COLUMNS($B$2:$D$2),,,0)),s,SUM(m),IF(s=0,"",CHOOSE({1,2},s,TEXTJOIN(", ",,FILTER($A$2:$A$4,m>0,"")))))

Hello, is there a way to have it also split it also based on another criteria? example "Pending", "Submitted", "In Progress"

 

[Fluff]

As this is a different question, please start a thread of your own. Thanks

 

[Kay10]

As this is a different question, please start a thread of your own. Thanks

Okay, i have started a new thread.

Create list of cells from one column that matches one criteria and separate based on another cell criteria

Can anyone help me make the following happen? I want to take the data from sheet 1 and have the results in sheet 2. I have a table that shows the Project name, status, allocation, start and end date, and the personal (Attached image) I would like to have sheet 2 auto-populate a consolidated...

www.mrexcel.com

Can you help?