Solved

use excel to calculate wether a record is one way or round trip

Posted on 2013-11-20
18
259 Views
Last Modified: 2014-01-30
I have a spreadsheet (see attached).  Its laid out as

Column A = id #'s
Column B = date times
Column C should calculate wether a row is a round trip or one way based upon the following rules.  I put data in column C just to show you what the values should be.  This should be calculated automatically.

For each id#, for each date, Id like to count the # of entries.  For example, id 1001, starting in row 2, has 3 entries for 11/1/2013.  After we count the # of entries an ID has for the day, the following rules should be applied.  If the # of entries is:

1
This should be considered "One Way."  All records for that id for the day should be classified as "One Way"

2
If the difference between the two times is less than 2.5 hours, all records for that id for the day should be classified as "One Way"

If the difference between the two times is greater than 2.5 hours, all records for that id for the day should be classified as "Round Trip"

3
If the difference between any two times is more than 2.5 hours apart, all records for that id for the day should be classified as "Round Trip"

Otherwise, all records for that id for the day should be classified as "One Way"

4
If the difference between any two times is more than 2.5 hours apart, all records for that id for the day should be classified as "Round Trip"

Otherwise, all records for that id for the day should be classified as "One Way"

5 or greater
If the difference between any two times is more than 2.5 hours apart, all records for that id for the day should be classified as "Round Trip"

Otherwise, all records for that id for the day should be classified as "One Way"

In the end, Id like a list of each id/date combination, along with whether that corresponding date was roundtrip or one way, like i2:k6
RoundOrOne.xlsx
0
Comment
Question by:colonialiu20
  • 7
  • 7
  • 4
18 Comments
 
LVL 23

Expert Comment

by:NBVC
ID: 39663764
Try:

=IF(AND($A2=$A1,INT($B2)=IFERROR(INT($B1),0)),C1,LOOKUP(((LOOKUP(2,1/(($A$2:$A$16=$A2)*(INT($B$2:$B$16)=INT($B2))),MOD($B$2:$B$16,1)))-MOD($B2,1))*24,{0,2.5},{"One Way","Round Trip"}))

Open in new window


copied down
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39663795
Note:  Made a small adjustment to formula above... changed D1 to C1....
Copy-of-RoundOrOne.xlsx
0
 

Author Comment

by:colonialiu20
ID: 39664016
Terrific, now how can i get my unique list of records on the right side of the spreadsheet?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Expert Comment

by:NBVC
ID: 39664045
Is the id unique to the dates?
0
 

Author Comment

by:colonialiu20
ID: 39664112
For each date, there should be a id# and round trip or one way designation. The time doesn't matter. The results should look similar to what I put on the right side  of the spreadsheet.
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39664131
You can get the firs column of ID's either by using Data|Advance Filter, and then selecting column A, and setting Unique Records Only and Copy to Range: I1...

or by formula in I2:

=IFERROR(INDEX($A$2:$A$16,MATCH(0,INDEX(COUNTIF($I1:I$1,$A$2:$A$16),0),0)),"")

copied down as far as you need to cover all unique records.

in J2 then for either above option:

=IF(I2="","",INT(INDEX($B$2:$B$16,MATCH(I2,$A$2:$A$16,0))))

copied down

and in K2:

=IF(I2="","",INDEX($C$2:$C$16,MATCH(I2,$A$2:$A$16,0)))

copied down.
0
 

Author Comment

by:colonialiu20
ID: 39664707
Im still having an issue.  Ive attached an updated document.  Youll see in rows 17 and 18, ive added two more records for the next day.  The round trip / one way formula doesnt appear to be calculation.  In addition, the unique records that are on the right side of the page are not finding 1001, 11/2/13, one way.  1001, 11/2/13, one way should be in i7:k7.
RoundOrOne-rev3.xlsx
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39664738
DO NOT AWARD POINTS TO THIS COMMENT

While entering date/time there should be a space before am/pm for excel to recognize it as a date/time
0
 

Author Comment

by:colonialiu20
ID: 39664751
Thank you ssaqibh.  That solved my first problem.  I still need help coming up with a unite list of id, date, round trip/one way.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39665007
Try these formulas in i2 and j2

