-2

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

wittman
  • 225
  • 5
  • 25
  • 1
    [Why not images of Code and Sample Data](http://meta.stackoverflow.com/questions/285551/285557#285557) and [Discourage screenshots of code and/or errors](http://meta.stackoverflow.com/questions/303812). –  May 12 '16 at 09:05
  • Because: i din't find anything on google for my problem (5 hours search); 2 answers didn't get my problem as i have it; html tags should apply on the pictures in the thread tags, for site SEO; the problem doesn't require vba code; if i put link to a site-for-picture, people don't go to that site because of spam. – wittman May 12 '16 at 09:29
  • That is count not .value - what i need. – wittman May 12 '16 at 09:46

1 Answers1

3

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).

XOR LX
  • 7,527
  • 1
  • 14
  • 15