Solved

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

Posted on 2013-11-20
18
218 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
 
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
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…

762 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now