Combine two columns into another sheet

Karina.Perez

Simply trying to combine FirstName and LastName columns into a new sheet. 

=JOIN({FirstName}1:{LastName}1, "-")

This come out #UNPARSEABLE

 

 

L_123

there are a couple ways to so this, the best way is somewhat complex.

If you refer to the cells individually you are going to run out of intersheet links quickly, you only get 100 of them. In order to use the most out of your available links, you need to use ranges and parse what you want out of them based on their location.

To do this you will need to make 2 column references, i'll call them {first} and {last}

Now we can take a look at how to "Join" them together. First we can use an index formula to return the value from a location.

Example:

Raw Data:

 Row#  Column1

1                A

2                B

3                C

4                D

5                E

 

Formula =Index([Column1]1:[Column1]5,1)

will return: A

Formula =Index([Column1]1:[Column1]5,3)

will return: C

 

We can use this to parse the data out of our ranges, then concatenate them. The formula should look something like this

 

=index({first},1) + index({last},1)

Now this will work perfectly, but it will set you up for a lot of manual work, because you need to change the "1" for every single row to reflect the next value. instead what I recommend is to add another column that simply counts up (I'll call it "Counter"). You can either do an autonumber, or what I recommend instead, just type 1 in the first row, 2 in the second, highlight both and drag it down to count up.

 

Once that is created, just change both of the "1"s in the index formula to reference the "Counter" Column.

 

=index({first},Counter1)+index({last},Counter1)

Paul Newcome

You could also build the counter into the formula itself without the additional helper column. You would put 

=INDEX({first}, 1) + INDEX({last}, 1)

in row 1. Then in row 2 you could put

=INDEX({first}, COUNT(Name$1:Name1) + 1) + INDEX({last}, COUNT(Name$1:Name1) + 1)

 

The COUNT(Name$1:Name1) + 1 will count how many rows you have starting from the first row (Name$1) and then stopping at the row above the formula (dragfill will auto-update the row number in the Name1 portion, hence the lack of a $ to lock that in like in the first one). We then add 1 to give you the current row count, and that replaces the additional helper column.

 

Not to say the above is wrong, but it is another option.

L_123

Agreed, the fewer columns the better.

Paul Newcome

Most of the time. Some of the crazier mess is easiest if you spread it out. Haha.