Posts by Nathan_E

    Ah, so the offset portion of the formula was referencing cells outside the specified range? I will insert the blank columns as recommended.


    I do have a follow up question, that might be too complex to implement.


    For item A, the output is 3. I was hoping the formula would count that as 1 since the 4 cells that combine for over 200% are all in conjoined columns. I understand that it is counting the other possible combinations but are using the same stringed together cells (AF2,AG2,AH2). Is there a way to count that as 1 occurrence and not stack by reusing cells?

    Yes, I had changed to % and relevant column range. Its interesting that I am receiving a count of 2 higher than the output from the SUM--(IF formula I was using. Its consistent for lines where I had an output of 3 with the suggested formula I get 5. (or 2 where I had zero). Any theories?

    Thanks for the quick reply. Not sure if it is functioning correctly or taking negative values into account. The below row of data returned a value of 2 with your formula (modified for relevant range). In this example there should not be occurrences of 4 consecutive columns with percentage totals >200%. That I'm seeing anyway..
    [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 2405"]

    [tr]


    [td]

    -28.9%

    [/td]


    [td]

    -49.2%

    [/td]


    [td]

    -89.8%

    [/td]


    [td]

    1.5%

    [/td]


    [td]

    -32.8%

    [/td]


    [td]

    -15.0%

    [/td]


    [td]

    -65.0%

    [/td]


    [td]

    -15.0%

    [/td]


    [td]

    -68.4%

    [/td]


    [td]

    -61.7%

    [/td]


    [td]

    -46.3%

    [/td]


    [td]

    107.0%

    [/td]


    [td]

    84.0%

    [/td]


    [td]

    -50.9%

    [/td]


    [td]

    40.4%

    [/td]


    [td]

    33.3%

    [/td]


    [td]

    -8.8%

    [/td]


    [td]

    130.0%

    [/td]


    [td]

    -11.5%

    [/td]


    [td]

    -46.9%

    [/td]


    [td]

    94.6%

    [/td]


    [td]

    82.8%

    [/td]


    [td]

    -25.5%

    [/td]


    [td]

    15.1%

    [/td]


    [td]

    -59.4%

    [/td]


    [td]

    -93.2%

    [/td]


    [td]

    -52.6%

    [/td]


    [td]

    -52.6%

    [/td]


    [td]

    -59.4%

    [/td]


    [td]

    -59.4%

    [/td]


    [td]

    27.8%

    [/td]


    [td]

    8.7%

    [/td]


    [td]

    -66.5%

    [/td]


    [td]

    -16.4%

    [/td]


    [td]

    -39.0%

    [/td]


    [td]

    120.5%

    [/td]


    [td]

    -16.0%

    [/td]


    [td]

    15.5%

    [/td]


    [td]

    47.0%

    [/td]


    [td]

    21.0%

    [/td]


    [td]

    76.0%

    [/td]


    [/tr]


    [/TABLE]

    In the original request we were looking for specific values within the columns. In this scenario I am looing for the sum total within consecutive columns and to count the number of occurences.


    Ex. -60%, 80%, 100%, 70%, -30%, 10%. There are no instances of 200% or greater values but those 4 consecutive cells combine for 220%.


    I can achieve this with a long SUM(--if(sum(4 cell range)>200%,1,0),if(sum(next 4 cell range)>200%,1,0)). Wondering if there's a simpler method using the frequency formula.

    Hello again,


    Is there a way to alter the suggested formula to output the number of times consecutive columns exceed X? Meaning, regardless of the specific cell values when added together and they are strings (side by side).


    Ex. If the cell values exceeds 200% in exactly 4 consecutive columns than count that as 1. Would like the formula to output the number of times this occurs within a dynamic column range.

    Re: Count number of consecutive values based on criteria


    Thanks for the reply. I am getting a #VALUE! error.


    My data is all in percentages. Not sure if this affecting the result. I swapped out the 50 to 50%, but it still returned the #VALUE!.


    EDIT:


    I just noticed your comment regarding the confirmation on array formulas. I think this solved it. Appreciate the help!

    Hello,


    I am looking for a solution that will count the number of hits greater than a value in consecutive columns. Also, looking for the formula to be interchangeable based on the desired number of consecutive occurrences. For example, count the number of times 3 (or more, adjustable) that back to back columns had a value greater than 50%.


    Data sample:


    Each number value is on the same row and specific to each column.


    20% 50% 60% 30% 70% 100% 50% 10%


    If I was targeting 3 consecutive columns greater than 50% I would like the formula to output 1 (based on a count of meeting this criteria). If the target was 2 consecutive months with 50% than the output would be 2.


    Please let me know if this is possible without the use of VBA.

    Re: Merge duplicate rows and sum value in specific column


    Quote from PCI;643403

    Another way to do, perhaps


    PCI,


    The macro you sent over worked perfectly, thank you. Also, the reason I had an empty column was due to the way the report is generated from SAP. I chose to export via HTML, which makes it easier to apply to excel. Could you modify the attached workbook to include the macro titled "TESTPCI" so it is compatible with the minor layout change. I just shifted over to elimante the blank column, thanks again!

    Re: Merge duplicate rows and sum value in specific column


    Please see attached. I just copied your coding in this workbook and that clears out the line items. But when I run from your attachment it merges and the quantities do not sum.


    The objective would be to just have the macro uploaded into a template and a user would copy and paste the data then run.

    Files

    Re: Merge duplicate rows and sum value in specific column


    Jindon,


    When I used the workbook that you uploaded it consolidated the duplicate line items but didnt sum the quantity. The AIO Cleaner should have equated to 24 but remained at 12:


    [TABLE="width: 431"]

    [tr]


    [td]

    Delivery

    [/td]


    [td]

    Material

    [/td]


    [td]

    Description

    [/td]


    [td]

    SU

    [/td]


    [td]

    Delivery quantity

    [/td]


    [td]

    VUn

    [/td]


    [/tr]


    [tr]


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

    [td]

    12345C

    [/td]


    [td]

    1234 Formula Forty

    [/td]


    [td]

    DRCS

    [/td]


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

    [td][/td]


    [/tr]


    [tr]


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

    [td]

    222222C

    [/td]


    [td]

    2222 AIO Cleaner

    [/td]


    [td]

    CS

    [/td]


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

    [td][/td]


    [/tr]


    [/TABLE]

    Re: Merge duplicate rows and sum value in specific column





    Yes I have ran Macro's before. When I paste the coding in and run all line items clear out with the exception of the column headers. Below is the result I get after running:


    [TABLE="width: 495"]

    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    Delivery

    [/td]


    [td]

    Material

    [/td]


    [td]

    Description

    [/td]


    [td]

    SU

    [/td]


    [td]

    Delivery quantity

    [/td]


    [td]

    VUn

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]

    Re: Merge duplicate rows and sum value in specific column


    Hello,


    Not too sure if I am doing something incorrect but I could not get it to work. I tried to copy and paste the coding in VB and it cleared out all data except the column headers. With your attachment I used the "Click" button and it reopened your attachment with the same screen. It was like a loop. I am somewhat of a rookie with the program, so it is probable there is something I do not understand. Please advise and thanks for your time.

    Re: Merge duplicate rows and sum value in specific column


    Yes basically, although if I could choose I would prefer to check the material number for relation. Attached is a sample of the workbook (Only three lines in the example but practicle version will have several). I would like to merge into one line and sum the qauntity. Please let me know and thanks for your assistance.

    Files

    • STO Sample.xlsx

      (9.25 kB, downloaded 129 times, last: )

    Hello,


    I understand there have been several posts related to the merging of rows but none specifically meet my needs. I can include a worksheet if necesarry, here is the data complex.


    The objective is to save a macro which will allow users to import the same file format (Report layout identical just new information) into the workbook and run/print.


    The rows will have identical information for all columns except B and F. Column F (QTY) is what I would like to sum. Column A will be blank (Can be deleted) and column B (Delivery) will be different but is not relavent. There will be up to column G which is just for reconciliation. Any assistance would be much appreaciated, please let me know of any questions.
    EX: [TABLE="width: 643"]

    [tr]


    [td][/td]


    [td]

    Delivery

    [/td]


    [td]

    Material

    [/td]


    [td]

    Description

    [/td]


    [td]

    SU

    [/td]


    [td]

    QTY

    [/td]


    [td]

    +/-

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


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

    [td]

    XXXXXX

    [/td]


    [td]

    DRUM RED 210L

    [/td]


    [td]

    DR

    [/td]


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

    [td][/td]


    [/tr]


    [tr]


    [td][/td]


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

    [td]

    XXXXXX

    [/td]


    [td]

    DRUM RED 210L

    [/td]


    [td]

    DR

    [/td]


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

    [td][/td]


    [/tr]


    [/TABLE]



    [TABLE="width: 579"]

    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


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

    [td][/td]


    [td][/td]


    [td][/td]


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

    [td][/td]


    [/tr]


    [tr]


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

    [td][/td]


    [td][/td]


    [td][/td]


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

    [td][/td]


    [/tr]


    [/TABLE]