How to make an array that only copies data if there are headers with the same name in both sheets and if column 3 = 0

[Bassie]

Hey,

I basically have 2 sheets lets call them Data sheet and Destination sheet.
Data sheet has around 30 columns and Destination sheet has around 15 columns.
I want to populate the 15 columns if they match any of the headers from the data sheet file and if column 3 in the datasheet file is 0

I can do this using a loop but its a file with around 1 million cells so I think using an array would be way better but I am really new to writing Array so would love some help on this.

The outcome would look something like this: (with more columns and on another sheet but I hope you get the idea). As you can see only the rows are copied if column 3 = 0 and if the headers match

Day	Sales Person	Quantity	Sales			Sales	Quantity	Sales person
7/1/2022	Jill	0	$3,848			$ 3,848	0	Jill
7/1/2022	Jack	2	$ 850			$ 252	0	Jack
7/1/2022	John	2	$ 982			$ 246	0	Jack
7/2/2022	Jack	0	$ 252			$ 1,344	0	John
7/2/2022	Jill	5	$2,315
7/2/2022	Jack	0	$ 246
7/2/2022	John	3	$ 996
7/3/2022	John	0	$1,344
7/3/2022	Jack	3	$ 702
7/4/2022	Jack	7	$3,269
7/4/2022	Mary	4	$1,232
7/5/2022	Jill	9	$2,925
7/4/2022	Mary	4	$1,232
7/5/2022	Jill	9	$2,925

Regards,
Bassie

 

[Joe4]

Why not just use the new FILTER function?
See: FILTER function - Microsoft Support

By the way, I don't think I would try using Excel on anything that has around 1 million rows!
This would be really easy to do in Microsoft Access with a simple query.

You could also probably use Power Query in Excel to do it as well.

 

[Bassie]

Why not just use the new FILTER function?
See: FILTER function - Microsoft Support

My bad I should have specified that I am creating a VBA macro

 

[Joe4]

Not sure if you saw, but I had added more comments to my original reply.

Why does it need to be VBA, especially if there is an easy way to do it without VBA?
Note that you can apply the Filter formula in VBA too. If you turn on your Macro Recorder and record yourself entering the formula manually, you will have recorded the VBA code you need to do that.

Whatever you do, I would STRONGLY advise against doing loops in VBA on data that size. Loops are notoriously slow and inefficient, and trying to do them on a range of nearly one million rows is probably not going to perform well.

 

[Bassie]

Not sure if you saw, but I had added more comments to my original reply.

Why does it need to be VBA, especially if there is an easy way to do it without VBA?
Note that you can apply the Filter formula in VBA too. If you turn on your Macro Recorder and record yourself entering the formula manually, you will have recorded the VBA code you need to do that.

Whatever you do, I would STRONGLY advise against doing loops in VBA on data that size. Loops are notoriously slow and inefficient, and trying to do them on a range of nearly one million rows is probably not going to perform well.

Hi!

I want it to be in VBA as its part of a bigger code. I understand that it is not smart to use it on bigger size data but my normal code runes in about 4 mins and has to be ran ones a week so I am fine with it for now.

I would love it to be with an array as I am trying to learn arrays but just cant get this to work.

- Bassie

 

[Joe4]

I want it to be in VBA as its part of a bigger code. I understand that it is not smart to use it on bigger size data but my normal code runes in about 4 mins and has to be ran ones a week so I am fine with it for now.

I would love it to be with an array as I am trying to learn arrays but just cant get this to work.

Sorry, really cannot help you with arrays. Those are not my forte.

I did explain in my reply how you can use VBA though.