How to Count SPDU Types by RNTI in Excel and Power Query
Question details
The user needs to summarize and count SPDU sequence types (like SPDU-1, SPDU-2) for each RNTI, handling complexities like SFN value restarts, multiple slots, and grouping by rlcCtrlAckSn_.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Counting and grouping network or sequential data logs with multiple grouping layers.
- Observed behavior
- The goal is to obtain grouped counts of specific SPDU types mapped to each RNTI while optionally factoring in SFN, slots, and rlcCtrlAckSn_ for uniqueness.
Ensure your raw data is organized with clear column headers such as RNTI, SPDU Type, SFN, and rlcCtrlAckSn_. If your dataset is extremely large, consider converting it to an Excel Table first to improve calculation performance and Data Model integration.
Use Power Query to Group Data and Remove Duplicates
Best for automated, repeatable transformations where records must be unique by RNTI, SFN, slot, and rlcCtrlAckSn_ before counting.
Power Query allows you to clean up your data, strip out duplicates across multiple criteria, and dynamically group your sequence counts. This method is highly efficient for data dumps where values like SFN restart after 1023.
Select your data range, go to the Data tab, and click 'From Table/Range' to load it into the Power Query Editor.
If records must be unique, select the RNTI, SFN, slot, and rlcCtrlAckSn_ columns, right-click the headers, and choose 'Remove Duplicates'.
Select the RNTI and SPDU type columns, navigate to the Transform tab, and click 'Group By'.
In the Group By dialog, choose 'Count Rows' as the operation, name the new column 'SPDU Count', and click OK.
Click 'Close & Load' on the Home tab to output the summarized table back into your worksheet.
Use COUNTIFS Formulas for Basic Counting
A straightforward formula approach if you only need basic counts of specific SPDU types for a given RNTI without complex duplicate removal.
Create a PivotTable to Summarize SPDU Types
A visual, drag-and-drop method to summarize large datasets dynamically without writing manual formulas.
Easily Analyze and Group Data with WPS Spreadsheet
WPS Spreadsheet offers powerful data handling capabilities, including PivotTables, advanced formulas, and robust data filtering, allowing you to summarize complex datasets effortlessly.
- 1. Install WPS Office: Download and install WPS Office for free to access WPS Spreadsheet.
- 2. Open your dataset: Open your existing Excel data file containing the RNTI and SPDU logs in WPS Spreadsheet.
- 3. Use PivotTables: Use the built-in PivotTable feature under the Insert tab to quickly group your RNTI and SPDU records.
- 4. Apply advanced formulas: Alternatively, use standard Excel-compatible formulas to calculate custom summaries instantly.
Frequently Asked Questions
How do I handle SFN values that restart after 1023?
When grouping data, ensure you include an additional sequential identifier (like timestamp, a unique record ID, or a slot identifier) in your Power Query or PivotTable grouping. This ensures that the restarted SFN values aren't incorrectly merged with the previous cycle's data.
Can I count multiple criteria dynamically without hardcoding 'SPDU-1'?
Yes. Instead of typing "SPDU-1" manually in the COUNTIFS formula, you can reference a cell that contains the text (e.g., =COUNTIFS($H:$H, $A2, $K:$K, B$1)). This allows you to drag the formula across columns for different SPDU types efficiently.
Why is my PivotTable counting duplicate records?
A standard PivotTable counts all rows that match the criteria. To count only unique instances across multiple fields, you need to add your data to the Data Model when creating the PivotTable and use the 'Distinct Count' option in the value field settings.
