Posts by paul789

    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

    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

    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


    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.


    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


    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


    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