Non-Consecutive Count

[yukibare]

Hello all, I have a bit of a difficult one here, help will be appreciated. Essentially:



I want to use a formula to count strings on a specific value as 1 entry till either supply comes or another interruption takes place. (Strings can be EF, OC, SG, SS, IMB, NS, BF, RF etc)

EG: The example below if done right with the formula would count (LS as 2); (NS as 2); (EF as 2) and SG as 1.

LS
LS
LS
2.4
2.5
NS
NS
EF
NS
3
EF
EF
LS
SG


Also note if the last hour of the previous day of a certain feeder is the same as the first hour on the present day, present day does not get counted.


EG

Previous day grid model Current day grid model
21:00 ef ns
22:00 ns imb
23:00 ns ns
24:00 ns oc


So here on the current day, you would see a count for (NS as 1) (IMB as 1) (OC as 1)

To further emphasize, I have attached 2 snips of each sheet, the conditionally formatted document is the source and (interruption) numbers. The values on interruption are all correct. Sars rd, adp, market, igwu, aka, ibb, indon and udo udoma all ended in ss on the previous day hence the missing value count for each of these.

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

[yukibare]

Okay thanks for clarifying. The sample workbook is now attached.

[AliGW]

Sorry - I have no clue what you are trying to do or where - can you please tell us where we will find the results that you have mocked up manually to show what you want?

[yukibare]

Okay,

So the sample workbook contains 3 sheets, 30th, 1st and Interruption. I am trying to use the 1st of December to populate the interruption sheet. The 30th is included to show continuity between two days.

For example cell A29 on the 30th continues on to A6 of the 1st hence, the cells (A6: A9) with SS values will not be counted on the interruption sheet.

The formula when created will count the following for row 6 (Sars rd) of the interruption sheet

EF BF RF DC IMB OC EMG LS SG LSG NS SS

2 1 1 ( For LS, NS, SS respectively)

Rows 3, 4 and 5 on the interruption workbook can be ignored


The point is to count any continuous string of consecutive values as 1 entry. Take a look at column f on the workbook (pipeline)

PIPE LINE
2.0
LS
LS
LS 1 count of LS
2.4
2.6
2.5
EF
EF
EF 1 count of EF
NS
NS
NS
NS
NS 1 count of NS
NS
NS
NS
SS 1 count of SS
3.3
3.2
3.0
2.8
2.7

[davsth]

Maybe others can do better but I am not sure what you are asking. what value wants to appear in what cell on what sheet and the reason/logic for this?


Are you wanting to count the number of runs of each of the numbers listed? Or just that they had appears at all

=COUNTIFS(J5:J28,"<>LS",J6:J29,"LS") will count 3 as there are 3 runs of LS

but your interruption sheet is transposed which makes the formulas far more painful, especially as you have not called things by the same name to match. Sars rd and sars road for example. do the sheets have to be transposed?

[yukibare]

Hello Davsth,

Thanks for responding. Firstly the heading names are irrelevant, they do not need transposing; what we are trying to do is count the number of interruptions to power supply for various types of fault occurences.

The LS count you're referring to, if its for the pipeline example, has to be counted as 1 (for the 3 runs as you put it) as it is 1 interruption to the power supplied.

So if ns (no supply) appears for 3 consecutive hours, that amounts to 1 interruption for ns.

I hope this has answered your question?

[yukibare]

more information:

LS - load shedding
NS - No supply
BF - Breaker fault
EF - Earth fault
OC - Over current
SS - Station under service


This is a sample load consumption grid model for a distribution company in western africa

[Glenn Kennedy]

I think I understand.

Three Qs.

1. Do you REALLY need the raw data on two sheets? It would be much easier if they were on one sheet, with an additional column for the date.

2. Where are these on your raw data?
ALUU
CHOBA
RUMUEKINI


3. WHY have you broken the logical sequence of your data with a total column in the raw data sheets, column K and in the results sheet, row 16? These interuptions are an Excel nightmare and should be avoided. Logical layout is KING.

[Glenn Kennedy]

Another Q. It there is an LS as the last entry for the day in one place, and an LS as the first entry for the NEXT day in the SAME place, is that 1 interruption, or two?

[Glenn Kennedy]

Yukibare, you have logged off. So... I combined the days into one sheet, ignored the 3 missing locations, and removed the columns/rows interrupting the logical data layout. I also assumed that last on day 1 / first on day 2 combination counts as 1 interruption. So:

