Creating Formula to help sort values from one table to another

yessir

Board Regular
Joined
Jun 7, 2019
Messages
91
Is it possible to create a formula where I would be able to generate values in the (sample) table I've entered below. However, the formula would need to be able to distingush the values in column A and when it changes from xxxx to yyyy. The main objective of this formula would be to count how many cars are in the lot at every given minute within the timeframe. Additionally, if the value in Column E is true the formula should disregard the data within that row.

DATA:
ABCDE
Lot 1
///16:0116:28FALSE
Lot 1///16:0816:13FALSE
Lot 2///16:0816:33FALSE
Lot 2
///10:0516:26FALSE
Lot 2///16:0216:11FALSE

<colgroup><col width="64" style="width: 48pt;" span="5"><tbody>
</tbody>

SAMPLE TABLE:

16:0016:0116:0216:0316:0416:0516:0616:0716:0816:0916:1016:1116:1216:1316:14
Lot 1011111112222221
Lot 2001112223332222
<colgroup><col width="64" style="width: 48pt;"> <col width="64" style="width: 48pt;" span="15"> <tbody> </tbody>







<colgroup><col width="64" style="width: 48pt;"><colgroup><col width="64" style="width: 48pt;" span="15"><tbody>
</tbody>
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Is it possible to create a formula where I would be able to generate values in the (sample) table I've entered below. However, the formula would need to be able to distingush the values in column A and when it changes from xxxx to yyyy. The main objective of this formula would be to count how many cars are in the lot at every given minute within the timeframe. Additionally, if the value in Column E is true the formula should disregard the data within that row.

DATA:
A
B
C
D
E
Lot 1
///
16:01
16:28
FALSE
Lot 1
///
16:08
16:13
FALSE
Lot 2
///
16:08
16:33
FALSE
Lot 2
///
10:05
16:26
FALSE
Lot 2
///
16:02
16:11
FALSE

<tbody>
</tbody>

SAMPLE TABLE:

16:00
16:01
16:02
16:03
16:04
16:05
16:06
16:07
16:08
16:09
16:10
16:11
16:12
16:13
16:14
Lot 1
0
1
1
1
1
1
1
1
2
2
2
2
2
2
1
Lot 2
0
0
1
1
1
2
2
2
3
3
3
2
2
2
2

<tbody>
</tbody>




<tbody>
</tbody>


Any kind of vague direction is appreciated.
 
Upvote 0
Try this


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:45.62px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:50.38px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /><col style="width:38.02px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td><td >O</td><td >P</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Lot 1</td><td >///</td><td style="text-align:right; ">16:01</td><td style="text-align:right; ">16:28</td><td >FALSE</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Lot 1</td><td >///</td><td style="text-align:right; ">16:08</td><td style="text-align:right; ">16:13</td><td >FALSE</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Lot 2</td><td >///</td><td style="text-align:right; ">16:08</td><td style="text-align:right; ">16:33</td><td >FALSE</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Lot 2</td><td >///</td><td style="text-align:right; ">10:05</td><td style="text-align:right; ">16:26</td><td >FALSE</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Lot 2</td><td >///</td><td style="text-align:right; ">16:02</td><td style="text-align:right; ">16:11</td><td >FALSE</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td style="text-align:right; ">16:00</td><td style="text-align:right; ">16:01</td><td style="text-align:right; ">16:02</td><td style="text-align:right; ">16:03</td><td style="text-align:right; ">16:04</td><td style="text-align:right; ">16:05</td><td style="text-align:right; ">16:06</td><td style="text-align:right; ">16:07</td><td style="text-align:right; ">16:08</td><td style="text-align:right; ">16:09</td><td style="text-align:right; ">16:10</td><td style="text-align:right; ">16:11</td><td style="text-align:right; ">16:12</td><td style="text-align:right; ">16:13</td><td style="text-align:right; ">16:14</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >Lot 1</td><td style="text-align:right; ">0</td><td style="text-align:right; ">1</td><td style="text-align:right; ">1</td><td style="text-align:right; ">1</td><td style="text-align:right; ">1</td><td style="text-align:right; ">1</td><td style="text-align:right; ">1</td><td style="text-align:right; ">1</td><td style="text-align:right; ">2</td><td style="text-align:right; ">2</td><td style="text-align:right; ">2</td><td style="text-align:right; ">2</td><td style="text-align:right; ">2</td><td style="text-align:right; ">2</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >Lot 2</td><td style="text-align:right; ">1</td><td style="text-align:right; ">1</td><td style="text-align:right; ">2</td><td style="text-align:right; ">2</td><td style="text-align:right; ">2</td><td style="text-align:right; ">2</td><td style="text-align:right; ">2</td><td style="text-align:right; ">2</td><td style="text-align:right; ">3</td><td style="text-align:right; ">3</td><td style="text-align:right; ">3</td><td style="text-align:right; ">3</td><td style="text-align:right; ">2</td><td style="text-align:right; ">2</td><td style="text-align:right; ">2</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B10</td><td >=COUNTIFS($A$1:$A$5,$A10,$C$1:$C$5,"<=" & B$9,$D$1:$D$5,">="&B$9,$E$1:$E$5,'FALSE')</td></tr></table></td></tr></table>
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

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
Back
Top