Find common names in 2 different tabs on an excel spreadsheet? Common data?

Question

I need help trying to find patient names that match/common data between 2 different reports on 2 different tabs. All names are in column A on both tabs.

Answer 1

Hello Shaina Russell,   Welcome to the Microsoft Community.

Method 1: Using "Conditional Formatting" to Highlight Common Names

1. Select column A in the first worksheet (assuming it's A1:A100).
2. In Excel's "Home" tab, click on "Conditional Formatting".
3. From the dropdown menu, select "New Rule".
4. In the New Formatting Rule dialog box, choose "Use a formula to determine which cells to format".
5. In the formula box, enter the following formula: =COUNTIF(Sheet2!A:A, A1)>0
   • Here, "Sheet2" is the name of the second worksheet, and A:A is the column range in the second worksheet that contains the patient names.
6. Click the "Format" button and set a noticeable format (e.g., fill color or font color).
7. Click "OK" to save the rule, then click "Apply" and "OK" to complete the setup.

All cells in the first worksheet that have matching names in column A of the second worksheet will be highlighted with the format you set.

Method 2: Using the "VLOOKUP" Function to Find Common Names

1. In the first worksheet, enter the following formula next to column B (assuming it starts from B1):

    =IF(ISERROR(VLOOKUP(A1, Sheet2!A:A, 1, FALSE)), "Does not exist", "Exists")
   

2. Drag this formula down to apply it to all the cells you need to compare.

This way, column B will show whether each patient name exists in the second worksheet. If it exists, it will display "Exists"; otherwise, it will display "Does not exist".

If you think this information is helpful, or if you still have any questions about it, feel free to let me know in your reply!Best RegardsEliac | Microsoft Community Support Specialist

Answer 2

Hi,

In cell C2, enter this formula

=FILTER(A2:A15,COUNTIF('Sheet1 (2)'!A2:A18,A2:A15))

Hope this helps.

Answer 3

Hi Shaina Russell. I haven't received your reply yet and would like to know if my suggestions helped solve your problem. I provided two methods that can highlight duplicate columns or show whether these names exist in another worksheet. Ashish Mathur's method can organize duplicates in a new column. You can choose which method to use based on your actual file situation, such as whether there is enough space to add extra columns or whether you need to organize these duplicates for further processing.

Additionally, for Ashish Mathur's formula, a simple supplement: in his example, the formula references A2 to A15. If your column A data is extensive, you only need to change it to A:A. Or, if your first few rows contain headers, start from the first cell containing names, such as A2:A.

I hope our responses are helpful to you. If you still have questions, feel free to reply!

Best Regards  Eliac | Microsoft Community Support Specialist