I have a list with names in column A Sheet 1. Each name appears several times. I would like to list each name once in Sheet 2. Basically sheet 2 is a copy of the filter list in column A sheet 1, but I would like it to be dynamic so that it stays updated with the list in column A.

i.e: if a new name is added to column A sheet 1 several times - the list in Sheet 2 will add it once.


Actually you need to generate Unique list of names in Sheet 2 and this array formula solves the issue.

enter image description here

  • Enter this formula in Cell C2 of Sheet 2, finish with Ctrl+Shift+Enter, fill it down.

    {=IFERROR(INDEX(Sheet1!$A$118:$A$132, MATCH(0,COUNTIF($C$1:C1, Sheet1!$A$118:$A$132), 0)),"")}


I would like to suggest to create Dynamic Named Range, to auto update the Unique name list in Sheet 2, as soon you add names in Sheet 1.

enter image description here

How it works:

  • Click Define Name or, press Ctrl+F3 to reach to Name Manger, then click the New button.
  • Write an appropriate name in Name box and this formula in Refers to.

    =OFFSET(Sheet1!$A$118, 0, 0, COUNTA(Sheet1!$A:$A), 1)
  • Finally enter this array Formula in Cell C2 of Sheet 2, finish with Ctrl+Shift+Enter and fill down.

    {=IFERROR(INDEX(NameList, MATCH(0,COUNTIF($C$1:C1, NameList), 0)),"")}

Adjust cell references in the formula as needed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.