Posts by paul789

  • Find all strings of 10 sample numbers in a range of 500k+ USD $40

    USD $4 Paid to OzGrid:


    Excel attached


    Data Sheet - Column d is the data that I input and column E and F is the same data with a tolerance of plus or minus one.


    In this instance there is 10 lots of data in col E/F which I want to find all occurrences of this data in this same order in column A


    As you can see on row 28 to 37 there is a perfect match which I have deliberately inserted so that it's easier to understand.


    Once this and any further matches are found I would like them to be put on the results page on the second sheet


    I have filled out the first example of how I would like it but I am open to other suggestions if there is an easier way.


    Columns B to K are the data from rows 28 to 37 and columns I to P are the next five rows after this data which is important.


    Column A on data sheet will have up to 800k entries . I am not bothered if the macro is slow. I have powerful computer anyway.


    As I am unsure if there will be enough sample results for what I need I would like to be able to change the data that I input in column d and restrict it if necessary to five six seven eight or nine number samples so that I can get enough result samples so would be good if I could change Cell (I2) to e.g. 7 to get 7 matches if i need only 7


    Hope this makes sense if not please ask.


    Best regards Paul

  • Vba code to automate finding highest figures USD $35.00

    Re: Vba code to automate finding highest figures USD $35.00


    Hi thanks


    as long as i get the same end result you can do it however you feel best


    i assume it will be vba code that i will need to insert myself so would appreciate instructions


    also just a reminder that it would be safer to have 10 seconds in between each cycle as i explained the data on sheet 1 is massive with loads of formula so even on my powerfull pc it still takes 3 seconds to update after 1 and 1.5 etc are put in the required cells


    hope that makes sense


    Thanks again Paul

  • Vba code to automate finding highest figures USD $35.00

    Hi all


    paid 3.50 usd to ozgrid

    I have used the pay for hire many times and you are fantastic

    I don’t know how easy or complicated this is so if 35 usd is not enough accept my apologies and if no one takes it up I will increase it.

    I have a task that I wondered if you could do with a vba code

    On attached spreadsheet there is 1.5 and 1.9 in the top left hand corner, A macro would need to constantly change these.


    Firstly to 1 and 1.5 (this changes over 300k sets of data on previous sheet) so this takes about 5 seconds to update all the cells in the top grid with the new figures.

    All The figures from the top grid need to be placed into the lower grid and the relevant to and from amount in the adjacent cell as shown in my example.

    Then the macro needs to place 1.1 and 1.6 in the cells and when data appears if ANY amounts are higher than the amounts in the corresponding lower boxes then they need individually changing with the new relevant to and from amount in the adjacent cell.

    The idea is that when the macro has finished after a few hours it will show me the highest outcomes of every individual square and the relevant to and from amounts adjacent

    To start the difference will be 0.5 per time then 1 then 1.5 then keep adding 0.5 till we get to 10

    Example

    1.0 and 1.5 then 1.1 and 1.6, 1.2 and 1.7, 1.3 and 1.8 right up to 10 and 10.5

    then we increase by 1 every time so

    1.0 and 2.0 then 1.1 and 2.1 then 1.2 and 2.2 etc up to 10 and 11

    then we increase by 1.5 every time so

    1.0 and 2.5 then 1.1 and 2.6 then 1.2 and 2.7 etc up to 10 and 11.5

    then we increase by 2.0 every time

    etc etc

    hope that makes sense

    i cannot attach the original spreadsheet and also its over 400mb so huge.

    I am not great with vba so would need a explanation when its done.

    Many thanks in advance


    ps if there is a better way that gets the same end result im happy to go with that.


    Paul

  • Sumifs one columb based on different criteria in two other colums

    Hi All


    Please can someone correct this formula as i am struggling to get it correct


    i am trying to sum the numbers in column BB when both corresponding column K is 6 and corresponding column AL is 0 (zero)


    i think i am close but i am missing something


    =SUMIFS('Sheet 1'!BB:BB, 'Sheet 1'!K:K, ="6", 'Sheet 1'!AL:AL, ="0")


    all the columns above contain numbers and there is about 50000 rows in total


    many thanks again


    Paul

  • Highlight largest value cell in range based on other columns

    Re: Highlight largest value cell in range based on other columns


    its a mystery


    according to my account


    i have microsoft office proffesional plus 2016


    should the file you uploaded just work or do i have to enable anything.


    i see the formula on your cf is =C1=_xlfn.MAXIFS(C:C,A:A,A1,B:B,B1)


    which is different from what you said in first post =C1=MAXIFS(C:C,A:A,A1,B:B,B1)


    but i cant get either to work


    very strange


    Paul

  • Highlight largest value cell in range based on other columns

    Re: Highlight largest value cell in range based on other columns


    thank you


    your downloaded file does not work on my excel 2016


    i have tried running it as xls and also changing it to xlxs and neither seem to work


    i have never used conditional formatting so i may be doing it wrong


    i will have a read up on it tonight and try and get it working using your formula


    many thanks Aligw


    Paul