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

Aberdham

Board Regular
Joined
Mar 8, 2018
Messages
163
Office Version
  1. 365
Platform
  1. Windows
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>
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top