Data summary - transfer certain columns of rows 2 criteria (VBA or Formula)

Aberdham

Board Regular
Joined
Mar 8, 2018
Messages
140
Hi there,

Recently I have received a payment workbook containing 2 Sheets (USD & EUR)
and I would like to Transfer to a new workbook named purchase summary:

Here is a selection of USD payment:
Course typeMonthDescriptionInvoice No.LecturerBookDescriptionGross amountTAXTAX
amount
Net AmountInvoice
Date
Due DateexchangeNotedRecordedreview dateacceptedClassificationType
Mechanic 43191,00Purchase 89854782,00ARDSC-0122NIO2541 $ 17.400,00 0%$ - $ 17.400,00 1-4-20181-5-2018 $ 1,23 ü11-4-20181
Mechanic 43191,00Purchase 5454125,00AACC-0122NIO2542 $ 500,00 0%$ - $ 500,00 1-4-20181-5-2018 $ 1,23 ü11-4-20182
450,0043221,00Purchase 5465213,00ARDSC-0251NIO2543 $ 85.000,00 0%0,00 $ 85.000,00 5-3-20185-4-2018 $ 1,23 ü8-3-20181
900002,0043221,00Purchase 4412154,00AARC-0251NIO2544 $ 542.563,00 0%$ - $542.563,00 27-3-201827-3-2018 $ 1,24 ü28-3-20182

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="6"><col><col></colgroup>

Here is a selection of the EURO payment:

Course typeMonthDescriptionInvoice No.LecturerDescriptiongross amountTAXTAX
amount
net amountInvoice DateDue DateexchangeNotedRecordedreview dateacceptedreferenceClassification
Mechanic43221,00Purchase585433,00DNJKoffice supplies261,1523%48,83€ 212,3230-4-201814-5-20181,0011-4-20181
Mechanic43221,00Purchase58952,00JHNservice charge on site1660,5023%310,50€ 1.350,0027-4-201827-5-20181,0012-4-20182
695,0043221,00Purchase451259,00KIJconsultancy service 317,3423%59,34€ 258,0030-4-201814-5-20181,0013-4-20183

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="6"><col><col></colgroup>

End result:
so basically, I would like to Transfer match the invoice type and the respective period of the invoice date. (e.g. all the mechanic rows and the respective invoice Dates period that falls within April 2018) would Transfer to the summary sheet.
However, by only transfering specific columns which is align with the summary sheet. (USD sheet: A - summary A; USD sheet D - summary B; USD E - summary C; USD G - summary D; USD L - summary E; USD N - summary F; USD K - summary G; USD S - summary J;EUR A - summary A; EUR D - summary B; EUR E - summary C; EUR F - summary D; EUR K - summary E; EUR M - summary F; EUR J - summary H; EUR S - summary J)

Not quite sure if it is possible to use formulas if Aggregate small columns combined and just drag it down or VBA would do the tricks.

Please lend me a helping Hand :) would be very much appreciated!

Thank you in advance.

Invoice typeInvoice no.Supplier (Debtor)DescriptionInvoice DateExchange rateUSD EUR AmountChange in InventoryClassificationReference
Mechanic 89854782ARDSNIO25411-4-2018 $ 1,2321 $ 17.400,00 1
Mechanic 5454125AACNIO25421-4-2018 $ 1,2321 $ 500,00 2
4505465213ARDSNIO25435-3-2018 $ 1,2307 $ 85.000,00 1
9000024412154AARNIO254427-3-2018 $ 1,2376 $542.563,00 2
Mechanic585433DNJKoffice supplies30-4-20181,00 - € 212,32 €3
Mechanic58952JHNservice charge on site27-4-20181,00 - € 1.350,00 €1
695451259KIJconsultancy service 30-4-20181,00 - € 258,00 €

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup>

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="6"><col><col></colgroup>
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Watch MrExcel Video

Forum statistics

Threads
1,114,661
Messages
5,549,284
Members
410,908
Latest member
Allen P
Top