How to use row data to perform a function against all other rows

**mparietti15** · 10-02-2011, 10:13 AM

This is driving me crazy, hopefully this is possible in excel.

What I have:

Stock market data. Each row has a symbol, a date, and a set of variables. My sheet has around 350 rows.

What I am trying to do:

I am attempting to match the variables from other rows. I have been using filters w/ formulas to do this however it is very time consuming because I have to pick one row at a time to manually match with the rest of the rows using the table filters.

What I want to do:

be able to see the matches all at once. right now I am using and "and" function with variable ranges in order to match, something like this:

=AND($U$1>=G20-(3), $U$1<=G20+(3), $V$1>=H20-(H20*0.05), $V$1<=H20+(H20*0.05))

i have to assign new u1/v1 values whenever i want to test a new data set

can i get around this?

**gjlindn** · 10-02-2011, 11:36 AM

This would be much easier to understand with an example workbook. Could you upload one?

**mparietti15** · 10-02-2011, 11:52 AM

I have a workbook with some further explanation attached

**gjlindn** · 10-02-2011, 12:09 PM

Sorry mparietti15...I'm still not clear on what you're trying to accomplish. But maybe a pivot table would get you what you're looking for? If not, maybe you coud post an example that looks like what you want for the end result.

**mparietti15** · 10-02-2011, 12:18 PM

I am trying to match a set of variables in one row to the variables in the other rows. My solution thus far has been to create a table and filter, and previously I would just filter each variable until I had my final matches - matched on all three points. I graduated to adding a true/false statement that told me true for those rows which matched on all three points. I am just looking for any method that will tell me all at once which rows match which other rows, without having to go through every single set of numbers.

**mparietti15** · 10-03-2011, 01:30 PM

No solution to this?

**gjlindn** · 10-05-2011, 09:40 PM

Sorry...not yet. Through the work week it becomes a challenge for me to spend time in this forum. I still need more understanding on your problem, though. Here's what I know:
A match is considered anything that is +/-20 from each of the values in columns D, E & F.

What I don't understand:

ideally, each row would match to the other rows and spit out the values in coll. G for the matching row

Column G contains something called GAIN. What is the output you're looking for? What would be most helpfull is seeing the final output you want in an example workbook.