VBA help needed for a beginner

    Hi there,


    I have a formula: =TEXTJOIN(",",TRUE,IF((Q:Q<>"")*(Q:Q<3),B:B,""))


    Where: B:B is a column of people's names and Q:Q is a column for the results for an individual in a test.


    The formula identifies which people have scored less than 3 marks in a section (column Q:Q).


    I would like to be able to enter this formula into VBA (I am a beginner!) and am having some issues with examples I have found online. I would then like the formula to work for other columns (other test sections), if possible - so that individuals who are missing points can be identified.


    Any help would be greatly appreciated! ^^


    D

    Hi and Welcome to the Forum :)


    Attaching a sample file would allow to illustrate your ultimate objective ...


    Do not hesitate to show the exact final result ... ;)

    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 :)

    Hi,


    Sadly you are not indicating ... the most important : your expected result ...!!!


    Is the second sheet Results what you might be looking for ...???


    An Array formula like yours is fine ... up until a certain number of records ... afterwards it slows down the whole workbook ..

    Hi Carim,


    My apologies - the result should be all the individuals with results below the highest score for each section (3 for Q1, 4 for Q2 etc.).


    Normally there is no more than 1000 records per sheet.


    The second sheet is what I'd be looking for! (without duplicates if possible, as sometimes individuals appear twice for not getting full points on a section).


    Is it possible to create a VBA macro that does this?


    Thank you again for all of your help! :)


    Thanks for your all your clarifications ...


    For 1'000 records, the best solution is a UDF ...

    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 :)

    Is it possible to add this formula to VBA with worksheet.function or Range("").FormulaArray?


    Apologies if this is a stupid question!


    DD

    As shown in your test file, a User Defined Function stands on its own ...


    Your Array Formula is fine ... but with 1'000 records for 4 questions ... it will drain on your Excel RAM memory ...

    and the whole workbook will become extremely slow ...


    You can test your Array Formula with your real-life data ... and you will quickly find out ...;)


    Meanwhile ... have you tested your UDF ...???

    Is it producing the expected result ... or not ...???

    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 :)

    Thanks for your Thanks :)


    Do not hesitate to give your feedback tomorrow


    Cheers

    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 :)

Participate now!

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

Register Yourself Login