Posts by dunc1234

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Re: worksheet code to copy the value of a formula result that constantly changes


    hi again
    i couldn't get the match to work but i think i have managed to get nearly a desired result with



    Although it picks up with the change of cell i think i can make it work for me, thanks though for your input, but i'm off to bed and will try and put this into practice tomorrow

    Re: worksheet code to copy the value of a formula result that constantly changes


    Quote from Grimes0332;768874

    That code would not work because a cell changing as a result of a calculation does not raise the Change event. You could try the code in the Calculate Event, scanning the relevant cells for 'Match', or you could use a Circular reference in E4


    =IF(AND(D4="match", E4=0), D4, E4)


    if D4 = "Match" and E4 is 0 (it will be 0 iwhen you first edit/copy the formula) then take the value of D4, otherwise take the value of E4. You will have to Enable Iterative Calculations and set the Max iterations to 1 in the Options/Formulas screen.


    many thanks for that, i'll give it a try but the "Enable Iterative Calculations and set the Max iterations to 1 in the Options/Formulas screen" is beyond my knowledge, but i'm sure searching this forum and google will answer that.
    Thanks again for looking and providing your assistance :)

    Hi gurus


    i'm stumped on this and i'll try to explain what i'm trying to achieve, but have uploaded a simplified version as well if my explanation is poor.


    I capture external data in to Range A1:A50 of numbers , this data changes every minute or so, but sometimes a lot quicker


    In Range B1:B50 is a formula crunching numbers in Range A1:A50.


    In range C1:C50 is a formula to get a match result if reaching a criteria e.g If B6 value is >5 and < 10 "match"


    The problem is as the values in column A6 changes the word "match" comes and goes in column C6


    So in column D6 i want to paste the value "match" if it appears in column B so it stays in place in D6 whether it stays in C6 or not.


    I gather it will be a trigger event in worksheet code rather than a macro, but from here i'm stuck.


    Appreciate any help


    Thanks in advance


    dunc

    Hi thanks to all that help us (not so bright) that struggle with code, thought this would be fairly simple but i'm stuck again :)


    in column A i have numbers that go concurrently up in value for an unspecified amount of cells, then drop to a low number and start again.
    i would like to sum each group in column B.


    For example the numbers in column A are 1,2,3,6,8,12,1,5,6,8,10,15,19,3,5,7,9,1,4,5 etc
    i was hoping to get something along the line of
    Select A2 - start of data
    progress down column until cell value is less the cell above
    select range and sum into column B
    reset and continue down column.


    so in the example data above, in cell B7 = 32 ,a sum of A2:A7 (1,2,3,6,8,12) and cell B14= 64, a sum of A8:A14 (1,5,6,8,10,15,19) etc


    As always many thanks to all those that help

    Re: copy and paste with two criteria


    Quote from shknbk2;765532

    Try this code:




    Gosh! thankyou so much, works a treat and far beyond my capabilities, you are a guru :)

    i think i'll have to admit to being dim as gone round in circles on this (probably simple) problem


    in column A i have data such as 1,3,7,9,12, 5,6,18,23, 4,5,6,11,13,15,18,20 etc. basically numbers going up in value for varying amounts of times and then dropping back down to a low number and starting again to go up in value. (i have left spaces in example to show where the groups start at a low number and increasing in value)


    in column B i have the value X next to the lowest number on some of these groups of numbers.


    When this happens i want to copy the range of numbers from A to column C


    Pseudo code would be;
    Select B2
    scroll down until X is found
    offset 1 cell to left to column A
    select range of cells down until value of cell is smaller
    copy that range to column C


    Return to column B and scroll down to next occurrence of X
    then loop;


    so if for example data starts in A2 and there was an X in B2 it would copy the range A2:A6 (having the values1,3,7,9,12) to Column C
    Then continue down column B until it finds another X.


    I've got in all sorts of muddle with offset and loops and appreciate any help

    Re: find repeating number sequence in a column


    Hi


    I can honestly say that your are a genius, the code works exazctly how i need it first time. i am over the moon and you have made my day.


    Thanks you


    Quote from nilem;760295

    Hi dunc1234,
    try this

    hi guru's


    I have a column of numbers from 1-7 in a random order and am trying to find repeat sequences of three or more numbers.
    For example 1 5 5 4 3 2 3 4 2 1 5 7 6 1 4 2 1 7 7 4 3 1 2 5 5 4 etc etc there are over a 10000 numbers in the column.


    In the above the is the sequence "421" repeated twice and also "521"


    by the side of these "found" sequences i would like the word "match" placed in column B as the number list is in column A


    Although i have used three number sequences in the example, i would also like the ability to match four and five number sequences if possible.


    I have attached an example file as well.


    Thanks in advanced to anyone who can help

    apologies for long post but trying to attach a a spreadsheet failed, i'm trying to mark with an x repeating numbers
    the first batch is a small part of the existing, the second part shows how i wish it to look.


    This is in pseudo code
    from top going downwards





    start at B1
    go down until value of cell is X
    go across to column A
    If cell below is = to active cell, place X in column B
    if next cell below is = to above cell, place x in column B
    if next cell down <> to one above, go to Column B, scroll down to next occurrence of X









    so if the cells in column A have the same number, put an x in column b
    if not, move down to next occurence of x




    [TABLE="width: 128"]

    [tr]


    [TD="width: 64, align: right"]830[/TD]
    [TD="width: 64"]x[/TD]

    [/tr]


    [tr]


    [TD="align: right"]830[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]820[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]805[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]790[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]790[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]790[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]790[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]780[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]780[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]780[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]765[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]765[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]745[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]720[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]830[/TD]

    [td]

    x

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]830[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]830[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]830[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]830[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]830[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]820[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]820[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]820[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]820[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]815[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]805[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]795[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]785[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]785[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]780[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]795[/TD]

    [td]

    x

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]760[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]760[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]760[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]760[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]760[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]760[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]760[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]760[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]760[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]760[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]760[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]760[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]745[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]745[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]745[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]745[/TD]

    [td]


    [/td]


    [/tr]


    [/TABLE]



    how i want it to look


    [TABLE="width: 128"]

    [tr]


    [TD="width: 64"][/TD]
    [TD="width: 64"][/TD]

    [/tr]


    [tr]


    [TD="align: right"]830[/TD]

    [td]

    x

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]830[/TD]

    [td]

    x

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]820[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]805[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]790[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]790[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]790[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]790[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]780[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]780[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]780[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]765[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]765[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]745[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]720[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]830[/TD]

    [td]

    x

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]830[/TD]

    [td]

    x

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]830[/TD]

    [td]

    x

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]830[/TD]

    [td]

    x

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]830[/TD]

    [td]

    x

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]830[/TD]

    [td]

    x

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]820[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]820[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]820[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]820[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]815[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]805[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]795[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]785[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]785[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]780[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]795[/TD]

    [td]

    x

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]760[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]760[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]760[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]760[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]760[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]760[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]760[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]760[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]760[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]760[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]760[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]760[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]745[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]745[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]745[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]745[/TD]

    [td][/td]


    [/tr]


    [/TABLE]

    Hi


    i have a few thousand lines of data of numbers in column A


    in column B at varying intervals is the letter X, the gap may vary between 2 and 50


    eg X at B7, B52, B81, B100 etc, with no data between.


    Starting at the bottom of the data and working upwards i am trying to achieve;


    When X is in column B, go to column A and going upwards find the first occurence of a number under the value of 6.


    When that value is found, copy it to the same row in column C


    Offset left to column B


    Continue upwards (thus ignoring the rest of the numbers) until the next occurence of X in column B and repeat the loop.


    Pseudo code of what i'm trying to do if this explains better


    Go to end of column A
    offset one cell right to column B
    Go upwards until cell data = X
    offset one cell left to column A
    If data is less than 5, copy data to column C
    If above 5 go up one cell
    (continue until first occurence of number under 5)
    (when found, copy to column C)


    Offset to Column B and go upwards to next occurence of X


    repeat above sequence until reaching cell A1


    Probably fairly simple to a guru but i'm really stuck, thanks in advance for any help.

    hi


    i have several 1000 lines of blocks of data containg numerical data separated by a space but each block is headed by the same title for example


    Cell Content
    A1 salesNumbers
    A2 5
    A3 8.5
    A4 2
    A5 9
    A6 Empty Row
    A7 SalesNumbers
    A8 55
    A9 45
    A10 66
    A11 Empty Row
    A12 Salesnumbers


    etc etc so when sorted A2=2 A3=5 A4=8.5 A5=9
    A8 = 45 A9 = 55 A10 = 66


    The title is always the same and not important to keep


    there is data to the sides of column A (in B to G) that i want sorting along with column A


    I want to sort each block of numbers and then move onto the next block (as shown above)


    keeping the empty row is not important.



    pseudo code
    a1 select
    select range to empty row
    sort data
    move down
    select next range to next empty row
    sort data


    stop when more than 3 empty rows detected (2 would be ok but 3 for safety incase extra row sneaked in)


    my pseudo code is a guess on what the coding might be , i've combed the sites and cannot find a solution anywhere and this is doing me in , hopefully simple to a guru


    many thanks in advance