Returning values if cells in array contain specific text

[keiranwyllie]

G'Day everyone,

I hope I can explain this one well enough because I haven't found any helpful hints/clues as yet.

I have a training spreadsheet with two worksheets. One sheet is a matrix (Matrix worksheet.png) of courses/course descriptions and employed positions. There's an area on that sheet to assign training as either Essential or Desirable, represented by an 'E' or 'D' respectively. This is just a basic sheet.

The second sheet (Training Captured.png) is intended to then capture the training identified as 'E' or 'D' and transpose the employed position and course description data automatically, based on where the 'Es' and 'Ds' are.

I feel this could get out of hand very easily because as 'Es' and 'Ds' are changed, there's potential for blank rows. I see this can all be solved with vba but I'm kind of lost as to where to start.

Caveat - my current version of this spreadsheet can handle this manually meaning I look at the matrix, then go to the other sheet and manually add the data. I was just hoping I might be able to do this automatically.

Any help will be greatly appreciated.

 

[keiranwyllie]

I am not clear what you are saying. Pl post pictures of what you do in data and what is the expected result.

Here goes. If the Matrix entries look like this:

Analysis Doc.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
2	Course / Position	Position 1	Position 2	Position 3	Position 4	Position 5	Position 6	Position 7	Position 8	Position 9	Position 10	Position 11	Position 12
3	Training	Essential/Desireable or otherwise trained
4	Course 1				E					E
5	Course 2		E
6	Course 3				E				E		E
7	Course 4
8	Course 5						E
Matrix

I get the that then allows me to manually add data into columns D, G, H, & I (highlighted in light blue)(J is Conditionally formatted):

Analysis Doc.xlsm
	A	B	C	D	E	F	G	H	I	J
3	Position	Designation	Position Number	Activity Alignment or Business Need	Course Title	Description	Training Complete	Availability	Provided by (Organisation)	Development or Training Req'd
4	Position 2	Ms	597776	Business Process	Course 2	Course description 2	Yes	Internal	RTO 1
5	Position 4	Mrs	403293	Leadership	Course 1	Course description 1	No	External	RTO 2
6	Position 4	Mrs	403293	Commercial	Course 3	Course description 3	No	Internal	RTO 3
7	Position 6	Ms	573998	General	Course 5	Course description 5	Yes	Internal	RTO 4
8	Position 8	Mrs	570948	Commercial	Course 3	Course description 3	Yes	External	RTO 3
9	Position 9	Mr	107775	Leadership	Course 1	Course description 1	Yes	External	RTO 2
10	Position 10	Mr	403387	Commercial	Course 3	Course description 3	No	External	RTO 3
Training & Gap Analysis

Now if I remove some of the 'Es' from the Matrix sheet:

Analysis Doc.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
2	Course / Position	Position 1	Position 2	Position 3	Position 4	Position 5	Position 6	Position 7	Position 8	Position 9	Position 10	Position 11	Position 12
3	Training	Essential/Desireable or otherwise trained
4	Course 1				E					E
5	Course 2
6	Course 3
7	Course 4
8	Course 5						E
Matrix

The data in columns D, G, H & I remains:

Analysis Doc.xlsm
	A	B	C	D	E	F	G	H	I	J
3	Position	Designation	Position Number	Activity Alignment or Business Need	Course Title	Description	Training Complete	Availability	Provided by (Organisation)	Development or Training Req'd
4	Position 4	Mrs	403293	Business Process	Course 1	Course description 1	Yes	Internal	RTO 1
5	Position 6	Ms	573998	Leadership	Course 5	Course description 5	No	External	RTO 2
6	Position 9	Mr	107775	Commercial	Course 1	Course description 1	No	Internal	RTO 3
7				General			Yes	Internal	RTO 4
8				Commercial			Yes	External	RTO 3
9				Leadership			Yes	External	RTO 2
10				Commercial			No	External	RTO 3
Training & Gap Analysis

Meaning if I now made further changes in the 'Matrix' sheet, that old data has to be manually removed.

 

[kvsrinivasamurthy]

Where from data comes to D, G ,H & I Columns

 

[keiranwyllie]

Where from data comes to D, G ,H & I Columns

I may have missed writing that above. I manually input it because it may not always be the same.