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
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Aladin Akyurek

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

Well-known Member
Joined
Oct 24, 2015
Messages
6,757
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,192
In response to your post #6 :

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">% Planned</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">PO Number</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"><</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">95</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">90</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">300</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">105</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">100</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">301</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">list</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">102</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">302</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">300</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">106</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">303</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">303</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">96</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">304</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">306</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">99</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">305</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">83</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">306</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p>

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

Watch MrExcel Video

Forum statistics

Threads
1,109,041
Messages
5,526,416
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top