Pick scattered entries from many columns and order them A-Z in one column

[Hitch75]

Hi guys

In the attached file, I have a large area of cells (blue), some of which contain words.
I need a formula or macro which will pick all the entries and group them in one single column (yellow one) without empty lines.
At the end they have to be ordered A-Z.
You can use several columns if needed.

Help would be appreciated... thanx!!

[AlKey]

Please see attached file. Column BL holds combined values and BM holds Sorted

Is this something you can work with?

[Hitch75]

AlKey, it doesn't work.
When I delete the blue area and type new entries in a few places, they get collected at the BOTTOM of your right column and in some lines I get #N/A...

[Hitch75]

Any help anyone?

[AlKey]

If you delete data in blue area your formulas will show nothing. Formulas do not retain data after it was deleted from the source. Also if you tried to modify formulas remember to re-enter them with using ALT+CTRL+SHIFT key combination as these are array formulas.

[Hitch75]

I think you didn't understand me. Please try to delete the blue area (at that moment the formulas will show nothing, of course) and then type again some new entries in several blue cells.
In the "sorted" column you will get first lots of "#N/A"s and then the entries appear at the bottom of the list.

[AlKey]

Ok, I have replaced the second formula with a dynamic one.

Please see attache file.

[Hitch75]

Now it works! The only problem is, if two entries are exactly the same, there will be an empty line in the list instead of that entry showing up twice...
If you can fix this too, would be great, otherwise this will do.

[AlKey]

Now it works! The only problem is, if two entries are exactly the same, there will be an empty line in the list instead of that entry showing up twice...
If you can fix this too, would be great, otherwise this will do.

Unfortunately I don't have a solution to this right now but I will keep this in mind and hopefully I find one. If nothing else, please don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

[newdoverman]

@AlKey
Please explain the text format "R0000C0000" that you used for the TEXT function. I can't find any reference to this format and don't understand it. Could this possibly by a reference using R1C1 style of cell referencing?

[AlKey]

@AlKey
Please explain the text format "R0000C0000" that you used for the TEXT function. I can't find any reference to this format and don't understand it. Could this possibly by a reference using R1C1 style of cell referencing?

Yes, it is a reference to R1C1. There is more detailed explanation for this given by Marcelo Branco for a formula that features this bit.

http://www.mrexcel.com/forum/excel-q...ml#post4012074

[newdoverman]

@AlKey
Awesome! Thanks. I have never had cause to use this but who knows

To eliminate the blanks add one more column and use this ARRAY FORMULA:

=IFERROR(INDEX($BM$2:$BM$200,MATCH(1,INDEX((COUNTIF($BN$1:BN1,$BM$2:$BM$200)=0)*($BM$2:$BM$200<>""),0),0)),"")

[Hitch75]

Thanks a lot guys!!! It's perfect now.