Report Formatting Issue

[squallgengar]

Hello! This is my first time posting on these forums so I apologize if this should have gone in a more specialized sub-forum. I have an issue where I'm trying to export a report from QuickBooks into Excel and our accounting manager only wants one line per invoice number. However, all the lines are mashed together (like in the example below) so I'm trying to find out if there's a way to automate the spreadsheet to look for duplicate invoice numbers, total the sales for each one, and replace the multiple lines with just one. For example, the segment below would just come out as

INV10784 2,790.32
INV11447 180.11

instead of

INV10784 242.76
INV10784 145.66
INV10784 458.46
INV10784 1,645.27
INV10784 298.17
INV11447 7.71
INV11447 36.03
INV11447 18.02
INV11447 2.71
INV11447 4.33
INV11447 51.88
INV11447 38.86
INV11447 1.67
INV11447 10.21
INV11447 2.88
INV11447 5.81

I'd like it so that the report is automatically updated upon being imported into Excel (or as close to being automatic as possible). Any suggestions on how I'd go about doing that? Help would certainly be appreciated! Thanks in advance!

[BadlySpelledBuoy]

Pivot Table?

I just used your sample data and created one with 9 mouse clicks and a keypress. That automatic enough?

You could record the actions as you perform them with the macro recorder and after a little tinkering with the code you could run it each time.
This doesn't have to use a pivot table approach if you'd rather it didn't.

Perhaps if you could post some sample data so we can see the actual data layout it would help provide you with a tailor made solution.

BSB

[squallgengar]

Unfortunately, every time I try to upload the file, it doesn't appear in the attachment pick list, even after uploading it via Manage Attachments. I've copied and pasted a cross-section of the spreadsheet below so if you copy and paste it into a blank spreadsheet, it should line up correctly. I've heard about pivot tables but I really don't know much about them or how they're utilized. Sorry, I'm such an Excel plebe.

Type Date Num Item Original Amount Paid Amount Balance

