How to Return the Highest Grade for Each Unique Drill-Hole ID in WPS Spreadsheets
Question details
Find the highest assay or gold-grade value for each unique drill-hole ID in a large WPS Spreadsheets dataset containing multiple samples per hole.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is managing a large drill-hole assay workbook with multiple sample rows per hole and wants to create a concise summary of the maximum grade value per unique hole ID.
- Observed behavior
- Manual lookup and filtering becomes time-consuming or error-prone when thousands of drill-hole IDs and sample rows are involved.
Confirm that your drill-hole IDs and grade values are in separate columns and that the grade column contains only numeric values. Also check that your WPS Office version supports the UNIQUE and MAXIFS functions if you plan to use the formula method.
Use UNIQUE and MAXIFS formulas to return the highest grade by drill-hole ID
Best for a live, reusable summary that updates when source data changes or grows.
WPS Spreadsheets supports Excel-compatible functions UNIQUE and MAXIFS. UNIQUE extracts each drill-hole ID once, and MAXIFS returns the largest grade matching that ID.
Click an empty cell such as E2 where you want the unique drill-hole IDs to appear.
Type =UNIQUE(A2:A10000) and press Enter. Replace A2:A10000 with the actual range containing drill-hole IDs.
In the adjacent cell F2, type =MAXIFS(C2:C10000,A2:A10000,E2#) and press Enter. Replace C2:C10000 with the grade column and E2# with the first unique ID cell or spill range.
If your WPS version does not support spilled arrays, replace E2# with E2 and drag the fill handle down alongside the unique ID list.
Sort by drill-hole ID and grade, then remove duplicates
Use when you need a quick one-time result without formulas or when UNIQUE is not available.
Summarize Maximum Drill-Hole Grades in WPS Spreadsheets
Use WPS Spreadsheets' Excel-compatible UNIQUE and MAXIFS functions to automatically return the highest assay value for each unique drill-hole ID in .xlsx workbooks.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, open the .xlsx drill-hole dataset, and confirm the drill-hole ID and grade columns.
- 2. Create the unique drill-hole ID list: Click cell E2, type =UNIQUE(A2:A10000), and press Enter so WPS spills the unique drill-hole IDs.
- 3. Return the highest grade for each ID: In cell F2, type =MAXIFS(C2:C10000,A2:A10000,E2#) and press Enter to display the maximum grade for each unique ID.
- 4. Adjust for your data range: Replace A2:A10000, C2:C10000, and E2# with your actual data range and unique list cell, then copy the MAXIFS formula down if your WPS version does not support spilled arrays.
- 5. Refresh and verify results: Check several drill-hole IDs against filtered source data to confirm that the returned grades are the highest values.
Frequently Asked Questions
Does WPS Spreadsheets support the UNIQUE and MAXIFS functions?
Yes, current versions of WPS Spreadsheets support UNIQUE and MAXIFS. Open your workbook, click an empty cell, and enter the formulas directly. If you see #NAME?, update WPS Office or use Data > Sort and Remove Duplicates.
What do I do if my drill-hole IDs are not in column A and grades are not in column C?
Replace the ranges in the formulas with your actual columns. For example, if IDs are in B2:B5000 and grades are in D2:D5000, use =UNIQUE(B2:B5000) and =MAXIFS(D2:D5000,B2:B5000,E2#).
Can I get the highest grade for each drill-hole ID without formulas?
Yes. Sort the data by drill-hole ID and grade descending from the Data tab, then use Data > Remove Duplicates on the ID column only. The first record kept for each hole will contain the highest grade.
Will the formula result update if I add new drill-hole samples?
If the formulas reference the full data range, new rows inside that range update automatically. For rows added beyond the original range, convert the data to a table or extend the formula ranges, then recalculate if needed.
Is WPS Spreadsheets compatible with Microsoft Excel drill-hole workbooks?
Yes. WPS Spreadsheets opens and saves .xlsx files, so you can work with Excel drill-hole datasets and share results without converting file formats.
