Dynamic Summary Report

[smaon]

I have a table in a worksheet containing thousands of records. For simplicity sake, let's say the column headings are:

A) Client
B) Account
C) Vendor Number
D) Vendor Name in Japan
E) Vendor Name in English
F) Amount Owed in Japanese Yen
G) Amount Owed in U.S. Dollars

I need a single summary table/report that includes Client, Vendor Number, Vendor Name in English, and total of Amount Owed in Yen and U.S. Dollars per vendor. I need it to be dynamic as they expect to add additional clients and vendors in the next few weeks.

OPTION 1: I can't use Pivot Table because I need to include the three columns: A) Client, C) Vendor Number and E) English Vendor Name. They all need to be on the same line.

OPTION 2: I can copy the unique values in A), C) and E) to a new sheet, and then use SUMIF to calculate total amount owed. However, if in the future we add other vendors to the table, I would need to pull unique values of A), C) and E) again since those columns in my summary table are not dynamic.

How can I create the dynamic summary report that I need using Excel 2010 without using VBA/macros?

Thanks,
Sammi