Automated worksheet for accounting filter problem

Hi all;
I am automating a workbook for our night audit team to use for Accounts Payable. My problem is that at the site level we track AP by accounting code, whereas Head Office oonly wants the company & invoice number listed. Our AP is split between 2 categories; General (housing) & Food (Food Services)
I have got the system mostly set but need a hand with the final step. For head office they only want to see 1 line per invooice with company, invoice #, date, & $ total. I have all the listings set but do not know how to make the information compile onto a single line on the page.
I have somewhat denatured the attached workbook but you will see my problem in the "Header General" & "Header Foods" tabs. These are the ones going to H/O.
The PAP tabs can be disregarded as there is never a split invoice on those tabs.

Any ideas would be greatly appreciated by the few remaining strands of hair that I have not yet pulled out.

Hi,

I'm assuming that for instance on the Header General tab you only want each unique Supplier/Invoice number. e.g for Acme, just one row for invoice 12035.

On the Track General tab, add a helper column F and in F9 enter

Please Login or Register  to view this content.

and copy this down.

Now use standard data filter advanced to extract a unique list of the names in column F of the Track General tab to a helper column F on the Header General tab.

Then use the formulae I've shown in B11:E11 on the Header General tab.

Use this same approach for the Food tabs.

HTH

Try autofilter
Select the Supplier Name(B10)
Goto Data>filter then with the dropdown arrow in B10 select what to filter for.
Check out the subtotal function to sum Filtered data

Richard thank you. It worked like a charm.

EDIT: After testing it seems not to be quite right yet. There still seem to be a lot of steps needed after entering the data. Not all our staff are fully excel literate.
I will continue to try automating the output.

Hi,

In which case you will need to develop a macro to automatically create the list of unique helper names at the click of a button, and then either keep the formulae I gave you down all the relevant rows or have the macro copy the formula down the rows.

As a starter try recording the macro first, then examine it and change the code where necessary so that it will always run against the original data.

Regards