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

Share and Enjoy:
  • Share/Bookmark

Related posts:

  1. Search for multiple text strings in multiple cells and use in data validation in excel
  2. Search for multiple text strings in multiple cells in excel
  3. Search for multiple text strings in multiple cells in excel, part 2
  4. Text to be on top of column in excel
  5. How to sort text cells filtered by two dates, part 2
  6. Lookup with multiple criteria and display multiple search results using excel formula
  7. Lookup with multiple criteria and display multiple search results using excel formula, part 2