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.

 

[Ezguy4u]

Keiranwyllie is there any way you could use that XLB up load? It would make helping you out much easier.

Posting Data use XL2BB version1.2.8 (8th Aug 2020)

 

[keiranwyllie]

Alrighty, I'll give this one a go.

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
5
6
7
8
9
10
11	DO NOT ADD NEW ROWS BELOW				DO NOT ADD NEW ROWS BELOW		DO NOT ADD NEW ROWS BELOW
Training & Gap Analysis

Analysis Doc.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
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													Description
4	Course 1	E				E	E			E		E	E		Course description 1
5	Course 2		E		D	D	E		E						Course description 2
6	Course 3			D				D			D		D		Course description 3
7	Course 4	D	D	E			E			E	E	E			Course description 4
8	Course 5	E	E	D		E		D	E				E		Course description 5
9	DO NOT INSERT ROWS BELOW
Matrix

 

[Ezguy4u]

Can you give us an example of the output or what you want to see when everything is completed.

 

[keiranwyllie]

Sure. Looking at the Matrix above, we see that each position has allocated training. What I'm aiming for is that when a "E" or "D" is entered in the Matrix, it'll autopopulate the other worksheet by looking up the corresponding Position, Course and Course descriptions and fill into columns A, E and F respectively as shown below.

I feel it'll have to be done in a Worksheet_Change function but not really sure where to start.

I think that once I have the start, I'll be able to incorporate changes such as an "E" or "D" changes against a position so I can update that row instead of adding to a new row while retaining old information.

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 1				Course 1	Course description 1
5	Position 1				Course 4	Course description 4
6	Position 1				Course 5	Course description 5
7	Position 2				Course 2	Course description 2
8	Position 2				Course 4	Course description 4
9	Position 2				Course 5	Course description 5
10
11	DO NOT ADD NEW ROWS BELOW				DO NOT ADD NEW ROWS BELOW		DO NOT ADD NEW ROWS BELOW
Training & Gap Analysis

 

[kvsrinivasamurthy]

All are ARRAY formulas to be copied down. If you drag further down after 9th row you will get data of all positions upto Position 12.
In A3
=IFERROR(INDEX(Sheet1!$B$2:$M$2,SMALL(IF(COUNTIF(OFFSET(Sheet1!$B$4:$B$8,0,0,,COLUMN(Sheet1!$B$2:$M$2)-COLUMN(Sheet1!$B$2)+1),"=?*")>=ROWS($A$3:$A3),COLUMN(Sheet1!$B$2:$M$2)-COLUMN(Sheet1!$B$2)+1,""),1)),"")

In E3
=IFERROR(INDEX(Sheet1!$A$4:$A$8,SMALL(IF(INDEX(Sheet1!$B$4:$M$8,,MATCH($A3,Sheet1!$B$2:$M$2,0))<>"",ROW(Sheet1!$A$4:$A$8),""),COUNTIF($A$3:$A3,$A3))-ROW(Sheet1!$A$4)+1),"")

In F3
=IFERROR(INDEX(Sheet1!$O$4:$O$8,SMALL(IF(INDEX(Sheet1!$B$4:$M$8,,MATCH($A3,Sheet1!$B$2:$M$2,0))<>"",ROW(Sheet1!$A$4:$A$8),""),COUNTIF($A$3:$A3,$A3))-ROW(Sheet1!$A$4)+1),"")

ARRAY formula is used

To enter ARRAY formula
Paste the formula
Press F2
Press Ctrl+Shift+Enter keys together.
formula will be covered with{} brackets by excel.

 

[Ezguy4u]

Because you can never have too many VBA answers here is mine. keiranwyllie now I don't know how much you know about excel and vba so if you have questions please ask. Now I took some liberties with your form. You will notice that you can only get one position at a time. If this is a problem let me know. But the ability to select a position from a drop down list, should make things go a little faster. If you do have more questions then answers, let us know.

Sub Train1()
Dim Row1 As Long
Dim Row2 As Long
Dim Col1 As Long

 Range("A3:F8").Select
    Selection.ClearContents
    Range("A3").Select
    
For Col1 = 1 To 12
If Sheets("Traniing & Gap Analysis").Range("A2") = "Position " & Col1 Then

Exit For
End If

Next Col1

Col1 = Col1 + 1

Row2 = 3

For Row1 = 3 To 7

If Sheets("Matrix").Cells(Row1, Col1) = "E" Or Sheets("Matrix").Cells(Row1, Col1) = "D" Then
Debug.Print Sheets("Matrix").Cells(Row1, 2)
Sheets("Traniing & Gap Analysis").Cells(Row2, 1) = Sheets("Traniing & Gap Analysis").Range("A2")
Sheets("Traniing & Gap Analysis").Cells(Row2, 5) = Sheets("Matrix").Cells(Row1, 1)
Sheets("Traniing & Gap Analysis").Cells(Row2, 6) = Sheets("Matrix").Cells(Row1, 15)

Row2 = Row2 + 1

End If

Next Row1

End Sub

