Counting days of absence for each section

[Mark123321]

Hello,
Can I ask for Your help ?


I have 2 worksheets. One with person ID number and dates of absence for each ID. In second worksheet i have person ID number and section ID number, with dates for which specific person was attached. Now i have to find number of days for each person and each section. The problem is that druring absence person was moved to other section. The section ID dates are changed but the dates of absence arent split. So if i use coinfifs i will just get the sum of absence for each person, not for each section.

for e.g
Absence between 01.01.2022 – 31.01.2022. During that time for. e.g. from 25.01.2022 to 31.03.2022 he is moved from financial section to HR. I need to find days of absence for financial section which is in this case 25 and for HR – 6 days.

I tried to do this but seems not to work.
Sample data in attachment

Will be really grateful for any tips/help

Edit:

[Richard Buttrey]

Sorry but I'm struggling to understand what results you expect to see.

In particular how you calculate them clearly setting out exactly which cells/ranges are involved.

Remember that when you ask a question in a forum those of us who might be able to help know absolutely nothing about your system and work process or the terminology you use. Unless you have uploaded a workbook we know nothing about how it is laid out and little about what you want done with it or how the results should be presented.

Whilst these things are second nature to you since you live with the workbook all the working day we start with zero knowledge so you need to take us by the hand and explain in quite precise detail your aims and goals as well as stuff like how your data might look different in other situations.
Don't assume we are able to "figure it out". We're usually quite good but not psychic.

Put yourself in our position and think about whether what you've posted makes sense and is unambiguous to someone who's never seen it before.
You are asking us for help so help us to be able to help you by providing all the information we need, even if that information seems obvious or intuitive to you.

[XLent]

I am with Richard, however

Please Login or Register  to view this content.

would return - 177, 706 and 15 days for each of the three sections (i.e. the splits of the 898 days by section)

if this is what you're trying to get to, the other calcs (G:J) aren't required.

[Mark123321]

Xlent, thanks a lot.
However,
I tried to implement Your formula, but it seems to work only if there is one ID number. Can You please help me once again ?

[Mark123321]

please find sample data in attachment

edit:
tried to figure out basing on Your formula. Can You please check this out and share Your opinion ?

Edit2:
Is it possible to count absence days for each person->section but in every single year and
for specific range of dates e.g 01.01.2021-31.01.2021 ?

[kvsrinivasamurthy]

It is not clear what you want. On guess I have given formulas in I and J columns. Pl see file.
In I2

Please Login or Register  to view this content.

In J2

Please Login or Register  to view this content.

[y.yusuke]

I2

Please Login or Register  to view this content.

[XLent]

edit:
tried to figure out basing on Your formula. Can You please check this out and share Your opinion ?

Edit2:
Is it possible to count absence days for each person->section but in every single year and
for specific range of dates e.g 01.01.2021-31.01.2021 ?

please refer to the attached

note following:

1. data in Columns G:I is just a "proof" of the results in Cols R:V etc and can be removed -- it is there just to highlight some errors in your section dates - where same ID has 1+ active segment in same period leading to double counting
2. you should keep the IF within the Array as this improves efficiency such that you only calculate the remainder of the formula where needed (i.e. where ID matches)
3. column V just highlights where, of the total days for a given ID, some fall outside of the year windows (e.g. 2018)

[Mark123321]

kvsrinivasamurthy
y.yusuke,
thanks for help, i really appreciate it.
XLent i think that's it ! Thanks a lot.
Last thing. If i want to count absent days for specific dates e.g. 01.01.2021-30.06.2021, will it work if i change "section from" and "section to" dates ? What do You think ?

Best regards,
Mark

[XLent]

Mark, I would be inclined to store that separately, e.g.

Please Login or Register  to view this content.

however, if you want to apply this filter to the year/matrix then it gets a little more complicated.

[Mark123321]

Thanks XLent !
You taught me a lot.

May i ask one more question ?
I've got around 100k rows with data, when filled down the formula, my pc was about to explode. But after couple of min it worked.
What im worried about is the "manual criteria".
Is there any way to overpass the mass calculation ? I've found macro that free memory for a moment. However it's only allowing to filling down the formula quickly but without calculating, so after the macro is done, excel starts to calculate and freezes everything.
What do You think about it ?

[XLent]

hi, no need to reply with quote, just use Reply -- else the Mods get irritated...

So, yes, Arrays are not efficient and so if you use with large precedent datasets and/or in large volume you will have a big performance hit.

