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

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Watch MrExcel Video

Forum statistics

Threads
1,113,835
Messages
5,544,593
Members
410,623
Latest member
RusHartley
Top