VBA: Index match multiple criteria & autofill until last row with Column as ref

Hello Excel Masters,

Firstly I apologize if I am asking too many requirements in this thread for a single operation.

Please find attached document having the requirements indicated below:
1) There are two spreadsheets, Sheet1 having inputs while Sheet2 having outputs.
2) Sheet2 has Index match multiple criteria with excel formula until the last row of the reference column.
3) Spreadsheet name & columns are dynamic in nature.

The number of rows are exceeding more than 20,000+, hence it is becoming cumbersome to use excel formula leading to high file size and as well as Excel document is hanging by consuming too much of time.

Henceforth it's my request to help me in performing this operation via VBA. I hope it is feasible.

Thanks for your time & support in advance.

- Billy

Quote from Carim

Hello,

Once you have tested out the macro ... feel free to share your comments ...

Hi Carim,

Many thanks for the solution. Sorry I was able to have a look now only.

I will try the code & provide the feedback to you shortly.

- Billy

Hi Carim,

I tried to map the code to my actual requirements but the code is not working to my bad luck.

I am still a rookie to VBA & thus I failed miserably.

I have attached updated file with actual requirements (Columns have been moved in both Sheet1 & Sheet2 as per my requirements). I once again kindly request you to provide the solution.

Further in Sheet2 in Column A the operation in identical but limited to Column A only along with above part.

Thanks for your kind support.

- Billy

Quote from Carim

Hi,

First question would be about your Test file ...

Is the macro producing the expected results ... or not ... ???

Quite important ... since you are dealing with a number of rows in excess of 20'000 ... !!!!!!

Display More

Yes Carim, the test file is meeting all the requirements defined & indeed is delivering desired results.

But I failed it mapping to actual requirements as defined above.

Its my sincere request for you to help me.

Again, thanks for your response & support in advance.

- Billy

Quote from Carim

Thanks for the info ...

Attached is your last Version 2 ... amended with your ' specific structure '

Hope this will help

Hi Carim,

I saw the magic, its working amazingly great.

Thanks for your generosity!!!!!!!!!!!!

- Billy

Quote from Carim

Glad to hear the macro does what you were expecting :wink:

Thanks for .. both your Thanks ..AND for the Like :smile:

Hi Carim,

I am sorry to bug you over & over.

I have one last query, what will be the change in the code if I am changing the spreadsheets names i.e. Sheet1 as Actual, Sheet2 as PT, Sheet3 as Planned.

As the VBA is not functioning if I am changing the spreadsheets names.

Thanks for your kind support again.

- Billy

Quote from Carim

Hello,

Your macro is using Sheets Code Names ... so you do have to worry about changing the Sheets Tab Names ... :wink:

To keep things simple ... there is one exception within the Evaluate function ... see attached Version 3

Hope this will help

Display More

Thank you, thanks a lot for your support.

Its embarrassing for me to ask you so many queries. Since I am a noob in Macro, I am not able to crack the code efficiently.

The last code going by:

col = 1
Set rng = Sheet2.Range(Cells(2, 1), Cells(last2, 1))
For Each c In rng
i = c.Row
c = Evaluate("=INDEX(Actual!" & Col_Letter(col + & "1:" & Col_Letter(col + & last1 & ",MATCH(J" & i & "&K" & i & ",Actual!B1:B" & last1 & "&Actual!C1:C" & last1 & ",0))")
c = IIf(IsError(c), "", c)
Next c

If I need to change the column number then I tried col = 2 but I am not able to fetch the results in Column 2 instead the results are appearing in Column 1 only. I am really sorry to pester you, this shall be the last query as all of my requirements has been cracked by you.

Once again I thank you very much from bottom of my heart for helping me out.

- Billy

A

Quote from Carim

Hello,

Your macro is using Sheets Code Names ... so you do have to worry about changing the Sheets Tab Names ... :wink:

To keep things simple ... there is one exception within the Evaluate function ... see attached Version 3

Hope this will help

Display More

And the code works absolutely!!!!!

Quote from Billy_Kisuke

Thank you, thanks a lot for your support.

Its embarrassing for me to ask you so many queries. Since I am a noob in Macro, I am not able to crack the code efficiently.

The last code going by:

col = 1
Set rng = Sheet2.Range(Cells(2, 1), Cells(last2, 1))
For Each c In rng
i = c.Row
c = Evaluate("=INDEX(Actual!" & Col_Letter(col + & "1:" & Col_Letter(col + & last1 & ",MATCH(J" & i & "&K" & i & ",Actual!B1:B" & last1 & "&Actual!C1:C" & last1 & ",0))")
c = IIf(IsError(c), "", c)
Next c

If I need to change the column number then I tried col = 2 but I am not able to fetch the results in Column 2 instead the results are appearing in Column 1 only. I am really sorry to pester you, this shall be the last query as all of my requirements has been cracked by you.

Once again I thank you very much from bottom of my heart for helping me out.

- Billy

Display More

Hi Carim,

I cracked the code to modify as per my requirements.

Thank you again.

- Billy

Quote from Carim

Hello,

Following your message ... attached is Version 4 ... if you need to move Output 6 from Column A to Column B ...

Hope this solves your problem

Thanks Carim, it was of a great help.

- Billy