Compare, sumif, delete row

Posted by Kevin on May 23, 2001 8:21 PM

I have a 1000+ entry report that's basically structured like:

A B C 1
A E F 3
A B C 5
A E G 2

I'd like to sort the report by the first three columns then compare their values. If they match, I'd like to sum the fourth column and delete the matched row. The final product would look like:

A B C 6
A E F 3
A E G 2

I've got the sorting bit down - and deleting the row doesn't sound too hard, but the selecting the right ranges and then summing the right cells is a little beyond me. Can anyone steer me in the right direction?

Thanks,
Kevin

Posted by Kevin James on May 23, 2001 10:31 PM

Hi Kevin:

Your data structures were too ambiguous to be understood. How about providing a bit more substance.

Kevin (too)

Posted by Eric on May 24, 2001 7:38 AM

Hi,

What you've got here is a great example of pivot table friendly data (especially if you do the concatenation step mentioned below) since it sounds like what you are really after is the subtotals for your different categories. If you want to keep it in the spreadsheet however, see below.

Here's what I thought of, but only worthwhile if your 1000+ entries don't have 1000+ combinations of letters in the first 3 columns!
first concatenate your first 3 columns (=a1&b1&c1)into 1 (D column).
Second, and this is where the solution is no good if you have a LOT of categories,
assuming the actual data is in column (E), type =sumif(D1:D1000+,abc,E1:E1000+) to get the ABC total, etc.

Posted by Kevin on May 24, 2001 8:12 AM

Columns A, B, C are strings, D is an integer I want to add together if the concatenated string A+B+C is an exact match in another row.

E.g.,

ROW 1
A - Philadelphia; B - cream; C - cheese; D - 3

ROW 2
A - Philadelphia; B - cream; C - cheese; D - 5

ROW 3
A - KFC; B - finger; C - lickin good; D - 3

In the case I want the program to return 2 rows, one reading "Philadelphia cream cheese 8" and "KFC finger lickin good 3".

Thanks for your help,

Hungry Kevin

Posted by Barrie Davidson on May 24, 2001 9:43 AM

Hungry Kevin, how about inserting a column that concatenates your three strings and then sub-totalling on that column? Insert a column at column A, put the formula =B1&C1&D1 in cell A1, copy that formula down. Then select the entire data table, select Data|SubTotals from the main menu, and choose "Sum" function for column E for each change in column A.

Does this work for you?
Barrie