How to: Return list of values from one column based on two criteria

AndreasMs

New Member
Joined
Jul 16, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hi there,

I'm making a sheet to analyze livestream participation and want to make the document automatically list the students who are absent for a given event (chosen by drop-down menu).

In Sheet A (see image), I have entered "Real name" of two participants. And underneath three livestream events marked them as Absent.
In Sheet B (see image), I want the names of any absent participant for a given livestream event to appear (can be selected using the dropdown in the top left corner).

So, using the examples in the images, if I choose Livestream event "Kick-Off", "Alibaba Story" or "PEST", I want both Andreas and Rasmus to appear as a list. And if I delete "Absent" in either of the events, the name should not appear on the list in Sheet B.

And, if, lets say, the list got 100 names on it, I want the names of those who are absent for any of those events to appear when I choose either event.

I'd then make a formula to say if "x real name" then (DingTalk Name, Group Name, Tutor) to fill in the rest based on the name that appears in the list.

I have tried with Index + Match and Lookup, but I'm not really able to figure out the logic that says: "When X event is chosen -> lookup "Absent" in Y Column -> return name in 3rd column."

Hope someone got some good ideas. I'm new to this forum, so not sure if I can upload the file if that is of any help.

Looking forward to hearing from you - thank you very much in advance!
Sincerely,
Andreas
 

Attachments

  • Sheet A.JPG
    Sheet A.JPG
    32.2 KB · Views: 6
  • Sheet B.JPG
    Sheet B.JPG
    32.7 KB · Views: 6

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,308
Office Version
  1. 2016
Platform
  1. Windows
Hi AndreaMs,

Here's my sample SHEET A

AndreasMs.xlsx
CDEFGHIJKL
2Kick-OffAlibabaPESTSoldering Brazing
3Real Name# of Absent13-Jul-2014-Jul-2016-Jul-2018-Jul-2020-Jul-20
4Andreas3AbsentAbsentAbsent
5Rasmus3AbsentAbsentAbsent
6John1Absent
7Bert0
8Alex2AbsentAbsent
9Robin1Absent
10Susan1Absent
11Vanessa2AbsentAbsent
12Hannah0
13James3AbsentAbsentAbsent
14
SHEET A
Cell Formulas
RangeFormula
F4:F13F4=COUNTIF(H4:L4,"Absent")


Here's my SHEET B

AndreasMs.xlsx
ABCDEFG
1Select eventKick-Off
2
7
8
9Absent participants for the livestream Kick-Off
10Real Name
11Andreas
12Rasmus
13Susan
14Vanessa
15James
16 
SHEET B
Cell Formulas
RangeFormula
C9C9="Absent participants for the livestream "&'SHEET B'!$B$1
C11:C16C11=IFERROR(INDEX('SHEET A'!$C$4:$C$108,AGGREGATE(15,6,ROW('SHEET A'!$H$4:$L$108)/(('SHEET A'!$H$4:$L$108<>"")*(COLUMN('SHEET A'!$H$4:$L$108)=MATCH('SHEET B'!$B$1,'SHEET A'!$H$2:$L$2,0)+COLUMN('SHEET A'!$G$1))),ROW()-ROW($C$10))-ROW('SHEET A'!$C$3)),"")
Cells with Data Validation
CellAllowCriteria
B1List='SHEET A'!$H$2:$L$2
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

Assuming that you have the FILTER function in your Excel 365, could you use something like this?

My Sheet A

AndreasMs 2020-07-17 1.xlsm
ABCDEFGHIJKL
1
2Kick-OffAlibabaPESTSoldering Brazing
3Ding Talk NameReal NameGroup Name# of Absent13-Jul-2014-Jul-2016-Jul-2018-Jul-2020-Jul-20
4DTN1AndreasGN13AbsentAbsentAbsent
5DTN2RasmusGN23AbsentAbsentAbsent
6DTN3JohnGN31Absent
7DTN4BertGN40
8DTN5AlexGN52AbsentAbsent
9DTN6RobinGN61Absent
10DTN7SusanGN71Absent
11DTN8VanessaGN82AbsentAbsent
12DTN9HannahGN90
13DTN10JamesGN103AbsentAbsentAbsent
14
Sheet A



For this layout of Sheet B, the FILTER formula would need to be entered into cell C11 only. The other results would automatically 'spill' across and down to the other required cells.

AndreasMs 2020-07-17 1.xlsm
ABCDE
1Select eventKick-Off
2
8
9Absent participants for the livestream Kick-Off
10Ding Talk NameReal NameGroup Name
11DTN1AndreasGN1
12DTN2RasmusGN2
13DTN7SusanGN7
14DTN8VanessaGN8
15DTN10JamesGN10
16
Sheet B
Cell Formulas
RangeFormula
C9C9="Absent participants for the livestream "&'Sheet B'!$B$1
C11:E15C11=FILTER('Sheet A'!B4:D200,FILTER('Sheet A'!H4:L200,'Sheet A'!H2:L2=B1)="Absent","")
Dynamic array formulas.
 

AndreasMs

New Member
Joined
Jul 16, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hi guys,

Sorry for my slow reply and thank you so much for both your time, effort and quick response. Really appreciate it!
In the end, the filter formula does the trick in the simplest way, so I end up using that one. If I may, a question in extension of that one, how do I ask it to filter Absent + e.g. those who view less than 30 minutes ("<00:30:00). I tried adding a + sign but kind really get it to work :(
Thank you so much for you help, truly amazing to see how quick you both respond with such detailed answers!

I got a follow up soon on the same document. Will remember to upload the full doc by then!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

how do I ask it to filter Absent + e.g. those who view less than 30 minutes ("<00:30:00).
That seems to bear no relationship to what you have given us so far. You may be very familiar with your data, layout & requirement but we only have what you tell/show us. I cannot see anything that resembles "view" or time values in the images you provided.

You would need to provide some small representative sample data, the expected results (preferably with XL2BB) and explain in relation to that sample data.
 

AndreasMs

New Member
Joined
Jul 16, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hi! Yes, you are right. Sorry. Please see the two images below. I'll add Xl2BB as well for parts of it.

V2 副本 2C July September 2020 Livestream Data Template.xlsx
ABCDEFGHIJ
2ParticipantsTutorAttendance problemsAlibaba StoryPESTCainiao - LectureCainiao - Interview
3DingTalk NameReal NameGroup Name# of Absent# less 1hr07-13-202007-16-202007-21-202007-21-2020
4李俊生-NPU-跨境电商-马来西亚0201:54:3602:03:5300:53:2900:49:00
5陈耀盛-NPU-跨境电商-马来西亚0400:50:3400:00:1200:49:3400:50:25
6杨欣仪-NPU-电子商务-马来西亚0201:47:3602:00:3600:53:3500:51:09
7罗曼 SHU 电子商务 俄罗斯1201:39:08Absent00:51:1300:27:35
8黄玮慷-NPU-跨境电商-马来西亚0201:54:2501:09:1000:52:0600:49:58
9蔡汶延-NPU-电商-马来西亚0201:54:5802:03:5900:52:4500:48:41
10陈勇成 NPU 电子商务 马来西亚0201:55:1602:04:3600:53:4100:51:12
11朱家仪-NPU-电子商务-马来西亚0201:54:3902:04:1600:52:5800:51:09
12黄惠忆-SHU-国贸-马来西亚0201:55:0902:04:5100:53:3600:51:10
13萧金盛-NPU-电子商务-马来西亚12Absent02:04:4400:53:4100:51:13
14龚世恩Kung Sie Ern-NPU-...0201:55:0202:04:0000:53:4100:51:13
15林悦-HIT-电子商务-马来西亚0201:55:0202:04:0500:53:1800:51:06
16梁守丰-HIT-跨境电商-马来西亚1201:54:57Absent00:53:1000:51:13
17爱玲Aizhan—SHU—电商—哈萨克斯坦0301:55:0500:30:0500:53:1000:49:23
18爱娜Aina -SHU-电子商务-哈萨克斯塔0201:27:2901:47:1700:51:4600:36:28
19陈成哲 + HIT + 电子商务+ 马来0201:54:2202:00:5700:53:3600:49:51
20沈锦升-NPU-电子商务-马来西亚0201:52:2002:03:0400:51:2300:50:59
21艾力-电子商务-NPU-俄罗斯12Absent01:20:4800:53:3600:51:15
2 - Participation
Cell Formulas
RangeFormula
G2G2='1 - Post-Event Info Input'!$A2
H2H2='1 - Post-Event Info Input'!$A3
I2I2='1 - Post-Event Info Input'!$A4
J2J2='1 - Post-Event Info Input'!$A5
G3G3='1 - Post-Event Info Input'!$B2
H3H3='1 - Post-Event Info Input'!$B3
I3I3='1 - Post-Event Info Input'!$B4
J3J3='1 - Post-Event Info Input'!$B5
E4:E21E4=COUNTIF(G4:T4,"Absent")
F4:F21F4=COUNTIF(G4:T4,"<01:00:00")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I4:J82Cell Value="Absent"textNO
I4:J82Cell Value<0,0208333333333333textNO
I4:J82Cell Valuebetween 0,0208449074074074 and 0,0312384259259259textNO
I4:J82Cell Value>0,03125textNO
G4:G81Cell Value="Absent"textNO
G4:G81Cell Valuebetween 0,0000115740740740741 and 0,0208449074074074textNO
G4:G81Cell Valuebetween 0,0208449074074074 and 0,0416666666666667textNO
G4:G81Cell Value>0,0416666666666667textNO
H4:H81,G83:T90,G82:H82,K4:T82Cell Value="Absent"textNO
H4:H81,G83:T90,G82:H82,K4:T82Cell Valuebetween 0,0000115740740740741 and 0,0208449074074074textNO
H4:H81,G83:T90,G82:H82,K4:T82Cell Valuebetween 0,0208449074074074 and 0,0416666666666667textNO
H4:H81,G83:T90,G82:H82,K4:T82Cell Value>0,0416666666666667textNO
 

Attachments

  • Excel - Absence.JPG
    Excel - Absence.JPG
    185.6 KB · Views: 1
  • Excel Participation.JPG
    Excel Participation.JPG
    211.5 KB · Views: 1

AndreasMs

New Member
Joined
Jul 16, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
V2 副本 2C July September 2020 Livestream Data Template.xlsx
ABCDEFG
1Select eventPEST
2
3
4Overview
5Total InvitedTotal OpenTotal AbsenceDuration
67976300:52:02
7
8
9Absent participants for the livestream PEST
10DingTalk NameReal NameGroupTutor
11罗曼 SHU 电子商务 俄罗斯000
12梁守丰-HIT-跨境电商-马来西亚000
13李凯源-NPU-电子商务-马来西亚000
14罗君君-NPU-电子商务-马来西亚000
15王淇萱-NPU-跨境电商-马来西亚000
16张凯伊 哈工大 电商 马来西亚000
17杨欣悦-西工大-跨境电商-马来西亚000
18陈紫薇+NPU+跨境电商+马来西亚000
19陆志豪LOKECHEEHOU-跨境电商-NPU000
20赖慧珊-NPU-跨境电商-马来西亚000
21林慧如DISA 西工大 跨境电商 马来西亚000
22李欣燕-HIT-电子商务-马来西亚000
23蔡蕙蔚 西工大 电商 马来西亚000
24adamAdamYouth SpiritAndreas
25谢宁-电子商务-NPU-俄罗斯000
26
27
28
3 - Event Analysis
Cell Formulas
RangeFormula
C6C6=LOOKUP($B$1,'1 - Post-Event Info Input'!$A$1:$A$17,'1 - Post-Event Info Input'!$D$1:$D$17)
D6D6=LOOKUP($B$1,'1 - Post-Event Info Input'!$A$1:$A$17,'1 - Post-Event Info Input'!$E$1:$E$17)
E6E6=LOOKUP($B$1,'1 - Post-Event Info Input'!$A$1:$A$17,'1 - Post-Event Info Input'!$F$1:$F$17)
F6F6=LOOKUP($B$1,'1 - Post-Event Info Input'!$A$1:$A$17,'1 - Post-Event Info Input'!$C$1:$C$17)
C9C9="Absent participants for the livestream "&B1
C11:F25C11=FILTER('2 - Participation'!A4:D90,FILTER('2 - Participation'!G4:T90,'2 - Participation'!G2:T2=B1)="Absent","")
Dynamic array formulas.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
I'm not sure I have understood correctly but is this it?

Code:
=FILTER('2 - Participation'!A4:D90,(FILTER('2 - Participation'!G4:T90,'2 - Participation'!G2:T2=B1)="Absent")+(FILTER('2 - Participation'!G4:T90,'2 - Participation'!G2:T2=B1)<1/48)*(FILTER('2 - Participation'!G4:T90,'2 - Participation'!G2:T2=B1)<>""),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,123,380
Messages
5,601,297
Members
414,440
Latest member
Kim0204

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top