Combine Two Columns of Names and Create Third Alphabetized Column

I created a sample spreadsheet but I am not able to upload it for some reason. I have uploaded samples files before (using Manage Attachments) but today for some reason, it won't upload the Excel file from my computer.

The spreadsheet has names in columns A and C. I want to be able to create a third column (in the sample it is E) that combines A and C and alphabetizes the list into E.

In the real world, A and C are variable lengths and will change month to month.

Thank you in advance! (I will keep trying to upload the sample file)

Thank you for the reply. I am still trying to upload the sample file without success.

To make my situation clearer, I want to take two columns of names and make one column of names and then alphabetize the list. I do not want to do something like add a last name from one column and a first name from another column and then put them together. I want to intermingle the two columns and have it alphabetized. I know my sample file would make this clearer but until I can upload it... grrrr.

sth like this?

Abab	Kibab
Cabab	Ubab
Ebab	Zubab

and result:

Abab
Cabab
Ebab
Kibab
Ubab
Zubab

edit: Ford was faster

I will study your post - thank you for taking the time to do this. In the meantime - here is my sample file.

Sandy - that is exactly it.

Originally Posted by sandy666

edit: Ford was faster

Yes he faster was but you have the right idea. He appended column A with column B and then alphabetized them. I will have to remember this because I may need to use this in the near future.

But you really want formula ?
I think some hits on keyboard is much faster
ctrl+c, ctr+v, data, sort AZ

problem with upload so external link should be working

sorted column with helper column

maybe it will help you ...

Originally Posted by jhren

With helpers (no array formulas)

Alternate link: https://drive.google.com/file/d/0B24...ew?usp=sharing

Thank you - this worked perfectly!! I change it a little. I took out the hard coding of the row references and took the entire columns of data (i.e. a:a, g:g, i:i, etc.) and I moved the helper columns to the left of the alphabetized list. This makes it cleaner for the data I collect from the alphabetized list.

Thank you everyone for your wonderful suggestions!!! As always - this forum is the best!

Originally Posted by sandy666

problem with upload so external link should be working

sorted column with helper column

maybe it will help you ...

I am so glad it wasn't just me. I was going crazy trying to figure out why I couldn't upload my file. I had no idea there was a minimum file size for the upload (taking two files and uploading them together solved that problem).

You're right but since the two columns of names come from different data sources, I can't predict month to month how many names will will come from each source. So putting them in two columns is the logical choice.


Sent from my iPad using Tapatalk

Check out jhren's post. It does this exactly (with three helper columns). It works great. I thought I set the status of this as solved. I am on my iPad right now so I will log in and make sure. Thank you.


Sent from my iPad using Tapatalk

Thank you. I'll try this out too.


Sent from my iPad using Tapatalk

Originally Posted by Tony Valko

Here's my offering...

Create the combined list...

Enter this formula in G3 and copy down until you get blanks:

=IFERROR(IF(ROWS(G$3:G3)>ROWS(A$3:A$30),INDEX(C$3:C$15,ROWS(G$3:G3)-ROWS(A$3:A$30)),INDEX(A$3:A$30,ROWS(G$3:G3))),"")

Very slick formula.....and simple! I couldn't figure out how to deal with zero's / empty cells.

Originally Posted by Tony Valko

Here's my offering...

Create the combined list...

Enter this formula in G3 and copy down until you get blanks:

=IFERROR(IF(ROWS(G$3:G3)>ROWS(A$3:A$30),INDEX(C$3:C$15,ROWS(G$3:G3)-ROWS(A$3:A$30)),INDEX(A$3:A$30,ROWS(G$3:G3))),"")

Create the sorted list...

Enter this array formula** in E3 and copy down until you get blanks:

=IFERROR(INDEX(G$3:G$50,MATCH(SMALL(IF(G$3:G$50<>"",COUNTIF(G$3:G$50,"<"&G$3:G$50)),ROWS(E$3:E3)),IF(G$3:G$50<>"",COUNTIF(G$3:G$50,"<"&G$3:G$50)),0)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

That is pretty slick! Thank you!!

Tony - I was playing with your formulas and I found a way around having the references as fixed rows. I created three Names: Females, Males, and Alpha_Combine. In each of the Names I have defined the column lengths like this:

Females

Formula:

Please Login or Register  to view this content.

Males

Formula:

Please Login or Register  to view this content.

Alpha_Combine

Formula:

Please Login or Register  to view this content.

Then in column G I changed the formula to:

Formula:

Please Login or Register  to view this content.

In column E I changed the {Array} formula to:

Formula:

Please Login or Register  to view this content.

Thank you again... this is a very slick combination.