Hi, I'm new to the board; thanks for any help anyone can give me.
I have a list with around 6,000 rows and 20 columns. Of these columns, 3 are pertinent. Column 1 has strings, and columns 2 and 3 have numbers, which are sometimes negative and sometimes positive. The strings are non-unique; although the list has around 6,000 rows, there are only around 500 unique strings. What I need to do is, if column 1 matches for any given set of rows, match up offsetting positive and negative values in column 2, and return the sum of the corresponding values in column 3. That's not a very clear example, so let me try to show what I'm talking about. Say this below is a segment of my list:
Column 1 Column 2 Column 3
Apples 4 20
Pears 6 88
Apples -2 -8
Apples -2 -5
What I want is something (a new column, a pivot table, something) that will return this for that segment:
Apples 7
The two -2 values for Apples in column 2, rows 3 and 4 offset the +4 value for apples in column 2, row 1, so the result is the sum of the corresponding values in column 3: 20+(-8)+(-5)=7.
But, I also want whatever it is to recognize when there are offsets that (1) don't get to 0, or (2) go past zero. For case 1, take the following example:
Column 1 Column 2 Column 3
Apples 4 20
Pears 6 88
Apples -2 -8
Here, I would want the output to be:
Apples 2.
Because half of the Apples in row 1 is offset by row 3, I would want to return the sum of column 3, row 3 and half of column 3, row 1. Case 2 is the opposite:
Column 1 Column 2 Column 3
Apples 4 20
Pears 6 88
Apples -2 -8
Apples -3 -6
Here, the output should be:
Apples 8.
The sum of column 2, rows 3 and 4 more than completely offsets the positive value in column 2, row 1, so I want to add the column 3, row 1 value to only the portion of the sum of column 3, row 3 and column 3, row 4 that's necessary to bring the sum of the column 2 values to 0 (in this case, row 3 and 2/3 of row 4).
I've tried specifying a pivot table to do this, and implementing a counter-type system in regular Excel, but haven't been able to get anything to work on all possible cases. The consensus among people I've talked to is that I'll need VBA, which I don't know. Does anyone have any ideas?
Thanks,
Sean
I have a list with around 6,000 rows and 20 columns. Of these columns, 3 are pertinent. Column 1 has strings, and columns 2 and 3 have numbers, which are sometimes negative and sometimes positive. The strings are non-unique; although the list has around 6,000 rows, there are only around 500 unique strings. What I need to do is, if column 1 matches for any given set of rows, match up offsetting positive and negative values in column 2, and return the sum of the corresponding values in column 3. That's not a very clear example, so let me try to show what I'm talking about. Say this below is a segment of my list:
Column 1 Column 2 Column 3
Apples 4 20
Pears 6 88
Apples -2 -8
Apples -2 -5
What I want is something (a new column, a pivot table, something) that will return this for that segment:
Apples 7
The two -2 values for Apples in column 2, rows 3 and 4 offset the +4 value for apples in column 2, row 1, so the result is the sum of the corresponding values in column 3: 20+(-8)+(-5)=7.
But, I also want whatever it is to recognize when there are offsets that (1) don't get to 0, or (2) go past zero. For case 1, take the following example:
Column 1 Column 2 Column 3
Apples 4 20
Pears 6 88
Apples -2 -8
Here, I would want the output to be:
Apples 2.
Because half of the Apples in row 1 is offset by row 3, I would want to return the sum of column 3, row 3 and half of column 3, row 1. Case 2 is the opposite:
Column 1 Column 2 Column 3
Apples 4 20
Pears 6 88
Apples -2 -8
Apples -3 -6
Here, the output should be:
Apples 8.
The sum of column 2, rows 3 and 4 more than completely offsets the positive value in column 2, row 1, so I want to add the column 3, row 1 value to only the portion of the sum of column 3, row 3 and column 3, row 4 that's necessary to bring the sum of the column 2 values to 0 (in this case, row 3 and 2/3 of row 4).
I've tried specifying a pivot table to do this, and implementing a counter-type system in regular Excel, but haven't been able to get anything to work on all possible cases. The consensus among people I've talked to is that I'll need VBA, which I don't know. Does anyone have any ideas?
Thanks,
Sean