Using a sub routine, to apply / calculate / store as value etc, won't make much difference aside the fact that, if you change a cell in the precedent ranges, it won't trigger a recalculation -- which can help, of course; the calcs themselves will still be inefficient as and when they do run.

Generally speaking... if you're working with massive datasets and you want to use formulae, you should look to sort your "source data" by the search criteria

Sorting data offers massive performance gains with big datasets, done properly, as you limit the scope of your calcs and leverage "binary search" type approaches - which only look at a tiny fraction of the data.

for ex... using an admittedly odd setup, to illustrate:

- assume you have the digits 0-9 repeated randomly, but even in frequency, across 100k rows (so each digit appears 10k times, but in non-contiguous ranges)
- and, let's say you needed to find the MAX of every value in Column C where A = 2 and B = "apple"

well, (putting aside more recent functions for sake of illustration), we would have to do something like:

=MAX(IF($A$1:$A$100000=G$1,IF($B$1:$B$100000=$F2,$C$1:$C$100000)))
confirmed with CTRL + SHIFT + ENTER
(where G$1 holds digit, and $F2 B-criteria)

and that's quite expensive in terms of iteration, even with the IF structure, as each calc is still iterating 10s of 1000s of lines

whilst this might perform ok as a stand alone calc if, hypothetically, we had 20 combinations of digit & "B" to cater for, so 200 calcs in total, this changes

on my machine, running the calc, 200 times, would take around 5 secs... not great!

however, if I were to sort that same source range, by first A (digit), then B, then C I can do the same calculation but much quicker...

=LOOKUP(2,1/(INDEX($B:$B,MATCH(G$1,$A:$A,0)):INDEX($B:$B,MATCH(G$1,$A:$A))=$F2),INDEX($C:$C,MATCH(G$1,$A:$A,0)):INDEX($C:$C,MATCH(G$1,$A:$A)))

so, whilst the above looks ungainly (compared to the MAX Array) it will be much more efficient, around ~0.3s on my machine -- that's a big gain % wise

and, of course, this is not truly optimised as we're still looking at more data than we need to... this is where light-weight "helpers" can be useful, e.g.:

D1: =A1&"^"&B1
copied down to D100000

with this helper we can streamline / simplify those 200 calcs yet further, e.g.

=LOOKUP(G$1&"^"&$F2,$D$1:$D$100000,$C$1:$C$100000)

and this, 200 times, will calculate in 0.007 seconds on my machine... so that's a performance gain of ~99% on the unsorted data

so, in short, sorting and, where necessary, light-weight helpers are the way forward when it comes to optimising calculation times.
it's not always about the shortness of the formula, or the quantity thereof, that matters -- it's how much data is being processed with each calculation that really matters.

I was trying to post a working example of the above but, for whatever reason, I am unable to upload -- if I resolve the issue I will attach here as an edit.

Hopefully that helps - of course, you might not be able to sort your data, if that's the case then you may need to get a little more creative...

[Mark123321]

thanks once again for this lecture. I will note that and translate with details, because i dont quite understand everything You wrote. But as far as i understood, the main purpose is to limit number of arrays by combining data ?
Will be rally grateful if You could share with me some example.
One thing i dont get, what does this part "#;\0;0". I see that without that the formula would add everything thats within the array, which would be incorrect.

Thanks once again for everything.
Mark

[XLent]

One thing i don't get, what does this part "#;\0;0"

this is, effectively, a custom number format, like you would use if formatting a cell; the (traditional) format construct for which is: positive ; negative ; zero ; text

so, the format applied here states: for +ve number return the number but for -ve results always return 0 {i.e. the \ treats the following value as a literal string}, and for 0 just return the number

the above means that we're essentially conducting a MAX(0,result) but without using MAX (which would not work here), or another IF construct {IF result < 0 then 0 else result } as this would be expensive (double evaluation)

of course, by applying this format via the TEXT function we're converting the numerics to strings -- so, once we've applied the number format via TEXT we then convert the resulting strings back to number, using +0

TEXT( < values >, < format >)+0

edit:

Will be rally grateful if You could share with me some example.

I was able to modify the sample, and zip it, so that it would upload -- the first tab shows the approach with "unsorted" data, the 2nd tab shows two approaches - both use sorted data (A:C), the 2nd set uses the "helper" in Col D -- both are exponentially quicker than unsorted method.

