# Complicated sorting and analysis problem--Pivot Table? VBA?

#### SeanGC

##### New Member
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

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Howdy, and welcome to the board.

I don't have time to look at this more closely, and my brain is fried right now. But wanted to extend a welcome, and an encouragement - someone will give you ideas.

Replies
5
Views
371
Replies
0
Views
211
Replies
0
Views
193
Replies
5
Views
458
Replies
1
Views
239

1,203,600
Messages
6,056,204
Members
444,850
Latest member
dancasta7

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back