How to sort by alphabetic order without repeating in a line

[Summer - Rain]

Greetings,
i need some help; in column A i have Customer's Names in column B Customer's Surnamas

Col:A Col:B
Mike Conroy
John Smith
Simon Conroy
Dave Mont
Brad Orton
Brad Orton
Mika Davidson
Mika Davidson
Vale Andersone
Kenji Igawa
Asen Adkins
Simon Conroy

i need to sort Customer's Names by alphabetic order without repeating in a line

Asen Adkins
Brad Orton
Dave Mont
John Smith
Kenji Igawa
Mika Davidson
Simon Conroy
Vale Andersone
Brad Orton
Mika Davidson
Simon Conroy
Mike Conroy


Thanks guys!!!

[AlKey]

Make sure that your first row has headers: Apply Filter to the first row and sort column "B" from A to Z, then use Remove Duplicates excel feature located under Data tab on the ribbon.

[alansidman]

Here is how I would do this: Firstly, since I don't know what version of excel you are using since you did not put that in your profile, I will use the 2003 version to explain. If you have a later version, then you can seek out where the items are on the Ribbon.
1. Sort the two columns on Column A.
2. Filter the data, Advanced Filter, Set the criteria as the whole range including the headers.
3. Select Unique values, and copy to a designated location.

The advanced wizard should walk you through this. In Excel 2003 this is on the Data Menu.

[newdoverman]

I use Excel 2010 and this is how I proceeded.

1/ Sort the list in order according to column A
2/ Combined Columns A and B into column C by =A1&" "&B1
3/ Entered this formula in cell E2 (could be any column other than A, B and C) Array Entered and copied down column E until blank cells came up.

Please Login or Register  to view this content.

[Summer - Rain]

First of all i want to thank you guys for your replies.
I have tried all you told me and thats not what i need so i realized i did not express myself correctly (english is not my native language), please forgive me.

I need all names/surnames in the list not to delete duplicates ones, i just dont need them repeating in a row, this is what i mean:

for example - in the List 1 Brad Orton, Simon Conroy and Mika Davidson repeat in a row, i still need them in list but not repeating in the row, the List 2 is what i need - all names are sorted by alphabetic order, when the first row is finished starts the second one, thats why after Vale Andersone comes Brad Orton (for second time) and after Brad Orton comes Mika Davidson (second time) and so on....

List 1
Mike Conroy
John Smith
Simon Conroy
Dave Mont
Brad Orton
Brad Orton
Mika Davidson
Mika Davidson
Vale Andersone
Kenji Igawa
Asen Adkins
Simon Conroy


List 2
Asen Adkins
Brad Orton
Dave Mont
John Smith
Kenji Igawa
Mika Davidson
Simon Conroy
Vale Andersone
Brad Orton
Mika Davidson
Simon Conroy
Mike Conroy

[sktneer]

See if this is what you are trying to achieve.

[Summer - Rain]

Thanks for your reply but thats not what i need, i do need duplicates in list just not in a row.

[AlKey]

Judging by your last explanation, the example you have provided contradicts the principle by witch data is sorted.

[sktneer]

I think this is what you need. Please see the attached sheet.

[Summer - Rain]

Wow, thanks a lot mate, this is exactly what i need.


Best regards

[sktneer]

You're welcome. Thanks for the feedback.

Yes correct. You only need to change the col. and range reference as per your need. Please pay attention in doing that because at one place in the formula the range is one row more than other ranges in the formula. E.g. at one place in the formula Row($A$2:$A$14) has been used while other ranges are $A$2:$A$13. And no need to tell that this is an array formula which requires confirmation with Ctrl+Shift+Enter instead of just Enter.
Hope this helps.

Moreover if that takes care of your question, please mark your thread as solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.