note to others: this file is solely to illustrate the value-add of sorting data, particularly w/ pre-O365 versions, and when using formulae (rather than VBA) -- nothing more...

[AliGW]

hi, no need to reply with quote, just use Reply -- else the Mods get irritated...

We don't get irritated, but unnecessary quoting takes up a lot of screen real estate and makes a thread hard to follow. The rationale is perfectly reasonable.

[FDibbins]

Xlent, I agree totally with Ali.

Irritated (whether we are or not - we arent) has nothing to do with commenting that unnecessary quoting whole posts just take up space.

[Mark123321]

XLent, thank you very much !!
Im trying to understand how Your formula works, sheet "sorted" the formula for "without helper" :
lookup(2;...) why 2 ? after dividing the result by 1 the array looks {1,1,1,1,1,1,1,1,1} . I see that ts working but why ? ;p
Anyways , do You think i can use this method in my case ?
it seems to me that this could help me to find the proper date range in an instance, instead of looking in over 100k rows ?

Thank You for everything

[XLent]

first, thanks for not quoting -- hopefully no more superfluous posts from the mods...

so, first, note:

=LOOKUP(10,{1,9,3,6,"banana"})

will return 6 because

- LOOKUP assumes all values in the lookup_vector to be in ascending order (irrespective of reality)
- LOOKUP ignores values in the lookup_vector that are not of the same data type as the criteria (including errors)
- LOOKUP retrieves the last value <= criteria from lookup_vector or, if result_vector specified, the associated value from the result vector

the final point means that by adding the result vector to the earlier example, instead of 6, we get the associated value from the result vector

=LOOKUP(10,{1,9,3,6,"banana"},{"a","b","c","d","banana"})

so, "d" as this it the 4th item in the result vector -- as 6 is 4th item in lookup_vector

so, bearing these concepts in mind, the same principle can be adapted for the construct you saw

=LOOKUP(2,1/(boolean),results)

in this instance the lookup vector will store either 1 [1/TRUE] or #DIV/0! [1/FALSE]

with criteria set to 2 we know that LOOKUP will find the last 1 in the lookup_vector, and return the associated value from the result vector.

above said, I want to be very clear, this approach isn't that efficient -- what makes the approach in the file quicker is the fact that by sorting the data we limit the size of the lookup_vector using INDEX w/MATCH so, instead of looking at 100k rows, you're looking at 10k.

i.e. applying this LOOKUP(2,1/...) approach, 200 times, to a 100k unsorted dataset will not perform very well.

[Mark123321]

I dont know what to say, for all that help... Thank you Xlent
PS. Do You think it's worth trying to do it through vba ?

[XLent]

I would say, if you can sort your data then you should not need VBA as the formula based approach should prove almost instantaneous to calculate. If you cannot sort your data then, yes, a Sub Routine (as opposed to an in-cell UDF call) may be worth considering as you can iterate quickly using array storage -- of course, the downside to a dataset being returned by VBA is that it's something of a black box (i.e. you see the result but no idea how it's calculated!). If a VBA sub routine is something it might be worth starting a new thread if you do go down that route (in the VBA forum). You might also consider using Power Query.

[Mark123321]

Trying to make vba code but i made so many if statements that i get lost in it.
Formula would be the best in my case


Sorted:
- dates of absence
- ID numbers descending,
- 2nd part which cornerns section data,

but I don't see any difference,

I dont want to abuse Your kindness, but
is there anything i can do ?
or there is to much data ? (over 100k rows with absences and 4k rows concerning section dates )

[XLent]

Hi, so this is hard to explain, succinctly (as I have proven) so, I have attached an extended version of your sample setup such that the source data is 20k rows, with 2k sections

per subsequent illustrations, there are 2 tabs, 1 where source data is unsorted and 1 where sorted

the array used on both tabs (Col O) is no different, logically speaking, aside how the source "ranges" are identified

unsorted: the 2000 calcs will take around 1 minute to calculate
sorted: the 2000 calcs will take around 0.1 seconds to calculate

the performance gain is solely down to amount of data the respective arrays are looking at
for unsorted each array is having to iterate the entire dataset (20k rows) whereas with sorted you're able to pre-emptively limit the iteration to only those rows where ID matches (i.e. 10s of rows)

you could make this faster still by sorting your sections by ID and date, and then calculating the MATCHes once per ID -- but given it's 0.1 seconds that's not really nec.

[Mark123321]

Xlent, if i can ever help your with anything then please feel free to text me.
Once again thanks