Table from a list

turtle81

New Member
Joined
Feb 19, 2019
Messages
9
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
This is not a List ?

SourcePivotTable
DTEventEventWater
18/02/2019 12:53​
WaterMonth NameJanuary
25/01/2019 18:22​
Electric
27/01/2019 21:13​
WaterDT
27/01/2019 23:30​
Dust
27/01/2019 21:13​
27/01/2019 23:50​
Water
27/01/2019 23:50​
28/01/2019 13:43​
Electric
30/01/2019 17:19​
30/01/2019 15:00​
Dust
31/01/2019 11:56​
30/01/2019 17:19​
Water
31/01/2019 11:56​
Water
 

turtle81

New Member
Joined
Feb 19, 2019
Messages
9
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
 

turtle81

New Member
Joined
Feb 19, 2019
Messages
9
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

New Member
Joined
Feb 19, 2019
Messages
9
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
In response to your post #6 :


Book1
ABCDE
1% PlannedPO Number<95
290300>105
3100301list
4102302300
5106303303
696304306
799305
883306
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))),"")
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,108
Messages
5,857,435
Members
431,879
Latest member
KiwDaWabbit

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