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

Question

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

Answer 1

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

See examples:

• https://support.microsoft.com/en-us/office/unique-function-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e

Answer 2

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)

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.