# Table from a list

#### turtle81

##### New Member
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

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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.

This is not a List ?

 Source PivotTable DT Event Event Water 18/02/2019 12:53​ Water Month Name January 25/01/2019 18:22​ Electric 27/01/2019 21:13​ Water DT 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

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

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

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

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

Replies
7
Views
185
Replies
4
Views
150
Replies
5
Views
129
Replies
10
Views
323
Replies
5
Views
115

1,203,242
Messages
6,054,349
Members
444,717
Latest member
melindanegron

### 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.

### Which adblocker are you using?

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

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