How to extract duplicate personnel from column B to column C using a formula in Excel?

pu rechard 100 Reputation points
2024-09-30T15:30:03.9066667+00:00

How to extract duplicate personnel from column B to column C using a formula in Excel?

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,898 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Viorel 117.3K Reputation points
    2024-09-30T15:53:45.46+00:00

    Try to write to C1 a formula like this: =UNIQUE(B1:B100). To exclude blank cells: =UNIQUE(FILTER(B1:B100,B1:B100<>"")).

    See examples:

    0 comments No comments

  2. Emi Zhang-MSFT 25,071 Reputation points Microsoft Vendor
    2024-10-01T06:48:58.15+00:00

    Hi,

    Try this formula:

    =LOOKUP(,0/FREQUENCY(1,(COUNTIF(B$2:B$20,B$2:B$20)>1)*(COUNTIF(C$1:C1,B$2:B$20)=0)),B$2:B3)

    User's image

    Just checking in to see if the information was helpful. Please let us know if you would like further assistance.


    If the response is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.