Group By From-To Text without duplication

[Saxon202202]

Hi,

I hava data contain following columns are type, bin, section, bay and level.

Logic1	If bay hasall the level from A to E range according to the Type, Section, Bay and Level then return from-to Bay (Example: Row 34 to 161).
Logic2	If bay doesn't have from A to E range according to the Type, Section, Bay and Level then return from to range bay with level (Example: Row 34 to 161).
Desired Result	Combine logic1 and logic2 into one line like this "A01D-A02D,A03-A05" according to the Type, Section, Bay and Level.
Criteria	Type, Bin, Section, Bay and Level.

Herewith attached the file for your reference.
https://www.dropbox.com/scl/fi/i8qi32erxkw7epg1rmuqn/FTR1.xlsx?rlkey=peyoqisp4t9ku8ut884m1yg5o&st=yb...

[danextian]

Hi @Saxon202202 

Sorry but your logic is unclear.

Given your data and the desired result, it seems that you want to group the bays by Section and then return the min and max bays as xxx-xxx. However, your sample data doesn't have any bays that have levels from A to E. In fact, there's no E at all.  How then rows 34-161 have xxx-xxx results? How are rows 1-33 different from 34-161?

[Anonymous]

Hi @Saxon202202 ,

Sorry, I don't quite understand your logical description.

From your data and what you expect, it seems that you want to group by Section, and then by Bay to see the non-recount of Levels, if it's equal to 1, it's AXXX-AXXX, otherwise it's AXX-AXX.
And also to combine them.

If possible, please provide more details of your problem.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Saxon202202]

@danextian and @Anonymous.

Thanks for your reply and sorry for the late reply. 

Given a dataset with columns such as Type, Bin, Section, Bay, and Level, you need to group the data by Section and Bay and check if all levels from A to E are present within each group. If all levels are present, you should return a single range representing the bay. If not all levels are present, you should return specific ranges for those levels. The final output should combine these results into one string.

Logic:

1. Logic 1 (Full Range of Levels Present):
• If a particular Bay has all levels from A to E (or whatever the full set is), return a range combining all of them, e.g., A03-A05.
2. Logic 2 (Partial Levels Present):
• If a particular Bay does not have all levels from A to E, identify the specific levels present and return the ranges for these levels, e.g., A01D-A02D.

Combined Result:

Combine the results from Logic 1 and Logic 2 into a single output string for each Section. For example, if a section has one complete bay and another with partial levels, the result should look like A01D-A02D, A03-A05.

Criteria:

• Criteria for Full Range: A bay must have all levels from A to E.
• Partial Levels: Identify any missing levels and include only the ranges that are present.

[danextian]

hi @Saxon202202 

You explanation is still unclear. You mentioned that a bay must have all levels. Bay A22 only has levels A, B and D and yet it based on the desired result, logic 2 is applied.