Billing Statement Help
 

Hi, I am hoping that someone with a little more excel experience can help me.
I compile a dispersable billing statement for our company execs for our
corporate card use.

I am able to download the report in excel format. I am losing a lot of time
filtering card users, running a sum for transactions, & copying & pasting to
a new sheet.

For example:
CARDHOLDER DEPARTMENT AMOUNT
Joe Brown Logistics $999.99
Joe Brown Logistics $698.23
Joe Brown Logistics ($33.21)
Joe Bruce Marketing $581.98
Joe Bruce Marketing $11.74

This excel spreadsheet contains approx. 300 separate cardholders and approx.
3500 separate transactions. I want to reduce it down to 1 instance of
cardholder's name, department, & transaction total. Any help?

Billing Statement Help
 

Why do that? Try this idea where a2 contains the name of the cardholder

=sumproduct((sourcesheet!a2:a22=a2)*(sourcesheet!b 2:b22="deptname")*sourcesheet!c2:c22)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"jeb" wrote in message
...
Hi, I am hoping that someone with a little more excel experience can help
me.
I compile a dispersable billing statement for our company execs for our
corporate card use.

I am able to download the report in excel format. I am losing a lot of
time
filtering card users, running a sum for transactions, & copying & pasting
to
a new sheet.

For example:
CARDHOLDER DEPARTMENT AMOUNT
Joe Brown Logistics $999.99
Joe Brown Logistics $698.23
Joe Brown Logistics ($33.21)
Joe Bruce Marketing $581.98
Joe Bruce Marketing $11.74

This excel spreadsheet contains approx. 300 separate cardholders and
approx.
3500 separate transactions. I want to reduce it down to 1 instance of
cardholder's name, department, & transaction total. Any help?

Billing Statement Help
 

Lets say the first row with Joe Brown in in A2 and there are 300 entries

Put a list of names in column G, starting in G2
Put a list of Departments in H1 to Z1 (whatever)
In H2 use =SUMPRODUCT(--($A$2:$A$350=$G2), --($B$2:$B$350=H$1), $C$2:$C$350)
Copy across and down as needed
I have used 350 to allow for extras.
Do NOT use full column reference (A:A) unless you have Excel 2007

For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html

Another (sometimes better) way is with a Pivot Table: see one or more of
these
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm
http://www.techonthenet.com/excel/pivottbls/index.htm
http://www.dicks-blog.com/archives/2...le-parameters/

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"jeb" wrote in message
...
Hi, I am hoping that someone with a little more excel experience can help
me.
I compile a dispersable billing statement for our company execs for our
corporate card use.

I am able to download the report in excel format. I am losing a lot of
time
filtering card users, running a sum for transactions, & copying & pasting
to
a new sheet.

For example:
CARDHOLDER DEPARTMENT AMOUNT
Joe Brown Logistics $999.99
Joe Brown Logistics $698.23
Joe Brown Logistics ($33.21)
Joe Bruce Marketing $581.98
Joe Bruce Marketing $11.74

This excel spreadsheet contains approx. 300 separate cardholders and
approx.
3500 separate transactions. I want to reduce it down to 1 instance of
cardholder's name, department, & transaction total. Any help?

Billing Statement Help
 

Hi

Use a Pivot Table.
Place your cursor within your source tableDataPivot Table Finish
On the new sheet where the Pivot Table skeleton appears
Drag Cardholder to Row area
Drag Department to Row area
Drag Amount to Data Area

--
Regards
Roger Govier

"jeb" wrote in message
...
Hi, I am hoping that someone with a little more excel experience can help
me.
I compile a dispersable billing statement for our company execs for our
corporate card use.

I am able to download the report in excel format. I am losing a lot of
time
filtering card users, running a sum for transactions, & copying & pasting
to
a new sheet.

For example:
CARDHOLDER DEPARTMENT AMOUNT
Joe Brown Logistics $999.99
Joe Brown Logistics $698.23
Joe Brown Logistics ($33.21)
Joe Bruce Marketing $581.98
Joe Bruce Marketing $11.74

This excel spreadsheet contains approx. 300 separate cardholders and
approx.
3500 separate transactions. I want to reduce it down to 1 instance of
cardholder's name, department, & transaction total. Any help?

Billing Statement Help
 

I've never used a pivot chart for Excel. I have used them in Access though.
The only hang-up with this pivot chart is that it gives me the total NUMBER
of transactions, not the TOTAL SPEND AMOUNT (JOE BROWN LOGISTICS
$12,000<total of 8 thransactions). Any further advice?

"Roger Govier" wrote:

Hi

Use a Pivot Table.
Place your cursor within your source tableDataPivot Table Finish
On the new sheet where the Pivot Table skeleton appears
Drag Cardholder to Row area
Drag Department to Row area
Drag Amount to Data Area

--
Regards
Roger Govier

"jeb" wrote in message
...
Hi, I am hoping that someone with a little more excel experience can help
me.
I compile a dispersable billing statement for our company execs for our
corporate card use.

I am able to download the report in excel format. I am losing a lot of
time
filtering card users, running a sum for transactions, & copying & pasting
to
a new sheet.

For example:
CARDHOLDER DEPARTMENT AMOUNT
Joe Brown Logistics $999.99
Joe Brown Logistics $698.23
Joe Brown Logistics ($33.21)
Joe Bruce Marketing $581.98
Joe Bruce Marketing $11.74

This excel spreadsheet contains approx. 300 separate cardholders and
approx.
3500 separate transactions. I want to reduce it down to 1 instance of
cardholder's name, department, & transaction total. Any help?

Billing Statement Help
 

You can change this to give Count, Sum, Average, etc
See one of the links in my first message
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"jeb" wrote in message
...
I've never used a pivot chart for Excel. I have used them in Access
though.
The only hang-up with this pivot chart is that it gives me the total
NUMBER
of transactions, not the TOTAL SPEND AMOUNT (JOE BROWN LOGISTICS
$12,000<total of 8 thransactions). Any further advice?

"Roger Govier" wrote:

Hi

Use a Pivot Table.
Place your cursor within your source tableDataPivot Table Finish
On the new sheet where the Pivot Table skeleton appears
Drag Cardholder to Row area
Drag Department to Row area
Drag Amount to Data Area

--
Regards
Roger Govier

"jeb" wrote in message
...
Hi, I am hoping that someone with a little more excel experience can
help
me.
I compile a dispersable billing statement for our company execs for our
corporate card use.

I am able to download the report in excel format. I am losing a lot of
time
filtering card users, running a sum for transactions, & copying &
pasting
to
a new sheet.

For example:
CARDHOLDER DEPARTMENT AMOUNT
Joe Brown Logistics $999.99
Joe Brown Logistics $698.23
Joe Brown Logistics ($33.21)
Joe Bruce Marketing $581.98
Joe Bruce Marketing $11.74

This excel spreadsheet contains approx. 300 separate cardholders and
approx.
3500 separate transactions. I want to reduce it down to 1 instance of
cardholder's name, department, & transaction total. Any help?

Billing Statement Help
 

Hi

Double click on Count of Amount and change the function from Count to Sum.
There must be some blank values in your range - Amount.
If all values are numeric, Excel will use Sum, but if any are Text or Blank,
then it will default to Count.

--
Regards
Roger Govier

"jeb" wrote in message
...
I've never used a pivot chart for Excel. I have used them in Access
though.
The only hang-up with this pivot chart is that it gives me the total
NUMBER
of transactions, not the TOTAL SPEND AMOUNT (JOE BROWN LOGISTICS
$12,000<total of 8 thransactions). Any further advice?

"Roger Govier" wrote:

Hi

Use a Pivot Table.
Place your cursor within your source tableDataPivot Table Finish
On the new sheet where the Pivot Table skeleton appears
Drag Cardholder to Row area
Drag Department to Row area
Drag Amount to Data Area

--
Regards
Roger Govier

"jeb" wrote in message
...
Hi, I am hoping that someone with a little more excel experience can
help
me.
I compile a dispersable billing statement for our company execs for our
corporate card use.

I am able to download the report in excel format. I am losing a lot of
time
filtering card users, running a sum for transactions, & copying &
pasting
to
a new sheet.

For example:
CARDHOLDER DEPARTMENT AMOUNT
Joe Brown Logistics $999.99
Joe Brown Logistics $698.23
Joe Brown Logistics ($33.21)
Joe Bruce Marketing $581.98
Joe Bruce Marketing $11.74

This excel spreadsheet contains approx. 300 separate cardholders and
approx.
3500 separate transactions. I want to reduce it down to 1 instance of
cardholder's name, department, & transaction total. Any help?

Billing Statement Help
 

That's awesome. Thank you so much!

"Bernard Liengme" wrote:

You can change this to give Count, Sum, Average, etc
See one of the links in my first message
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"jeb" wrote in message
...
I've never used a pivot chart for Excel. I have used them in Access
though.
The only hang-up with this pivot chart is that it gives me the total
NUMBER
of transactions, not the TOTAL SPEND AMOUNT (JOE BROWN LOGISTICS
$12,000<total of 8 thransactions). Any further advice?

"Roger Govier" wrote:

Hi

Use a Pivot Table.
Place your cursor within your source tableDataPivot Table Finish
On the new sheet where the Pivot Table skeleton appears
Drag Cardholder to Row area
Drag Department to Row area
Drag Amount to Data Area

--
Regards
Roger Govier

"jeb" wrote in message
...
Hi, I am hoping that someone with a little more excel experience can
help
me.
I compile a dispersable billing statement for our company execs for our
corporate card use.

I am able to download the report in excel format. I am losing a lot of
time
filtering card users, running a sum for transactions, & copying &
pasting
to
a new sheet.

For example:
CARDHOLDER DEPARTMENT AMOUNT
Joe Brown Logistics $999.99
Joe Brown Logistics $698.23
Joe Brown Logistics ($33.21)
Joe Bruce Marketing $581.98
Joe Bruce Marketing $11.74

This excel spreadsheet contains approx. 300 separate cardholders and
approx.
3500 separate transactions. I want to reduce it down to 1 instance of
cardholder's name, department, & transaction total. Any help?