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

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
colonialiu20Asked:
Who is Participating?
 
NBVCCommented:
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
 
NBVCCommented:
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
 
NBVCCommented:
Note:  Made a small adjustment to formula above... changed D1 to C1....
Copy-of-RoundOrOne.xlsx
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
colonialiu20Author Commented:
Terrific, now how can i get my unique list of records on the right side of the spreadsheet?
0
 
NBVCCommented:
Is the id unique to the dates?
0
 
colonialiu20Author Commented:
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
 
NBVCCommented:
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
 
colonialiu20Author Commented:
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
 
Saqib Husain, SyedEngineerCommented:
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
 
colonialiu20Author Commented:
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
 
Saqib Husain, SyedEngineerCommented:
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
 
colonialiu20Author Commented:
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
 
Saqib Husain, SyedEngineerCommented:
Sorry I forgot the bit about putting this formula in D2 and copying down.

=INT(B2)
0
 
Saqib Husain, SyedEngineerCommented:
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
 
NBVCCommented:
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
 
colonialiu20Author Commented:
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
 
NBVCCommented:
0
 
colonialiu20Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.