# 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
###### Who is Participating?
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.

Commented:
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.
Commented:
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
EngineerCommented:
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
Commented:
To ssaqibh,

That's sweet!

Paul

Experts Exchange Solution brought to you by