Posts by Zarfot

    TL;DR: I need a formula to concatenate visible cells, remove duplicates, and add a ", " in between values.



    I have a spreadsheet that I have macros filtering all the time and running data pulls.
    Row 3 holds formulas that pull specific metrics out of my data set in A6:S16627.
    Cell R3 calls a VBA script to run that concatenates visible cells, and returns a value with duplicates removed.
    For example, if the data was 100, 100, 101, 102, 101, it would return "100, 101, 102."


    Is this possible with formulas? I have a script that accomplishes this already, but the script can't update effectively within the sheet when filters change.


    Thanks in advance.

    Re: VBA Script Loop Help


    Hi Smallman,


    Forgive me -- I'm on my mac where Excel is quite buggy compared to my windows work laptop where I use Excel frequently.


    It seems your output in the summary sheet is the exact data i'm looking for. Fantastic work, thank you very much! I am very very grateful.


    I did have one question, and that may be due to my mac being buggy, but I see you used the run button which generated the data. Will I lose the functionality to sort the data through the named ranges to only have certain projects generate the data at once, rather than all be displayed at the same time? I will test in 8 hours when I have my windows computer available. It could just be I lost some functionality on my current computer.


    Thank you again!

    Hello all:


    My VBA script is "almost" completed, but needs help correcting some errors before it works.
    The script is supposed to loop through filters in Regions!B5 (it's the column header) for data in Regions!B6:B553, then after filtering, copy Regions!A3:R3 to Temp!A1:R1 for each unique filtered value. For example, if there are 10 unique IDs in Regions!B, Temp!A1:R1 should have one row for each value, creating a dataset from A1:R1, to A10:R10.
    For whatever reason, the final output generated on RegionsSummary! is not looping through each filter to copy+Paste on the sheet, it is pasting duplicates of the first unique filter value, for as many times as there are unique values in regions!B.
    Can anyone identify the error? It's likely having to do with LR3 on RegionSummary, finding the last row. Nothing (according to the code) exists on RegionSummary, so it only applies the pasted value to row LR3+1 in column A. That doesn't change, so the loop keeps overwriting. If Dim LR3 is put outside of the loop, then ReDim inside of the loop, it might work, but I'm not sure how this could be accomplished. Here is the file link: http://s000.tinyupload.com/dow…2773662777787382231444371 I appreciate any help!