Group data by values

[okela]

Hi,

I'm having trouble with grouping data by value using a specific format.
In my table, I have weekly hours and I want to group data following this example:

W1	W2	W3	W4
35:00	35:00	35:00	35:00

The final result would be: W1-W4 35:00.

However, if there are different values in the table, the result should be:

W1	W2	W3	W4	W5	W6
35:00	35:00	35:00	35:00	30:00	35:00

Final result: W1-W4 35:00 W5 30:00 W6 35:00

Please find attached a workbook with more explanations.

Could you please help me?

Many thanks!

Okela

[Limor_OP]

Hi,
In your 2nd example: Why should W6 be 35 and not 30?

[okela]

Hi Belinda,

This is because the W6 has 35 hours and not 30 hours.
In my table, weeks can have different hours that is why I gave those examples.
Is this clearer?

Thanks,
Okela

[Limor_OP]

Sorry, I meant why is your final result

W1-W4 35:00 W5 30:00 W6 30:00


when the value in W6 is 35...?

[okela]

Hi Belinda,

Indeed, the final desired result is: W1-W4 35:00 W5 30:00 W6 35:00.
Sorry, I have corrected the workbook.
Please find attached the updated workbook. I have also updated the main post.

Okela

[okela]

Up, could you please help me with this request?

Many thanks!

Okela

[Bo_Ry]

Please try at G12

Please Login or Register  to view this content.

[Limor_OP]

Bo_Ry Amazing!

[okela]

Bo_Ry, this is amazing!
You saved me twice today. Thank you so much.

Okela

[okela]

Hi Bo_Ry,

I tried to add more weeks and to leave blanks cells. It duplicated the hyphens. For example:

W1--W5 35:00--

Could you please help me to fine-tune the function? I have attached the updated workbook with new weeks added.

Many thanks,

Okela

[Bo_Ry]

The hyphens is tricky parts.

How about no hyphens?

=TRIM(TEXTJOIN(" ",,REPT(G$5:O$5,((F6:N6<>G6:O6)+(G6:O6<>H6:P6))*(G6:O6<>"")>0)&REPT(TEXT(G6:O6," [h]:mm "),(G6:O6<>H6:P6)*(G6:O6<>""))))

[okela]

Yes, indeed... Is there any way to handle the hyphens?