Mimic SQL "group by"

[consultant_of_swing]

I have a worksheet that contains invoice information at the detail
level.

VEND_CODE | INV_NUMBER | INV_DATE | LINE_AMOUNT
123 123-A 3/5/08 100.00
123 123-A 3/5/08 200.00
456 456-B 3/31/08 500.00
456 456-B 3/31/08 800.00
456 456-C 4/2/08 700.00


I need to build a second worksheet that summarizes the amount by all
the other columns

VEND_CODE | INV_NUMBER | INV_DATE | INV_TOTAL
123 123-A 3/5/08 300.00
456 456-B 3/31/08 1300.00
456 456-C 4/2/08 700.00


This would be a simple select query with a group by clause in SQL, but
I'm at a loss as to how to accomplish it in Excel. The invoice detail
worksheet comes from an imported text file and can contain >10K rows.

Any thoughts?

 

[Bob Phillips]

If they were separate workbooks, you could run MSQuery against the saved
version of the former.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Earl Kiosterud]

The group/summarize tools in Excel are the pivot table and Data - Subtotals. Excel's
equivalent to the database query is the Autofilter or Advanced Filter. I don't think either
can do grouping. At least through Excel 2003.

 

[Tushar Mehta (Microsoft MVP Excel 2000-2008)]

Treat the table as the source of a SQL query and have it do the
totaling. Excel includes a product, MS Query, that can help with a
GUI. For an intro see
Building and using a relational database in Excel (with a little help
from MS Query)
http://www.tushar-mehta.com/excel/newsgroups/rdbms_in_excel/index.html

And, yes, you can use the same workbook as the source of the SQL query
once you save the file.

 

[Harlan Grove]

I have a worksheet that contains invoice information at the detail
level.

VEND_CODE INV_NUMBER INV_DATE LINE_AMOUNT
123 123-A 03/05/2008 100
123 123-A 03/05/2008 200
456 456-B 03/31/2008 500
456 456-B 03/31/2008 800
456 456-C 04/02/2008 700

I'll refer to this range as Tbl. You could define the name Tbl (or
something else) referring to this range. I'll also assume this table
is in ascending order on the INV_NUMBER field.

I need to build a second worksheet that summarizes the amount by all
the other columns

VEND_CODE INV_NUMBER INV_DATE LINE_AMOUNT
123 123-A 03/05/2008 300
456 456-B 03/31/2008 1300
456 456-C 04/02/2008 700

....

I'll assume this would begin in cell A1 in the 2nd worksheet. I'll
also assume the column headers are already in A11, but I'll assume
you need to pull in all the fields in A24.

Here's a formula-only approach.

B2:
=INDEX(Tbl,MATCH(B1,INDEX(Tbl,0,2),0)+COUNTIF(INDEX(Tbl,0,2),B1),2)

A2:
=INDEX(Tbl,MATCH(B2,INDEX(Tbl,0,2),0),1)

C2:
=INDEX(Tbl,MATCH(B2,INDEX(Tbl,0,2),0),3)

D2:
=SUMIF(INDEX(Tbl,0,2),B2,INDEX(Tbl,0,4))

Select A22 and fill down as far as needed.