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?

[TABLE="width: 458"]
<tbody>[TR]
[TD][/TD]
[TD] [/TD]
[TD="colspan: 2"]Training courses[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Empoyee[/TD]
[TD]T1[/TD]
[TD]T2[/TD]
[TD]T3[/TD]
[TD]T4[/TD]
[TD]T5[/TD]
[TD]T6[/TD]
[/TR]
[TR]
[TD]Emp 1[/TD]
[TD]MC[/TD]
[TD] [/TD]
[TD]EA[/TD]
[TD]MA[/TD]
[TD][/TD]
[TD]EC[/TD]
[/TR]
[TR]
[TD]Emp 2[/TD]
[TD]MC[/TD]
[TD]EC[/TD]
[TD]EA[/TD]
[TD] [/TD]
[TD]MC[/TD]
[TD]EC[/TD]
[/TR]
[TR]
[TD]Emp 3[/TD]
[TD]MC[/TD]
[TD]EC[/TD]
[TD]EC[/TD]
[TD] [/TD]
[TD]EC[/TD]
[TD]EC[/TD]
[/TR]
</tbody><colgroup><col><col><col span="5"></colgroup>[/TABLE]


The result should be

When I select employee Emp1 , I should get

[TABLE="width: 241"]
<tbody>[TR]
[TD][/TD]
[TD]Training[/TD]
[TD]Type[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD]T1[/TD]
[TD]MC[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD]T3[/TD]
[TD]EA[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD]T4[/TD]
[TD]MA[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD]T6[/TD]
[TD]EC[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]

 

[BrianJN1]

Working from your link I derived this:
[TABLE="width: 477"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Employee name[/TD]
[TD]Emp 4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Training[/TD]
[TD]Type[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1 [/TD]
[TD] T1[/TD]
[TD]MC[/TD]
[TD][/TD]
[TD]Emp 4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD] T2[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD] T3[/TD]
[TD]EC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD] T4[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD] T5[/TD]
[TD]EC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD] T6[/TD]
[TD]EC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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)

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD] Empoyee[/TD]
[TD] T1[/TD]
[TD] T2[/TD]
[TD] T3[/TD]
[TD] T4[/TD]
[TD] T5[/TD]
[TD] T6[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD] Emp 1[/TD]
[TD] MC[/TD]
[TD][/TD]
[TD] EA[/TD]
[TD] MA[/TD]
[TD][/TD]
[TD] EC[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD] Emp 2[/TD]
[TD] MC[/TD]
[TD] EC[/TD]
[TD] EA[/TD]
[TD][/TD]
[TD] MC[/TD]
[TD] EC[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD] Emp 3[/TD]
[TD] MC[/TD]
[TD] EC[/TD]
[TD] EC[/TD]
[TD][/TD]
[TD] EC[/TD]
[TD] EC[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2 (summary)

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD] Emp 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD] Training[/TD]
[TD] Type[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD] T1[/TD]
[TD] MC[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD] T3[/TD]
[TD] EA[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD] T4[/TD]
[TD] MA[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD] T6[/TD]
[TD] EC[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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)))