20-09-20 Train.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Position	Designation	Position Number	Activity Alignment or Business Need	Course Title	Description	Training Complete	Availability	Provided by (Organisation)	Development or Training Req'd
2	Position 7											Position 1
3	Position 7				Course 3	Course description 3						Position 2
4	Position 7				Course 5	Course description 5						Position 3
5												Position 4
6												Position 5
7												Position 6
8												Position 7
9	DO NOT ADD NEW ROWS BELOW				DO NOT ADD NEW ROWS BELOW		DO NOT ADD NEW ROWS BELOW					Position 8
10												Position 9
11	Position 1				Course 1	Course description 1
12	Position 1				Course 4	Course description 4
13	Position 1				Course 5	Course description 5
14	Position 2				Course 2	Course description 2
15	Position 2				Course 4	Course description 4
16	Position 2				Course 5	Course description 5
Traniing & Gap Analysis

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=$L$2:$L$10

20-09-20 Train.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	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
2	Training	Essential/Desireable or otherwise trained													Description
3	Course 1	E				E	E			E		E	E		Course description 1
4	Course 2		E		D	D	E		E						Course description 2
5	Course 3			D				D			D		D		Course description 3
6	Course 4	D	D	E			E			E	E	E			Course description 4
7	Course 5	E	E	D		E		D	E				E		Course description 5
8	DO NOT INSERT ROWS BELOW
Matrix

 

[keiranwyllie]

All are ARRAY formulas to be copied down. If you drag further down after 9th row you will get data of all positions upto Position 12.
In A3
=IFERROR(INDEX(Sheet1!$B$2:$M$2,SMALL(IF(COUNTIF(OFFSET(Sheet1!$B$4:$B$8,0,0,,COLUMN(Sheet1!$B$2:$M$2)-COLUMN(Sheet1!$B$2)+1),"=?*")>=ROWS($A$3:$A3),COLUMN(Sheet1!$B$2:$M$2)-COLUMN(Sheet1!$B$2)+1,""),1)),"")

In E3
=IFERROR(INDEX(Sheet1!$A$4:$A$8,SMALL(IF(INDEX(Sheet1!$B$4:$M$8,,MATCH($A3,Sheet1!$B$2:$M$2,0))<>"",ROW(Sheet1!$A$4:$A$8),""),COUNTIF($A$3:$A3,$A3))-ROW(Sheet1!$A$4)+1),"")

In F3
=IFERROR(INDEX(Sheet1!$O$4:$O$8,SMALL(IF(INDEX(Sheet1!$B$4:$M$8,,MATCH($A3,Sheet1!$B$2:$M$2,0))<>"",ROW(Sheet1!$A$4:$A$8),""),COUNTIF($A$3:$A3,$A3))-ROW(Sheet1!$A$4)+1),"")

ARRAY formula is used

To enter ARRAY formula
Paste the formula
Press F2
Press Ctrl+Shift+Enter keys together.
formula will be covered with{} brackets by excel.

I gave this a go and it works well for columns A, E & F. The downside is that if an "E" or "D" is deleted from the 'Matrix', any data that exists is columns D, G through J remains. Same goes when a new "E" or "D" is added in a Matrix column that is before already entered data (i.e there's an "E" in the column under Position 5 and I add a new "E" in the column under Position 2)...any data in columns D, G through J doesn't adjust.
I'm not concerned about columns B or C because they use formulas to draw data based on the adjacent cell in Column which automatically changes.

 

[kvsrinivasamurthy]

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

 

[keiranwyllie]

Because you can never have too many VBA answers here is mine. keiranwyllie now I don't know how much you know about excel and vba so if you have questions please ask. Now I took some liberties with your form. You will notice that you can only get one position at a time. If this is a problem let me know. But the ability to select a position from a drop down list, should make things go a little faster. If you do have more questions then answers, let us know.

Sub Train1()
Dim Row1 As Long
Dim Row2 As Long
Dim Col1 As Long

Range("A3:F8").Select
    Selection.ClearContents
    Range("A3").Select
   
For Col1 = 1 To 12
If Sheets("Traniing & Gap Analysis").Range("A2") = "Position " & Col1 Then

Exit For
End If

Next Col1

Col1 = Col1 + 1

Row2 = 3

For Row1 = 3 To 7

If Sheets("Matrix").Cells(Row1, Col1) = "E" Or Sheets("Matrix").Cells(Row1, Col1) = "D" Then
Debug.Print Sheets("Matrix").Cells(Row1, 2)
Sheets("Traniing & Gap Analysis").Cells(Row2, 1) = Sheets("Traniing & Gap Analysis").Range("A2")
Sheets("Traniing & Gap Analysis").Cells(Row2, 5) = Sheets("Matrix").Cells(Row1, 1)
Sheets("Traniing & Gap Analysis").Cells(Row2, 6) = Sheets("Matrix").Cells(Row1, 15)

Row2 = Row2 + 1

End If

Next Row1

End Sub

Thanks mate, I'll have to look into this one a bit further when I have the time to digest the code. Awesome work though!!! Appreciate it!!