+ Reply to Thread
Results 1 to 15 of 15

Locating pairs in set of data within time range

  1. #1
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2010
    Posts
    526

    Locating pairs in set of data within time range

    Hi all,

    Got a bit of a tough one here, which has confused me for days.

    I have a table of data which is shown in my attached workbook as Sheet1 (Most of the data has been deleted for various reasons).

    From this data the worksheet "Near Values" is produced, which looks at pairs of eastings and northings (from Columns Q and R in Sheet1) that are within 1km of each other.

    I am looking at being able to run through Sheet1 and locate any instances that any of these pairings feature within 10 minutes of one another AND where their corresponding number in column AD is different - if the criteria is met then highlight the corresponding cells in column AB.

    Any help much appreciated!

    Kind Regards,

    Chris
    Attached Files Attached Files
    Last edited by chrisellis250; 03-12-2020 at 09:42 AM.

  2. #2
    Valued Forum Contributor WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    869

    Re: Locating pairs in set of data within time range

    You didn't attach anything ...
    Excel 2016 on Windows 10, looking for rep!

  3. #3
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2010
    Posts
    526

    Re: Locating pairs in set of data within time range

    Thanks WBD!

  4. #4
    Registered User
    Join Date
    07-02-2013
    Location
    Cheshire
    MS-Off Ver
    Office Professional 2007
    Posts
    79

    Re: Locating pairs in set of data within time range

    Quote Originally Posted by WideBoyDixon View Post
    You didn't attach anything ...
    Oh yes Chris did - eventually.

  5. #5
    Valued Forum Contributor WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    869

    Re: Locating pairs in set of data within time range

    OK. This might work based on your stated requirements:

    Please Login or Register  to view this content.
    WBD

  6. #6
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2010
    Posts
    526

    Re: Locating pairs in set of data within time range

    Fantastic, thanks WBD! Works perfectly

  7. #7
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2010
    Posts
    526

    Re: Locating pairs in set of data within time range

    How about incorporating this into it. Where the parings are now within a specific distance and a specific time, I'd like to look at whether they "Look at" one another.

    When looking at the pairs, the 1st easting and northing is A and the 2nd (of the pair) is B. The pairs need to be looked at in conjunction with the data on the test workbook and specifically column O - which would contain a value of between 0 and 350 with the following parameters:

    If A is NW of B then any value in Column O works as follows:
    Between 90 – 180 for A would “Look at” B

    If A is NE of B then any value in Column O works as follows:
    Between 180-270 for A would “Look at” B

    AND

    If B is SE of A then any value in Column O works as follows:
    Between 270-0 would “Look at” A

    If B is SW of A then any value in Column O works as follows:
    Between 0-90 would “Look at” B

    Then highlighting where A and B would look at one another, or where A would look at B or vise versa. Not sure how to go about it as the first instance would be working out the position of the E&N's...

  8. #8
    Valued Forum Contributor WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    869

    Re: Locating pairs in set of data within time range

    That's a new question and it sounds very, very complex! Perhaps worth adding a new question.

    WBD

  9. #9
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2010
    Posts
    526

    Re: Locating pairs in set of data within time range

    I think i've cracked it tbf WBD, using your help! Thank you.

  10. #10
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2010
    Posts
    526

    Re: Locating pairs in set of data within time range

    Hi WBD,

    Thanks for your code, it works perfectly - however I have changed my dataset slightly. As with the test workbook which I attached, the Eastings and Northings are now in columns C and D as opposed to B and C, as there is an extra column of data in Column B. How would I go about changing this, so the code can search the pairs from "Near Values" to see if they feature within 10 mins of one another in Sheet1?

  11. #11
    Valued Forum Contributor WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    869

    Re: Locating pairs in set of data within time range

    Just change this line I think:

    Please Login or Register  to view this content.
    to this:

    Please Login or Register  to view this content.
    WBD

  12. #12
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2010
    Posts
    526

    Re: Locating pairs in set of data within time range

    Hi WBD,

    Thank you very much, that worked perfectly (again).

  13. #13
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2010
    Posts
    526

    Re: Locating pairs in set of data within time range

    Hi WBD,

    I have changed my data again, so now when looking between Near Values and Sheet1 I am looking to use the values from Column U and V from the Near values sheet.

    Similar to before, I am then looking in Sheet1 in Column M OR Q to see if these values feature within 10 minutes of one another, where there is a change in Column AD.

    Is there anyway to amend the code to work with this?

    Thank you very much for all your help so far, it is very much appreciated.
    Attached Files Attached Files

  14. #14
    Valued Forum Contributor WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    869

    Re: Locating pairs in set of data within time range

    I think it *should* be this but it's not finding any matches in the data set you provided:

    Please Login or Register  to view this content.
    WBD

  15. #15
    Forum Contributor
    Join Date
    03-04-2014
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2010
    Posts
    526

    Re: Locating pairs in set of data within time range

    Hi WBD,

    Massive thanks for your code, it works perfectly. Is there a way that when the cells are highlighted that a corresponding number is put in column D?

    For example, on the attached workbook the criteria is met for you code in rows 2 and 5, so the cell in column AB is coloured and the criteria is met in rows 78-80, so the cell in column AB is coloured again.

    As there are the only 2 where the criteria has been met I have manually added in numbers in the corresponding cell in column D, so D2 and D5 show the number 1 and D78-D80 show the number 2.

    Is there a way this can be automated with the original code?

    Many thanks,

    Chris
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 7
    Last Post: 10-17-2019, 12:00 PM
  2. Locating Duplicates in a range
    By AppSupportKarl in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-18-2011, 03:54 PM
  3. [SOLVED] Search a range, move data in pairs to a single column
    By wherbjr35 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-02-2011, 01:09 AM
  4. Locating sections of time value using vba.
    By Yappa in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-31-2008, 04:33 AM
  5. Locating closest value in range
    By additude in forum Excel General
    Replies: 16
    Last Post: 07-08-2008, 03:34 AM
  6. Replies: 6
    Last Post: 01-05-2006, 04:30 AM
  7. [SOLVED] Locating a value in a range from a reference.
    By mr_chattaway in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-21-2005, 11:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1