formula in excel for including only neg/neg and positive positive values in separate columns

Ok. I have an excel file with data. I want to control for an inverse relationship between the two columns so I want to create a separate set of columns that include only values with either negative values in both columns or positive values in both columns. There will be more rows than listed below and additional columns as seen in the attached file. For example, I have the data below: If this can't be done in excel, I how would it be done in perl when looking at an excel file? The output file could simply contain the final three columns of data.

column A    column M column O
1/1/80        -1.30        2.22
2/1/80        .34           .43
3/1/80        .55           -.57
4/1/80        -.22          -.90

My update excel file would contain this

column A    column M column O column Q column R column S
1/1/80        -1.30        2.22          2/1/80        .34           .43
2/1/80        .34           .43            4/1/80        -.22          -.90
3/1/80        .55           -.57
4/1/80        -.22          -.90
Mastersdata.xls
libertyforall2Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

alpha456Commented:
This can be done in Excel, almost for sure, but it slightly depends on what you want at the end. So...
1.  You could add a column that multiplies columns M and O. In that new column, any positive number is a result of both M and O being positive or negative (or use an IF statement or an AND statement to achieve the same thing), so then you can set a filter on all your data and filter out all negative numbers, leaving your spreadsheet just showing the rows you are interested in. You could then manually copy the data to another sheet.

2. You could create columns Q, R and S as you show them in your question using formulae with lookup formulae, but the formulae would have to go all the way down to row 385, whereas the information you want to show would stop before then, so you would have to have zeroes or something in the cells below that point. Not clear if this matters to you.

3. Otherwise, you could use a macro to run down your data, select the rows that meet the criteria and copy the relevant information to the cells as you lay it out in your question.
0
FlysterCommented:
See if this is what you're looking for. In columns AH - AJ I have formulas that select the matching data. The macro copies that data to columns Q-S, eliminating the blank rows.

Flyster
Mastersdata-Sample.xls
0
Saqib Husain, SyedEngineerCommented:
Here is one method which uses a helper column and uses match, index and offset functions. I have used random numbers and you can press F9 to see it change.
Extrposneg.xlsx
0
FlysterCommented:
To ssaqibh,

That's sweet!

Paul
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
libertyforall2Author Commented:
Great
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.