# Posts by Nathan_E

• ## Count number of consecutive values based on criteria

Yes it is. That's impressive. Thanks for your help.

• ## Count number of consecutive values based on criteria

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?

• ## Count number of consecutive values based on criteria

Here's part of the data set. When I copied it over the output from your formula was not producing any values so I might have adjusted something incorrectly.

• ## Count number of consecutive values based on criteria

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?

• ## Count number of consecutive values based on criteria

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]

• ## Count number of consecutive values based on criteria

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.

• ## Count number of consecutive values based on criteria

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.

• ## Count number of consecutive values based on criteria

Re: Count number of consecutive values based on criteria

It does, thank you.

• ## Count number of consecutive values based on criteria

Re: Count number of consecutive values based on criteria

Confirming that the solution worked. Would you mind explaining how it works?

• ## Count number of consecutive values based on criteria

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!

• ## Count number of consecutive values based on criteria

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.

• ## Merge duplicate rows and sum value in specific column

Re: Merge duplicate rows and sum value in specific column

That is amazing!

Thank you both for your help on this, definitely beyond my expertise.

• ## Merge duplicate rows and sum value in specific column

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!

• ## Merge duplicate rows and sum value in specific column

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.

• ## Merge duplicate rows and sum value in specific column

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]

• ## Merge duplicate rows and sum value in specific column

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]

• ## Merge duplicate rows and sum value in specific column

Re: Merge duplicate rows and sum value in specific column

Quote from jindon;643109

No idea.

How should I be operating the macro? The click button doesn't change the layout/data and the uploading the code manually just erased all line items.

• ## Merge duplicate rows and sum value in specific column

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.

• ## Merge duplicate rows and sum value in specific column

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

• ## Merge duplicate rows and sum value in specific column

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]