# Locating pairs in set of data within time range

1. ## 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

2. ## Re: Locating pairs in set of data within time range

You didn't attach anything ...

Thanks WBD!

4. ## Re: Locating pairs in set of data within time range

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

5. ## 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. ## Re: Locating pairs in set of data within time range

Fantastic, thanks WBD! Works perfectly

7. ## 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. ## 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. ## Re: Locating pairs in set of data within time range

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

10. ## 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. ## 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. ## Re: Locating pairs in set of data within time range

Hi WBD,

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

13. ## 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.

14. ## 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. ## 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

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

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