Posts by brin1980

    Hi all,


    I've a spreadsheet which has a medianif formula which use to work, and works occasionally but mostly only presents 0 value results and i'm not sure why.


    Basically the formula is meant to report the median price if the number of bedrooms/bathrooms/car spaces match from the table. I've attached the spreadsheet for your reference.


    As mentioned above, it works for some criterias but not all of them.


    thanks,

    Ernest

    Hey mate,


    It worked.... there was special characters which was causing the error. It only took 20 mins too...


    thank you so much for your help.

    I did try code from #7 but it doesn't actually seems like it's does anything.... that said i only waited for a few minutes and then stopped it.


    There is no difference between the actual workbook and the sample i gave you, only the number of rows.

    Hi Jolivanes,


    I will try to explain it as best i can....


    The code needs to go through "column I" and any instances where there is a row in "column I" that <> "" AND the next row does = "", THEN concatenate all rows in column J, starting from the first blank row in column I, down to the last blank row in column I. The concatenated values from column J are then inserted into "column K" on the same row where "Column I" <> "". All the rows where I = "" are then deleted and moved up. The code should then loop through the rest of the worksheet until the end of the data set.


    Again i hope this makes sense.


    Thanks for your help.

    Hi Roy,


    Thank you so much for responding and for your code.


    I've taken it and had to loop it so that it goes through all the instances in the workbook. I'm sure that this is not the best way to do it given your commences on avoiding loops.


    I've attached a file with an example of the data i'm trying to modify to give you a better idea of what i'm trying to achieve. Although your code seems to be quicker, it will still take a number of hours to go through 500K rows.


    any further suggestions you might be happy to help me with?


    again thanks for your help.

    Files

    Hi all,


    i'm very new to VBA and have created a macro that loops through concatenating values in a variable number of rows and then pastes the joined string into a cell and deletes all the rows where the values were concatenated from. I have a spreadsheet that has 500K rows and this macro takes a good 10 hours to process.


    Just wondering how i could change the macro to dramatically reduce the running time.


    Here is the macro: