Hello everyone,
I am stuck with tranferring the spreadsheet i receive to a new workbook by having a better overview of each invoice.
I would like to have the data from table 1 Transfer to table 2 with certain lookup Aggregate formula?
same invoice number, Company, Serial number, tax rate and Performance date may appear multiple times due to the different orders.
in table 2 I would like to have the invoice number, Performance(Company); Description(Serial number), tax rate, currency; Performance date appear only once; but to sum the invoice amount and tax amount.
Results will be like this:
<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col></colgroup>
Table 1
<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col><col></colgroup>
I am stuck with tranferring the spreadsheet i receive to a new workbook by having a better overview of each invoice.
I would like to have the data from table 1 Transfer to table 2 with certain lookup Aggregate formula?
same invoice number, Company, Serial number, tax rate and Performance date may appear multiple times due to the different orders.
in table 2 I would like to have the invoice number, Performance(Company); Description(Serial number), tax rate, currency; Performance date appear only once; but to sum the invoice amount and tax amount.
Results will be like this:
Invoice number | Performer | Descrpition | Tax rate | Invoice amount | Tax Amount | Currency | Performance date |
PIN100722 | Sweptoff | PIN200770 | 0,00 | 18.877,00 | 0,00 | USD | 03-jan-2018 |
PIN100732 | Eastern cleaning | PIN200773 | 23,00 | 24.500,00 | 4.655,00 | USD | 04-jan-2018 |
PIN100737 | DNS | PIN200788 | 0 | 10.700,00 | 0 | USD | 12-jan-2018 |
<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col></colgroup>
Table 1
Invoice number | Company | Serial number | Order number | Tax rate | Invoice amount | Tax Amount | Currency | Performance date |
PIN100722 | Sweptoff | PIN200770 | 5A1433 | 0,00 | 18.877,00 | 0,00 | USD | 03-jan-2018 |
PIN100732 | Eastern cleaning | PIN200773 | 5A1885 | 23,00 | 24.000,00 | 4.560,00 | USD | 03-jan-2018 |
PIN100732 | Eastern cleaning | PIN200773 | 154DN2 | 23,00 | 500,00 | 95,00 | USD | 03-jan-2018 |
PIN100733 | Western cleaning | PIN200779 | 6A8162 | 23,00 | 650.000,00 | 123.500,00 | USD | 03-jan-2018 |
PIN100734 | Southern cleaning | PIN200755 | 2A4157 | 0,00 | 16.950,00 | 0,00 | USD | 08-jan-2018 |
PIN100735 | Northern Cleaning | PIN200784 | 9379M61P03 | 23,00 | 1.292,50 | 245,58 | USD | 08-jan-2018 |
PIN100736 | Oriental | PIN200785 | 340-051-901-0 | 0,00 | 4.740,00 | 0,00 | USD | 12-jan-2018 |
PIN100737 | DNS | PIN200788 | 1347M32G08 | 0,00 | 2.850,00 | 0,00 | USD | 12-jan-2018 |
PIN100737 | DNS | PIN200788 | 3A2704 | 0,00 | 5.000,00 | 0,00 | USD | 12-jan-2018 |
PIN100737 | DNS | PIN200788 | 6A7906 | 0,00 | 2.850,00 | 0,00 | USD | 12-jan-2018 |
PIN100738 | KEA | PIN200789 | 340-085-120-0 | 0,00 | 21.000,00 | 0,00 | USD | 12-jan-2018 |
PIN100739 | KLC | PIN200787 | 1523M71G07 | 0,00 | 2.850,00 | 0,00 | USD | 12-jan-2018 |
PIN100739 | KLC | PIN200787 | 1851M59P01 | 0,00 | 2.850,00 | 0,00 | USD | 12-jan-2018 |
PIN100739 | KLC | PIN200787 | 1864M97P01 | 0,00 | 2.850,00 | 0,00 | USD | 12-jan-2018 |
PIN100740 | 874EAP | PIN200792 | 1971M17G01 | 0,00 | 5.000,00 | 0,00 | USD | 18-jan-2018 |
PIN100741 | DLC | PIN200797 | 340-116-401-0 | 0,00 | 18.000,00 | 0,00 | USD | 19-jan-2018 |
PIN100742 | COPN | PIN200802 | 9511M24P07 | 0,00 | 7.950,00 | 0,00 | USD | 22-jan-2018 |
|
<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col><col></colgroup>