I have 6 columns of Excel data, 3 sets of date/times matched with data points. The only problem is that the date/times are on different intervals. I want to only keep date/times that have all 3 data points.

For example, if I had:

Set A:

1/10 7
1/11 8 
1/12 13

Set B:

1/10 6
1/12 5
1/14 3

Set C:

1/9 4
1/10 3
1/11 14
1/12 5

I'd want to keep only the data for 1/10 and 1/12. Is there an easy way to do this? Thanks!

To make it more specific, my spreadsheet has 6 columns of data: Column A has dates/times where data exists for commodity 1, and then column B contains the value of commodity 1 at those dates/times. Similar for C/D and E/F. I want to filter it so that each column only contains data points associated with a date/time that all columns have data for.

Sample portion

  • When you write "keep", do you mean you want to delete the others? Or merely copy the one's that meet the criteria to another table? (Or something else). Is a VBA solution acceptable? Is the data in your worksheet as shown in your example -- a single column with the labels as shown? Two columns? ??? – Ron Rosenfeld Oct 19 '15 at 18:59
  • Either deleting the others or just copying the good ones are fine. I don't know what VBA is but if it's easy to do on Windows or from Excel then sure. – retterermoore Oct 19 '15 at 19:53
  • I still think we need to know your first logic like I said bellow to properly help you – Eric F Oct 19 '15 at 20:07
  • I'm not sure what you mean by first logic. The most low-level description would be: (p,q) in A x B or C x D or E x F such that there exists (p, r) in A x B, (p, s) in C x D, and (p, t) in E x F. – retterermoore Oct 19 '15 at 20:19

I believe I have what you are asking but your question was a little confusing. I broke it apart into sections to hopefully make it easier for you to learn but of course you can combine formulas to make it not as messy.

First note the left side where I have simply entered your data:

enter image description here

I have data on the far right which is your condition to look for, 1/10 and 1/12.

First I shorten the first part of your data just to the part before the space since that is the recognized pattern:

enter image description here

Once this is found then it is jut a matter of checking if your data is in this list or not, to which I ticked an "x" if it was:

enter image description here

Next, I gave them index values so we can summarize our results:

enter image description here

Lastly, it is easy to just sort our new index values and return their corresponding values:

enter image description here

Hope that this helps you get to your answer. If you have questions let me know, since like I said your question was kind of confusing.

| improve this answer | |
  • Yeah I probably should have shown a table to make it less confusing. I think there may have been some confusion - the 1/10 and 1/12 in this example are not necessarily already known and a big part of the problem is detecting which dates have data from all 3 sets. The motivation is that I have a lot of pricing data that I want to correlate, but each commodity is on the market for a different portion of the day, so I only want times where the commodities overlap. So in the example, I would want to give it the data and have it figure out that 1/10 and 1/12 are the dates to keep the data from – retterermoore Oct 19 '15 at 18:12
  • My example would still work though wouldn't it? Just populate the input on the far right with whatever the case happens to be – Eric F Oct 19 '15 at 18:19
  • Yeah but how would I calculate what should go on the far right without doing it manually? I have thousands of different timestamps some of which overlap and some of which don't. – retterermoore Oct 19 '15 at 18:23
  • Well what condition does your data have to meet for you to type those particular dates? You have to copy that logic into Excel logic basically. – Eric F Oct 19 '15 at 18:36
  • I don't know enough about that data and logic based on your question to help you answer that right now – Eric F Oct 19 '15 at 18:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.