How do I combine data from two or more columns into a single column?

Question

Level 1

4 points

How do I combine data from two or more columns into a single column?

Let's say I have a table with three columns: A, B & C. I want to append the values found in the rows of column A with the values of the rows in columns B and have the results appear in column C.

For example,

A B C

1 6 1

2 7 2

3 8 3

4 9 4

5 10 5

6

7

8

9

10

I don't mind duplicate values or the order of the resultant list (I can deal with that myself), but is there a formula (perhaps utilizing the VLookup or Index functions) which will produce the result?

Obviously, I could just copy/paste the values from column A onto column C and then copy/paste the values from column B below that, but it would be very helpful to my overall project to have this done automatically.

Posted on Jan 12, 2022 9:54 AM

Reply

Answer 1

Top-ranking reply

Barry

Level 9

59,957 points

Jan 12, 2022 7:20 PM in response to curtprolix

I'm assuming your example is accurate in showing there are no empty cells between the first and last values recorded in each of columns A and B.

If that is correct, this example should do what you want:

The formula shown below the table is entered in cell C2, and filled down to C12 (or further).

In each row, IFS checks the condition expressed up to the first comma. If that condition is false, IFS skips the action expresses after the comma, and repeats with the next condition.

IF the condition returns true, IFS performa the action stated by the part in that row and following the comma.

The last condition in the set is set to true, so the last action is performed when there has been no true result in the previous sets.

Results are shown in column C.

For convenience, here is a copyable version of the formula, which can be copied, then pasted into C2 and filled down.

IFS((ROW(cell)−1)>COUNT(A)+COUNT(B),"",(ROW(cell)−1)>COUNT(A),INDEX(B,ROW(cell)−COUNT(A)),TRUE,INDEX(A,ROW(),))

Regards,

Barry

Reply

Answer 2

Barry

Level 9

59,957 points

Jan 14, 2022 6:29 PM in response to curtprolix

Hi Curt,

Pretty much the same solution, but using a different function from the same 'family.'

Bottom formula is the one used for the first version of the question.

The COUNT function counts only cells that contain Numbers or quasi Numeric values (eg. Dates)

For the text version, use COUNTA which counts cells containing text (and any other type of value. As can be seen below, the COUNTA version works with text or numbers (or, in this case, both.

Regards,

Barry

Reply

Answer 3

curtprolix Author

Level 1

4 points

Jan 14, 2022 4:26 PM in response to Barry

Thank you so much Barry, this was a great solution and really helped to increase my understanding.

I do have a follow-up question though: I noticed that what seems to be doing the heavy lifting here is the inequality, which is perfect when the cell values are numeric--as in my example--but I'm curious if there is a solution that might also accomplish the task when the cell data are strings.

For example,

A B C

Cat Fish Cat

Dog Ant Dog

Bird Fly Bird

Fish

Ant

Fly

And like before, the specific order of the results column is unimportant, only that all data is copied over.

Thanks again!

Reply

Answer 4

curtprolix Author

Level 1

4 points

Jan 16, 2022 9:57 AM in response to Barry

You are an absolute sage! Thank you so much Barry.

Reply

How do I combine data from two or more columns into a single column?

Similar questions