Search code examples
excelformulascolumnsorting

Get values from columns excel

i have 2 columns A and B with names. I want in column C to have all the names from columns A and B in alphabetic order no matter if are present in more than one cell. So if i have mike1 in more than one cell in A or/and B it should be only one time in column C. Thank you.

enter image description here

Solution

  • Assuming the entries are in the range A2:B10, enter this array formula**, used to determine the expected number of returns, in C1:

    =SUM(IF(A2:B10<>"",1/COUNTIF(A2:B10,A2:B10)))

    Then enter this array formula** in C2:

    =IF(ROWS($1:1)>C$1,"",INDIRECT(TEXT(MIN(IF(COUNTIF(A$2:B$10,"<="&A$2:B$10)=MIN(IF(COUNTIF(C$1:C1,A$2:B$10)=0,IF(A$2:B$10<>"",COUNTIF(A$2:B$10,"<="&A$2:B$10)))),10^5*ROW(A$2:B$10)+COLUMN(A$2:B$10))),"R0C00000"),0))

    Copy down until you start to get blanks for the results.

    Regards

    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).