How to compare data(containing letters and numbers) in excel

Help
,
1

Hi!

I have a problem, where I have IDs that need to be compared and any wrong ID needs to be highlighted. The IDs can contain both numbers and letters. I wanted to know if there is a way to solve this in Uipath?

The comparison is done on 2 parameters:

  1. Duplicates (eg. 1234ABC appears twice so need to be highlighted)
  2. Missing IDs (eg. 1234ABC, 1236ABC → since 1235ABC is missing, therefore 1236ABC needs to be highlighted

Samply excel file as attached : IDs.xlsx (8.6 KB)

Any help would be greatly appreciated! Thank you!

Best Regards!

2

To compare data in excel, A2 and B2 in this example, the formula is as follows:

=IF(A2=B2,“Match”,“”)

Formula for differences
To find cells in the same row with different content, simply replace “=” with the non-equality sign:

=IF(A2<>B2,“No match”,“”)

Matches and differences
And of course, nothing prevents you from finding both matches and differences with a single formula:

=IF(A2=B2,“Match”,“No match”)

Or

=IF(A2<>B2,“No match”,“Match”)

Hope this was helpful.

3

Try this:

Highlight matches and differences in each row
To compare two columns and Excel and highlight cells in column A that have identical entries in column B in the same row, do the following:

Select the cells you want to highlight (you can select cells within one column or in several columns if you want to highlight entire rows).
Click Conditional formatting > New Rule… > Use a formula to determine which cells to format.
Create a rule with a simple formula like =B2=A2 (assuming that row 2 is the first row with data, not including the column header).
To highlight differences between column A and B, create a rule with the formula =B2<>A2
If you are new to Excel conditional formatting, please see How to create a formula-based conditional formatting rule for step-by-step instructions.

Highlight unique entries in each list
Whenever you are comparing two lists in Excel, there are 3 item types that you can highlight:

Items that are only in the 1st list (unique)
Items that are only in the 2nd list (unique)
Items that are in both lists (duplicates) - demonstrated in the next example.
This example demonstrates how to highlight items that are in one list only.

Supposing your List 1 is in column A (A2:A6) and List 2 in column C (C2:C5). You create the conditional formatting rules with the following formulas:
Highlight unique values in List 1 (column A): =COUNTIF(C2:C5, A2)=0
Highlight unique values in List 2 (column C): =COUNTIF(A2:A6, C2)=0

Let me know if this helps.

Regards,
Jerry M