=SUMPRODUCT((INDEX(Raw!$B$6:$Q$53,,MATCH($A6,Raw!$B$5:$Q$5,0))=E$2)*(INDEX(Raw!$B$7:$Q$54,,MATCH($A6,Raw!$B$5:$Q$5,0))<>E$2))

copied across and down.

[yukibare]

Alright Glen,

1) Feel free to copy/paste on one sheet, I can link them later. This is operating procedure that I can't break haha.

2) These three from the interruption sheet are to be ignored as I added them accidentally to the sample workbook

3) They broke as I had to unlink the original data source, excel nightmare indeed pal; however, for the purposes of this formula they are unnecessary.

4) For the NEXT day, it would NOT count as an interruption as between midnight and 1am, the no power supply stays the same. The interruption would only count if say previous day ends in NS and current day starts in LS. The count is done only on current day using previous as a reference IF there is an interruption on the first data value at any given location.

5) Noticed you on quite a few forums, glad to have a maestro keyed in to my problem.

[Glenn Kennedy]

LoL. So take a look at the file. It should do what you want. IF it does, I need to make some minor changes, to allow the ranges of the formula to adjust automatically when you add more days data. At the moment, that has to be done manually, which is a bit of a pain.

[yukibare]

Right I've had a look at your workbook Glen and its close to perfect.

I can handle changing the formula to reflect a one day count (1st december) however, the interruption shouldn't be counted if it spills over into the next day which it is.

The only reason I've attached 2 days is to reflect a non count on the 1st dec if the 30th nov has the same value at midnight. I tried changing your formula to reflect this and it didn't work out.

[Glenn Kennedy]

I am unclear about what you mean;
the interruption shouldn't be counted if it spills over into the next day

Mon EF
Mon LS
Mon LS
Mon LS
Tue LS
Tue LS
Tue EF

is this ONE interruption or two?

[Glenn Kennedy]

Badly worded Question... Is this one LS interruption, or two?

[yukibare]

Yessir, it does what I want it to do. Automating would be great.

The minor change would be counting from B30 instead of B6 in order to start the count from the 1st dec and the non count of spillage from previous day to next. Thank you so much

[yukibare]

okay so for tuesday there would be no Ls interruption unless one occurs below EF. Interruptions happen when, you know, something interrupts. So essentially from the data you posted just now, the only count would be EF for tuesday

[Glenn Kennedy]

OK. Next area that I don't understand. My sheet calculates a TOTAL number of interruptions, by type, by location. So, 30/11 and 1/12 are combined. Is that NOT what you want. If not, what DO you want?

[yukibare]

I would like for the 30th to be ignored on the count front. The interruptions are counted on a daily basis. Type and location is correct; the 30th Nov data shows which Row 30 values (according to your edited workbook) on the 1st dec should NOT be counted. So essentially the correct answer for sars road on the first of december is (2 1 1) for (LS NS and SS) respectively while yours has 4,4,4 which is the sum of both days.

[Glenn Kennedy]

Can I then add a dropdown on the results sheet to select the day chosen for calculation?

Then I shall have to think about the day end/day beginning problem again...

[yukibare]

Dropdown if absolutely necessary, The manual nature of this sheet is mind boggling so at this point I would be okay with the automation as long as it works just fine

[Glenn Kennedy]

OK. Dates now in a dropdown in cell A2.

My last problem is those day-change events. See cell P3: SS on Sars Road. What is the expected result for 30/11/2020 and 01/12/2020? I still don't quite understand the day-change rule....

[yukibare]

Okay so as cell B29 on the Raw is 'SS' then B30:B33 will NOT be counted as SS due to the fact that its a continuous interruption. Essentially, the expected result for P3 is '1' for the first of december as B30:B33 will not be counted.

[Glenn Kennedy]

OK, sir. Over to you. Try to break it....

[yukibare]

Haha, I've spent a good while on it already mate, if you can't crack it I don't think anyone can. Thanks for the help anyway, I'm one step away from the goal because of you.

[Glenn Kennedy]

Well test it hard. I'm away for the night now. It's beer o'clock in Belfast.

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[yukibare]

Be sure to buy you a pint next time I'm over there Northern Ireland's got great people... done with the reputation but my problem still persists so can't mark it solved just yet.

[Glenn Kennedy]

What remsins to be done?

[yukibare]

Realised you've done it all, just the format that needs work. Thanks again Glen