September 9, 2020
The question is simple: what would you do if you were in my shoes?
At the moment, I can't think of anything concrete. So, if you have any clues, I'd love to hear them. I start from this situation: I always have a Proprio line. I can have one or more partner lines. For each partner of the Proprio , I have one line per uninterrupted period of the household. What I want to achieve: Have the different couples with the corresponding period. The Proprio will always be present.
I am sorry but my first message was not explicit enough. So, I will clarify some data.
In the Start sheet, we have the starting situation.
- This is the history of the housekeeping of an owner.
- This owner may live alone for a whole year.
- Or, he may live with one or more partners.
- Either way, the owner will always be present for the whole year from January to December.
- As for the partners, they can live at the same address for the whole year or for a few months.
- And the owner can be in a household with one or more partners.
What is the ultimate goal? In the End sheet, I present the desired result.
- I would like to be able to establish the different households from 1 January to 31 December.
- For each household, we will always have the owner either alone or with one or more partners.
I hope that this is much clearer.
January 31, 2022
I played around with your file a bit, but found it difficult to come-up with the format for the end result. But if your only intention is to see the composition of the household during any given month, perhaps the attached file is useful. IT uses PowerQuery to allocate the presence of the partners to the correct months.