## Multiple cells in a new row

*Article updated on March 14, 2009*

**Question:** How do i put column values into one single column, with each having only one name?

to this:

**Answer: **Use this formula for the first column:

=INDEX(Sheet1!A1:A100,SMALL((IF(Sheet1!B1:Z100<>"",ROW(Sheet1!B1:Z100),"")),ROW())) + Ctrl + Shift + Enter

IF(Sheet1!B1:Z100<>"",ROW(Sheet1!B1:Z100),"")) gets all the row numbers from the cells that have values in the range Sheet1!B1:Z100.

SMALL((IF(Sheet1!B1:Z100<>"",ROW(Sheet1!B1:Z100),"")),ROW()) returns the k-th smallest row number in this data set.

{1,1,1,2,2,3,3,3,3}

INDEX(Sheet1!A1:A100,SMALL((IF(Sheet1!B1:Z100<>"",ROW(Sheet1!B1:Z100),"")),ROW()))

Returns a value of the cell at the intersection of a particular row and column, in a given range (Sheet1!A1:A100).

Use this formula for the second column:

=INDEX(Sheet1!$B$1:$Z$100,SMALL((IF(Sheet1!B1:Z100<>"",ROW(Sheet1!B1:Z100),"")), ROW()),RIGHT(SMALL((IF(Sheet1!B1:Z100<>"",((ROW(Sheet1!B1:Z100))&","&( COLUMN(Sheet1!B1:Z100)))*1,"")),ROW()),FIND(",",SMALL((IF(Sheet1!B1:Z100<>"", ((ROW(Sheet1!B1:Z100))&","&(COLUMN(Sheet1!B1:Z100)))*1,"")),ROW()))-1)-1)

SMALL((IF(Sheet1!B1:Z100<>"",ROW(Sheet1!B1:Z100),"")),ROW()) returns the k-th smallest row number in this data set. This returns row numbers to use in the index function

{1,1,1,2,2,3,3,3,3}

RIGHT(SMALL((IF(Sheet1!B1:Z100<>"",((ROW(Sheet1!B1:Z100))&","&( COLUMN(Sheet1!B1:Z100)))*1,"")),ROW()),FIND(",",SMALL((IF(Sheet1!B1:Z100<>"", ((ROW(Sheet1!B1:Z100))&","&(COLUMN(Sheet1!B1:Z100)))*1,"")),ROW()))-1)-1 This formula returns column numbers to use in the same index function.

{2,3,4,2,3,2,3,4,5}

Here is the result:

Download excel sample file for this tutorial.

multiple-cells-in-new-row

(Excel 97-2003 Workbook *.xls)

**Functions in this tutorial**

**SMALL(**array,k**)** returns the k-th smallest row number in this data set.

**ROW(**reference**)**

returns the row number of a reference

**COLUMN(**reference**)
**returns the column number of a reference

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**IF(**logical_test;[value_if:true];[value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**RIGHT(**text;num_chars**)** returns the specified number of characters from the end of textstring

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

## Share this article