VBA Code to Match the Value from Sheet1 Row 6

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    I have two sheets Sheet1 and Sheet2. Where Sheet2 has Data and i wanted to copy the data after matching the "Quarters" from Row 6.


    The row 6 would have below quarters and we will start copy the data from below quarters to end of the year (which is available in shape of like this Y2099.


    Q1 2020

    Q2 2020

    Q3 2020

    Q4 2020


    If code finds that row six has "Q3 2020" this then code will start copy the Gross Wage available under the matching column. If there is one Gross Wage it will copy that one if there are multiples then code will sum and then paste it to Sheet1.

    I have attached 2 sheets where i have pasted a DATA in Sheet1 Col"D" to show an example and code will paste data accordingly.


    Your help will be highly appreciated.

    Hi,


    Take a look at the formula in cell D8 :

    Code
    =INDEX(Sheet2!$7:$7,MATCH("Q"&ROUNDUP(C8/3,0)&" "&B8,Sheet2!$6:$6,0))

    Attached is your test file


    Hope this will help

    :)

    Glad to see you added a Like for the suggested formula ...:)


    Does it mean this solves your problem ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Love 1

    Understand there are two separate issues ...


    1. Cell D8 with the combination of the two rows ...


    Is the following formula producing the correct result :

    Code
    =INDEX(Sheet2!$7:$7,MATCH("Q"&ROUNDUP(C8/3,0)&" "&B8,Sheet2!$6:$6,0))+INDEX(Sheet2!$10:$10,MATCH("Q"&ROUNDUP(C8/3,0)&" "&B8,Sheet2!$6:$6,0))

    2. Sometimes months have to be converted to Quarters for the Current Year .... otherwise they need to be converted to Years ...


    is that right ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Love 1

    Unfortunately ... you did not answer my previous my two questions :


    1. Is the new formula for cell D8 producing the correct result ... or not ...?


    2. Do you have a sheet 2 recap always built with a combination of Quarters and Years ... ?


    As a consequence, the version 2 test file is only my guess ... about your objective ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Love 1

    I apologies,


    1. Is the new formula for cell D8 producing the correct result ... or not ...?

    I was using your formula which is producing accurate result but what i am trying to do is that formula should be dynamic like if there are multiple "Gross Wage" in Sheet 2 Col"C" formula should SUM them all if there is one "Gross Wage" it should give single result Formula would always SUM "Gross Wage" values no other headers available in Col"C". Like this picture


    2. Do you have a sheet 2 recap always built with a combination of Quarters and Years ... ?

    Yes Sheet 2 will have always that values Quarter and year.

    Thanks for your clarification ...:)


    How could you expect me to guess you do need to sum all the rows from Sheet 2 where ' Gross Wage ' appears in Column C ...


    Let me analyze a bit more your question ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Love 1

    The Formula solution will produce a long intricate Array formula ...


    You are right ... VBA is the way to go ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Love 1

    If you need a very quick Formula solution ...


    This will require an intermediate formula in Sheet2 ... say in cell H2


    Code
    =SUMIF($C$7:$C$50,"Gross Wage",H7:H50)

    and cell D8 needs the Formula to be adjusted as follows :

    Code
    =IF(LEFT(INDEX(Sheet2!$6:$6,MATCH("*"&B8,Sheet2!$6:$6,0)),1)="Q",INDEX(Sheet2!$2:$2,MATCH("Q"&ROUNDUP(C8/3,0)&" "&B8,Sheet2!$6:$6,0)),INDEX(Sheet2!$7:$7,MATCH("Y"&B8,Sheet2!$6:$6,0)))


    BUT ... If you cannot insert the Formula in Sheet 2 ... then we will have to go the VBA route ...


    Please let me know ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Love 1
    Quote from Excel&VBA

    I think formula will be very tough, If you can please go with VBA it will be great

    OK ...


    As soon as I have a little moment, I will dive into your code ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Love 1

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login