Combine multiple First/Last Name columns into one column, data can contain blank rows

Hi,

Been struggling with this over the last couple of days. I have reports that contain two separate sets of "First" and "Last" names in columns similar to the below example, except the reports can be thousands of rows long:

set1_FirstName	set1_LastName	set2_FirstName	set2_LastName
Jenell P	Judah
		Jaquelyn	Wafford
		Leoma	Shore

The end goal is to merge all these First/Last names together into a new single combined "Full Name" column, keeping any blank rows that may have been present, like this:

combined_FullName

Jenell P Judah

Jaquelyn Wafford

Leoma Shore

The two sets never have a data overlap, if names are in the first set the second set is always blank and/or vice versa.
But either set can contain middle initials, have blank rows or in some cases be completely blank altogether.
It's this variable nature I'm struggling with accounting for when merging the name sets together. A simple "=A2&" "&B2" type formula won't cut it.

I have attached an example workbook that contains four sheets of the different scenarios that the reports can contain along with a full_name column with the expected result for each scenario.

Thanks for any insight into this problem.

In F2 (or whatever column you want to use for full name:

Formula:

Please Login or Register  to view this content.

Then copy down

The only difference is that your blank names contain one space and my formula would leave the cell completely blank, but that's easily changed by changing the second condition to " " instead of "". Like this:

Formula:

Please Login or Register  to view this content.

If the data never overlaps the just concatenate all 4 fields together and then trim the result.

Please Login or Register  to view this content.

That way blank rows will trim to zero characters and others will trim the spaces before and after.

Good call pjwhitfield, much more efficient!

Originally Posted by pjwhitfield

If the data never overlaps the just concatenate all 4 fields together and then trim the result.

Please Login or Register  to view this content.

That way blank rows will trim to zero characters and others will trim the spaces before and after.

Simple!

One scenario I can't get it to work properly yet though is if only "set 2" is populated with names. Has to do with the way I've been auto-filling the formulas up to now;

Please Login or Register  to view this content.

As I've been using "A" column to define how far the auto-fill should go this obviously doesn't work if A is blank, it'll end up auto-filling upwards into the header row.

Ideas? It's a very rudimentary way of auto-filling I know.

Edit: I worked around this problem by simply adding another column that is always 100% populated with the full range of the report and use that for figuring out the row count.

Thank you guys.