Hi,
I'm trying to create a spreadsheet that has passed my level of expertise! Usually use recorded macro's and formulas but think this might be a VBA code job?
I have attached a simplified version of my sheet to assist.
On the data tab i have several columns of data. The only pertinent columns of data are the ones highlighted yellow (Name, ID, Date & time) however i have left in the other columns and populated the cells with "data" to reflect that there are several other columns in this sheet.
In column T i have a cell called seconds (currently 15) which can/will be changed.
My objective for this spreadsheet is to take each row of data (starting with row 2) and check it against all other rows in the sheet and count if my criteria is met. Once row 2 is done, continue for all rows.
The criteria i need it to count against is as follows:
- Name (col A) does NOT equal same name as the row being checked (therefore looking for different names)
- ID of another row is the SAME as that being checked
- Date and time of another row are within X seconds of the date and time of row being checked (X being the T2 cell - currently 15)
Where the criteria is met i would like it to count as 1 and return the result on the results tab as a total count of a pair of names. I have manually inserted the counts for the sample data. So to clarify where John & Paul are paired together there are 3 instances where the ID is the same and the date and time are within 15 seconds of each other and at the other end of the scale there are 0 instances where George & Ringo have the same ID and are within 15 seconds of each other.
Hope that makes sense! In summary i'm looking for some kind of code that will loop through and result each row until all rows are checked.
Few things to be aware of:
- The amount of rows that will be in the real spreadsheet are likely to run into the thousands
- The amount of names are likely to run into the hundreds
- There is no issue if any solution takes a while to process
- T2 cell is a user defined time but realistically never going to look beyond 30 seconds
- I need each row to check all rows and not just the ones below it. E.g when the loop hits row 3 i still need it to look at row 2 aswell as row 4 onwards
If anyone is capable of providing code that i can attach to a button i would be eternally grateful!
Many thanks in advance
Bookmarks