Invoice 02/02/2016 INV10784 [Item] [Orig. Amt.] 242.76 242.76
Invoice 02/02/2016 INV10784 [Item] [Orig. Amt.] 145.66 388.42
Invoice 02/02/2016 INV10784 [Item] [Orig. Amt.] 458.46 846.88
Invoice 02/02/2016 INV10784 [Item] [Orig. Amt.] 1,645.27 2,492.15
Invoice 02/02/2016 INV10784 [Item] [Orig. Amt.] 298.17 2,790.32
Invoice 02/05/2016 INV11447 [Item] [Orig. Amt.] 7.71 2,798.03
Invoice 02/05/2016 INV11447 [Item] [Orig. Amt.] 36.03 2,834.06
Invoice 02/05/2016 INV11447 [Item] [Orig. Amt.] 18.02 2,852.08
Invoice 02/05/2016 INV11447 [Item] [Orig. Amt.] 2.71 2,854.79
Invoice 02/05/2016 INV11447 [Item] [Orig. Amt.] 4.33 2,859.12
Invoice 02/05/2016 INV11447 [Item] [Orig. Amt.] 51.88 2,911.00
Invoice 02/05/2016 INV11447 [Item] [Orig. Amt.] 38.86 2,949.86
Invoice 02/05/2016 INV11447 [Item] [Orig. Amt.] 1.67 2,951.53
Invoice 02/05/2016 INV11447 [Item] [Orig. Amt.] 10.21 2,961.74
Invoice 02/05/2016 INV11447 [Item] [Orig. Amt.] 2.88 2,964.62
Invoice 02/05/2016 INV11447 [Item] [Orig. Amt.] 5.81 2,970.43
Invoice 02/05/2016 INV11447 [Item] [Orig. Amt.] -1.67 2,968.76
Invoice 02/05/2016 INV11447 [Item] [Orig. Amt.] -5.81 2,962.95
Invoice 02/05/2016 INV11447 [Item] [Orig. Amt.] -2.88 2,960.07
Invoice 02/05/2016 INV11447 [Item] [Orig. Amt.] -7.71 2,952.36
Invoice 02/05/2016 INV11447 [Item] [Orig. Amt.] -36.03 2,916.33
Invoice 02/05/2016 INV11447 [Item] [Orig. Amt.] -18.02 2,898.31
Invoice 02/05/2016 INV11447 [Item] [Orig. Amt.] -2.71 2,895.60
Invoice 02/05/2016 INV11447 [Item] [Orig. Amt.] -4.33 2,891.27
Invoice 02/05/2016 INV11447 [Item] [Orig. Amt.] -51.88 2,839.39
Invoice 02/05/2016 INV11447 [Item] [Orig. Amt.] -38.86 2,800.53
Invoice 02/05/2016 INV11447 [Item] [Orig. Amt.] -10.21 2,790.32
Invoice 02/08/2016 INV10606 [Item] [Orig. Amt.] 772.20 3,562.52
Invoice 02/08/2016 INV10606 [Item] [Orig. Amt.] 179.28 3,741.80
Invoice 02/08/2016 INV10606 [Item] [Orig. Amt.] 102.96 3,844.76
Invoice 02/08/2016 INV10606 [Item] [Orig. Amt.] 258.45 4,103.21
Invoice 02/08/2016 INV10606 [Item] [Orig. Amt.] 308.88 4,412.09
Invoice 02/08/2016 INV10606 [Item] [Orig. Amt.] 346.80 4,758.89
Invoice 02/08/2016 INV10606 [Item] [Orig. Amt.] 173.40 4,932.29
Invoice 02/08/2016 INV10606 [Item] [Orig. Amt.] 583.20 5,515.49
Invoice 02/08/2016 INV10606 [Item] [Orig. Amt.] 672.36 6,187.85
Invoice 02/11/2016 INV11601 [Item] [Orig. Amt.] 1.06 6,188.91
Invoice 02/11/2016 INV11601 [Item] [Orig. Amt.] 1.08 6,189.99
Invoice 02/11/2016 INV11601 [Item] [Orig. Amt.] 4.42 6,194.41
Invoice 02/11/2016 INV11601 [Item] [Orig. Amt.] 1.01 6,195.42
Invoice 02/11/2016 INV11601 [Item] [Orig. Amt.] 1.03 6,196.45
Invoice 02/11/2016 INV11601 [Item] [Orig. Amt.] 4.22 6,200.67
Invoice 02/11/2016 INV11601 [Item] [Orig. Amt.] 1.01 6,201.68
Invoice 02/11/2016 INV11601 [Item] [Orig. Amt.] 1.03 6,202.71
Invoice 02/11/2016 INV11601 [Item] [Orig. Amt.] 4.20 6,206.91
Invoice 02/11/2016 INV11601 [Item] [Orig. Amt.] 0.85 6,207.76
Invoice 02/11/2016 INV11601 [Item] [Orig. Amt.] 0.87 6,208.63
Invoice 02/11/2016 INV11601 [Item] [Orig. Amt.] 3.54 6,212.17
Invoice 02/11/2016 INV11601 [Item] [Orig. Amt.] 12.85 6,225.02
Invoice 02/11/2016 INV11601 [Item] [Orig. Amt.] 53.53 6,278.55
Invoice 02/11/2016 INV11601 [Item] [Orig. Amt.] 13.12 6,291.67
Invoice 02/11/2016 INV11601 [Item] [Orig. Amt.] -1.03 6,290.64
Invoice 02/11/2016 INV11601 [Item] [Orig. Amt.] -1.03 6,289.61
Invoice 02/11/2016 INV11601 [Item] [Orig. Amt.] -1.08 6,288.53
Invoice 02/11/2016 INV11601 [Item] [Orig. Amt.] -13.12 6,275.41
Invoice 02/11/2016 INV11601 [Item] [Orig. Amt.] -0.87 6,274.54
Invoice 02/11/2016 INV11601 [Item] [Orig. Amt.] -1.06 6,273.48
Invoice 02/11/2016 INV11601 [Item] [Orig. Amt.] -1.01 6,272.47
Invoice 02/11/2016 INV11601 [Item] [Orig. Amt.] -1.01 6,271.46
Invoice 02/11/2016 INV11601 [Item] [Orig. Amt.] -0.85 6,270.61
Invoice 02/11/2016 INV11601 [Item] [Orig. Amt.] -12.85 6,257.76
Invoice 02/11/2016 INV11601 [Item] [Orig. Amt.] -53.53 6,204.23
Invoice 02/11/2016 INV11601 [Item] [Orig. Amt.] -4.42 6,199.81
Invoice 02/11/2016 INV11601 [Item] [Orig. Amt.] -4.22 6,195.59
Invoice 02/11/2016 INV11601 [Item] [Orig. Amt.] -4.20 6,191.39
Invoice 02/11/2016 INV11601 [Item] [Orig. Amt.] -3.54 6,187.85

[squallgengar]

Oh. Well, that didn't keep the formatting after it posted.

[BadlySpelledBuoy]

Pivot table in the attached, along with notes on how to produce it.

Hope that helps.

BSB

[squallgengar]

Thank you very much for the step by step! I was able to recreate the pivot table but it seems to have issues when adding the paid amounts in cases where there are negative line items included in the invoice number (discounts, for example). Here's what I got when I changed Count of Paid Amount to Sum of Paid Amount:

Row Labels Sum of Paid Amount
INV10606 3397.53
INV10784 2790.32
INV11447 2.84217E-14
INV11601 -4.44089E-15
(blank)
Grand Total 6187.85

Is there a setting that needs changed in order to avoid this?

[candybg]

I'm guessing that your totals for INV11447 and INV11601 are the ones you are having issues with? If you format that column with the COMMA, you should see ZERO in those two spots.

The funny looking number indicates a very, very tiny fraction and is something that Excel has an issue with, not you.

Hope this helps!

[squallgengar]

That did help! Thank you both so much!