0

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

Improve this question
4
  • 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? ??? Commented Oct 19, 2015 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. Commented Oct 19, 2015 at 19:53
  • I still think we need to know your first logic like I said bellow to properly help you Commented Oct 19, 2015 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. Commented Oct 19, 2015 at 20:19

1 Answer 1

Reset to default
0

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
5
  • 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 Commented Oct 19, 2015 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 Commented Oct 19, 2015 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. Commented Oct 19, 2015 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. Commented Oct 19, 2015 at 18:36
  • I don't know enough about that data and logic based on your question to help you answer that right now Commented Oct 19, 2015 at 18:46

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.