Formula to list records based on names

[sreejeshc]

Hi,

I need to get the list of training records for a particular employee from the overall employee training matrix in different sheet.
Attached the requirement in sample sheet. sheet 1 (data) is the training matrix and sheet 2 (summary) is where I need the formula to retrieve the info based on employee name


https://ufile.io/lmzg6

Sreejesh

 

[Aladin Akyurek]

Care to post a small sample here along with the desired results here instead of a link?

 

[sreejeshc]

Care to post a small sample here along with the desired results here instead of a link?

		Training courses
Empoyee	T1	T2	T3	T4	T5	T6
Emp 1	MC		EA	MA		EC
Emp 2	MC	EC	EA		MC	EC
Emp 3	MC	EC	EC		EC	EC

<tbody>
</tbody><colgroup><col><col><col span="5"></colgroup>

The result should be

When I select employee Emp1 , I should get

	Training	Type
	T1	MC
	T3	EA
	T4	MA
	T6	EC

<tbody>
</tbody><colgroup><col><col><col></colgroup>

 

[BrianJN1]

Working from your link I derived this:

				Employee name	Emp 4
	Training	Type
1	T1	MC		Emp 4
2	T2	0
3	T3	EC
4	T4	0
5	T5	EC
6	T6	EC

<tbody>
</tbody>

I realised that you have a dropmenu beside Employee Name so I used that in the formulae.

The first formula that appears under "Type" is:
=VLOOKUP(F$4,data!$A$3:$G$12,2)

F4 is where I get the Employee ID, the range is collected from sheet data and the 2 is the second column of that range.
For the next row which is appearing as "0" the formula, when dragged down has the 2 changed to 3. Obviously in the next row the 3 becomes 4.
Now, if you don't want the "0" to show do Conditional Formatting to make the colour white.

Aladin will not be aware of some of the details that I have included from knowledge of your link, but ... I think that should he view this then he can advise on making my formula more adaptable.

Oh. In your link you had some rows left out, I did not consider that.

 

[Aladin Akyurek]

Thanks lots...

Sheet1 (data)

Row\Col	A​	B​	C​	D​	E​	F​	G​
1​	Empoyee	T1	T2	T3	T4	T5	T6
2​	Emp 1	MC		EA	MA		EC
3​	Emp 2	MC	EC	EA		MC	EC
4​	Emp 3	MC	EC	EC		EC	EC

<tbody>
</tbody>

Sheet2 (summary)

Row\Col	A​	B​
1​	Emp 1
2​	4​
3​	Training	Type
4​	T1	MC
5​	T3	EA
6​	T4	MA
7​	T6	EC
8​

<tbody>
</tbody>

In A2 just enter:

=COUNTIFS(INDEX(Sheet1!$B$2:$G$4,MATCH($A1,Sheet1!$A$2:$A$4,0),0),"?*")

In A4 control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$4:A4)>$A$2,"",INDEX(Sheet1!$B$1:$G$1,SMALL(IF(1-(INDEX(Sheet1!$B$2:$G$4,MATCH($A$1,Sheet1!$A$2:$A$4,0),0)=""),COLUMN(Sheet1!$B$1:$G$1)-COLUMN(Sheet1!$B$1)+1),ROWS($A$4:A4))))

In B4 just enter and copy down:

IF($A4="","",INDEX(Sheet1!$B$2:$G$4,MATCH($A$1,Sheet1!$A$2:$A$4,0),MATCH($A4,Sheet1!$B$1:$G$1,0)))