Need a little Assistance.

Swizz3y 

Your requirement is to avoid duplicate lengths for the same cable tag in your summary worksheet, so far I understand it. Here is a more detailed step-by-step guide to achieve this in Excel, assuming you are using Excel 365 or Excel 2019.

Step-by-Step Solution

1. Identify Unique Cable Tags:

• In your summary worksheet (e.g., "Summary"), create a column to list unique cable tags.
• Enter the following formula in cell H4 of the "Summary" worksheet to get the unique cable tags:

=UNIQUE(FILTER(CONTROL!$M$4:$M$105, CONTROL!$M$4:$M$105 <> ""))

This formula will list all unique cable tags from the range CONTROL!$M$4:$M$105.

2. Sum Lengths for Unique Tags:

• Next, create another column to sum the lengths for each unique cable tag.
• Enter the following formula in cell I4 of the "Summary" worksheet:

=IF(H4<>"", SUMIF(CONTROL!$M$4:$M$105, H4, CONTROL!$E$4:$E$105), "")

Drag this formula down to match the number of unique tags generated in column H.

Full Example

Assume your summary worksheet is named "Summary":

• Unique Cable Tags: In Summary!H4:

=UNIQUE(FILTER(CONTROL!$M$4:$M$105, CONTROL!$M$4:$M$105 <> ""))

• Sum Lengths for Unique Tags: In Summary!I4:

=IF(H4<>"", SUMIF(CONTROL!$M$4:$M$105, H4, CONTROL!$E$4:$E$105), "")

Visualizing the Summary Sheet

Your summary sheet should look something like this after entering the formulas:

Important Points

• Dynamic Array Formulas: The UNIQUE function creates a dynamic array of unique values that automatically updates as data in the "CONTROL" worksheet changes.
• Summing Unique Lengths: The SUMIF function sums the lengths for each unique tag listed in column H.

Handling Duplicates in Length Calculation

If you still face issues with duplicated lengths due to the presence of multiple entries of the same tag, consider the following approach:

• Consolidate Data: Ensure your data in the "CONTROL" worksheet doesn't contain duplicate tags for the same lengths, or handle this through data preprocessing to clean up duplicates.
• Advanced Formula: If needed, you can use more complex formulas or Power Query to consolidate data before performing the summary calculations.

If this doesn't fully address your needs or if there are any specific issues, please provide more details, and I can refine the solution accordingly. The text, steps and functions were created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.