Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-11-20
18
Medium Priority
?
265 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

721 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