Table from a list

[turtle81]

I have a list of dates & times in column A, in colum B I have a list of events that happened at that time

I want to create a list of dates an times of every time a specific event happened

eg

A
18/2/19 12:53 Water
25/1/19 18:22 Electric
27/1/19 21:13 Water
27/1/19 23:30 Dust
27/1/19 23:50 Water
28/1/19 13:43 Electric
30/1/19 15:00 Dust
30/1/19 17:19 Water
31/1/19 11:56 Water

So I want a list all Water in Jan 19 & Creat a list as follows

27/1/19 21:13
27/1/19 23:50
30/1/19 17:19
31/1/19 11:56

I have it on pivot at he momnt but would Like an list instead

 

[Aladin Akyurek]

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

=IFERROR(INDEX($A$2:$A$10,SMALL(IF(INT($A$2:$A$10-DAY($A$2:$A$10)+1)=$E$1,IF($B$2:$B$10=$E$2,ROW($A$2:$A$10)-ROW(INDEX($A$2:$A$10,1,1))+1)),ROWS($E$4:E4))),"")

where E1 = 1/1/2019 and E2 = water.

 

[sandy666]

This is not a List ?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]Source[/td][td][/td][td][/td][td]PivotTable[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]DT[/td][td=bgcolor:#5B9BD5]Event[/td][td][/td][td=bgcolor:#DDEBF7]Event[/td][td=bgcolor:#DDEBF7]Water[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

18/02/2019 12:53​

[/td][td=bgcolor:#DDEBF7]Water[/td][td][/td][td=bgcolor:#DDEBF7]Month Name[/td][td=bgcolor:#DDEBF7]January[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

25/01/2019 18:22​

[/td][td]Electric[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

27/01/2019 21:13​

[/td][td=bgcolor:#DDEBF7]Water[/td][td][/td][td=bgcolor:#DDEBF7]DT[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

27/01/2019 23:30​

[/td][td]Dust[/td][td][/td][td]

27/01/2019 21:13​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

27/01/2019 23:50​

[/td][td=bgcolor:#DDEBF7]Water[/td][td][/td][td]

27/01/2019 23:50​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

28/01/2019 13:43​

[/td][td]Electric[/td][td][/td][td]

30/01/2019 17:19​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

30/01/2019 15:00​

[/td][td=bgcolor:#DDEBF7]Dust[/td][td][/td][td]

31/01/2019 11:56​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

30/01/2019 17:19​

[/td][td]Water[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

31/01/2019 11:56​

[/td][td=bgcolor:#DDEBF7]Water[/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[turtle81]

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

=IFERROR(INDEX($A$2:$A$10,SMALL(IF(INT($A$2:$A$10-DAY($A$2:$A$10)+1)=$E$1,IF($B$2:$B$10=$E$2,ROW($A$2:$A$10)-ROW(INDEX($A$2:$A$10,1,1))+1)),ROWS($E$4:E4))),"")

where E1 = 1/1/2019 and E2 = water.

Thank you mch that works pefectly

 

[Aladin Akyurek]

Thank you mch that works pefectly

You are welcome.

 

[turtle81]

Could I ask how this works, I have another sheet I could use this on except this time I want it to show if the % is below .95 or above 105

The field I want to lilst is in col AU & the % is AP

EG
% Planned PO Number
90 300
100 301
102 302
106 303
96 304
99 305
83 306

So the "list" would be
300
303
306

 

[turtle81]

Thought Id cracked it with

=IFERROR(INDEX($AU$5:$AU$22,SMALL(IF($AU$5:$AU$22=$AU$5,IF(OR($AV$5:$AV$22<$AK$72,$AV$5:$AV$22>$AK$72),ROW($AU$5:$AU$22)-ROW(INDEX($AU$5:$AU$22,1,1))+1)),ROWS($AU$64:AU65))),"")

But this only picks up the 1st entry on the list, irrelevent if the range falls in to my range

 

[Aladin Akyurek]

In response to your post #6 :

Book1
	A	B	C	D	E
1	% Planned	PO Number		<	95
2	90	300		>	105
3	100	301			list
4	102	302			300
5	106	303			303
6	96	304			306
7	99	305
8	83	306
Sheet1

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

=IFERROR(INDEX($B$2:$B$8,SMALL(IF(($A$2:$A$8 < $E$1)+($A$2:$A$8 > $E$2),ROW($B$2:$B$8)-ROW(INDEX($B$2:$B$8,1,1))+1),ROWS($E$4:E4))),"")