asked on

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

NBVC

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

NBVC

Note: Made a small adjustment to formula above... changed D1 to C1....
Copy-of-RoundOrOne.xlsx

colonialiu20

ASKER

Terrific, now how can i get my unique list of records on the right side of the spreadsheet?

NBVC

Is the id unique to the dates?

colonialiu20

ASKER

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.

NBVC

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.

colonialiu20

ASKER

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

Saqib Husain

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

colonialiu20

ASKER

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.

Saqib Husain

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

colonialiu20

ASKER

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

Saqib Husain

Sorry I forgot the bit about putting this formula in D2 and copying down.

=INT(B2)

Saqib Husain

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

NBVC

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

colonialiu20

ASKER

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

NBVC

See attached.
Copy-of-RoundOrOne-rev5-1.xlsx

colonialiu20

ASKER

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

ASKER CERTIFIED SOLUTION

NBVC

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial