Marking Case-Sensitive Unique Values in Excel

Published on

Marking case-sensitive unique values provides several benefits compared to merely extracting them in an Excel spreadsheet.

I’m referring to a formula that returns TRUE if a value is case-sensitively unique; otherwise, it returns FALSE. Once you achieve this, you can use these Boolean values for numerous tasks in Excel, such as:

  • Extracting case-sensitive unique values in a column
  • Filtering case-sensitive unique records
  • Highlighting duplicates based on case sensitivity

We do not rely on any drag-down formulas for this. To mark case-sensitive unique values, you can use the following formula in Excel:

=VSTACK(TRUE,MAP(A1:A7, A2:A8, LAMBDA(value1,value2,NOT(OR(EXACT(A1:value1,value2))))))

Example of Marking Case-Sensitive Unique Values in Excel

Consider an Excel spreadsheet with some values in the range A1:A8. If you apply the above formula in B1, the result will be as follows:

Marking case-sensitive unique values in Excel

This method effectively marks case-sensitive unique values in an Excel spreadsheet.

How to Adapt the Formula

When using this formula, make these adjustments:

  • Replace A1 with the cell reference of the first row in the range.
  • Replace A1:A7 with the range reference from the first row to the second-to-last row.
  • Replace A2:A8 with the range reference from the second row to the last row.

Formula Explanation

=VSTACK(TRUE,MAP(A1:A7, A2:A8, LAMBDA(value1,value2,NOT(OR(EXACT(A1:value1,value2))))))

The EXACT(A1:value1, value2) function compares values case-sensitively. The MAP function iterates through each value in the arrays, so the formula processes pairs like EXACT(A1:A1, A2), EXACT(A1:A2, A3), and EXACT(A1:A3, A4).

The EXACT function returns an array of TRUE or FALSE values. If the current row value appears anywhere above, the EXACT output will include a TRUE.

The OR function combines the results of EXACT to determine if a match exists.

The NOT function converts TRUE to FALSE, ensuring that unique values return TRUE. If a value appears anywhere above, the formula will mark it as FALSE.

This process corresponds to rows 2 through the last row in the range. The first row will always be considered a unique value, so we append a TRUE at the top using VSTACK.

Real-Life Uses of Marking Case-Sensitive Unique Values in Excel

As mentioned earlier, marking rows containing case-sensitive unique values has several benefits:

1. Extract Case-Sensitive Unique Values

To extract case-sensitive unique values from a range, use:

=FILTER(A1:A8, B1#)
Example of extracting unique values from a column case-sensitively in Excel

If you prefer not to use the helper formula in B1, replace B1# with the MAP formula directly.

2. Filter Records from Case-Sensitive Unique Rows

Follow a similar approach to filter case-sensitive unique records in a table. Mark the relevant column using the formula and filter the table based on the Boolean results.

3. Highlight Case-Sensitive Unique Values

To highlight case-sensitive unique values:

  1. Select the range A2:A8.
  2. Click Home > Conditional Formatting > New Rule.
  3. Select “Use a formula to determine which cells to format.”
  4. Enter:=B1=TRUE
  5. Click Format and choose a fill color.
  6. Click OK to close the Format Cells dialog box.
  7. Click OK to close the New Formatting Rule dialog box.

This will highlight the case-sensitive unique values in the selected range.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Expert Prashanth KV is a Diamond Product Expert in Google Sheets, officially recognized by Google for his contributions to the Docs Editors Help Community and featured in the Google Product Experts Directory. Explore his blog to learn advanced formulas, automation tips, and problem-solving techniques to elevate your spreadsheet skills.

Mode of Comma-Separated Numbers in Excel (Dynamic Array)

There is no dedicated function in Excel to directly find the mode of comma-separated...

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

Count Consecutive Workday Absences in Google Sheets

This tutorial offers a powerful formula-based solution to count consecutive workday absences in Google...

More like this

Mode of Comma-Separated Numbers in Excel (Dynamic Array)

There is no dedicated function in Excel to directly find the mode of comma-separated...

Sort Each Row Individually in Excel Using a LAMBDA Formula

Sorting rows in Excel typically refers to rearranging entire datasets based on values in...

Sort by Field Labels Using the SORT and XMATCH Combo in Excel

Want to sort your Excel data by column names instead of column positions? Learn...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.