=IFERROR(INDEX($A$2:$A$18,MATCH(0,INDEX(COUNTIFS($I$1:I1,$A$2:$A$18,J$1:$J1,$D$2:$D$18),0),0)),"")

=IFERROR(INT(INDEX($B$2:$B$18,MATCH(0,INDEX(COUNTIFS($I1:I$1,$A$2:$A$18,J1:$J$1,$D$2:$D$18),0),0))),"")
0
 

Author Comment

by:colonialiu20
ID: 39665553
ssaqibh,

That doesnt appear to have worked.  In the spreadsheet i attached, rev4, i put some data in n2:p7.  This is an exact example of what data should be getting pulled.   I tried your two formulas but did not have luck.  Please see attached file.
RoundOrOne-rev4.xlsx
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39665564
Sorry I forgot the bit about putting this formula in D2 and copying down.

=INT(B2)
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39665567
I have now modified the formulas to not require the helper column in column D

=IFERROR(INDEX($A$2:$A$18,MATCH(0,INDEX(COUNTIFS($I$1:I1,$A$2:$A$18,J$1:$J1,INT($B$2:$B$18)),0),0)),"")

=IFERROR(INT(INDEX($B$2:$B$18,MATCH(0,INDEX(COUNTIFS($I1:I$1,$A$2:$A$18,J1:$J$1,INT($B$2:$B$18)),0),0))),"")
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39665611
I had asked if the Id's were unique to the dates in my post # ID: 39664045 above....

when you use ssaqibh's formulas you will get repeats at the bottom... to avoid, you can first get a count of unique id/date combos with a formula in H1:

=COUNT(1/FREQUENCY(IF($A$2:$A$18<>"",IF($B$2:$B$18<>"",MATCH($A$2:$A$18&INT($B$2:$B$18),$A$2:$A$18&INT($B$2:$B$18),0))),ROW($A$2:$A$18)-ROW($A$2)+1))

confirmed with CTRL+SHIFT+ENTER not just ENTER.

Then adjust his formula for I2 to:

=IF(ROWS(I$2:I2)>$H$1,"",INDEX(A$2:A$18,MATCH(0,INDEX(COUNTIFS($I1:$I$1,$A$2:$A$18,$J1:$J$1,INT($B$2:$B$18)),0),0)))
0
 

Author Comment

by:colonialiu20
ID: 39665625
the id's should repeat for every date that is present.  thats why in my example in n2:p7, you see id# 1001 listed twice.  The second time that id is listed is for a different date.  Could one of you take the spreadsheet attached, rev5, and try to get results that are exactly like n2:p7?
RoundOrOne-rev5.xlsx
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39665723
0
 

Author Comment

by:colonialiu20
ID: 39668806
Ive added rows 19 and 20.  However, I cant get the new unique record to show up in i8:k8.  the new record should be 1002, 12/2/13, One Way.  What am i missing?
RoundOrOne-rev7.xlsx
0
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
ID: 39669345
Perhaps we can redefine your table to make it a Dynamic Named Range. so that you can expand and contract the table

So go to Formulas tab, then Define Name.

Enter a Name like MyData

And define it with formula:

=OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A:$A)-1,3)

Then your formulas in I2:K2, respectively are:

=IFERROR(INDEX(INDEX(MyData,0,1),MATCH(0,INDEX(COUNTIFS($I$1:I1,INDEX(MyData,0,1),J$1:$J1,INT(INDEX(MyData,0,2))),0),0)),"")

=IFERROR(INT(INDEX(INDEX(MyData,0,2),MATCH(0,INDEX(COUNTIFS($I1:I$1,INDEX(MyData,0,1),J1:$J$1,INT(INDEX(MyData,0,2))),0),0))),"")

=IFERROR(INDEX(INDEX(MyData,0,3),MATCH(0,INDEX(COUNTIFS($I1:I$1,INDEX(MyData,0,1),J1:$J$1,INT(INDEX(MyData,0,2))),0),0)),"")

each copied down as far as needed.
Copy-of-RoundOrOne-rev7-1.xlsx
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question