Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sam via OfficeKB.com
 
Posts: n/a
Default Return Single Row of Numeric Data to Single Column

Hi All,

I have a Row of numeric data ( in individual columns) that I would like
returned to individual cells in a single column.

My data starts in Column C Row 13 to Column DV Row 13. However, every other
Column in the Row of data is blank. Is it possible to return the data
excluding the blank columns, so there are no blank cells when the data is
returned to a single column?

Sample Data Layout:
C13 BLANK, D13 70, E13 BLANK, F13 45, G13 BLANK, H13 50, I13 BLANK, J13 63,
K13 BLANK etc.

Thanks
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200512/1
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default Return Single Row of Numeric Data to Single Column

Hi!

Try this:

=INDEX(C$13:DV$13,ROWS($1:1)*2)

Copy down as needed.

Biff

"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:58f0e24c9bcaa@uwe...
Hi All,

I have a Row of numeric data ( in individual columns) that I would like
returned to individual cells in a single column.

My data starts in Column C Row 13 to Column DV Row 13. However, every
other
Column in the Row of data is blank. Is it possible to return the data
excluding the blank columns, so there are no blank cells when the data is
returned to a single column?

Sample Data Layout:
C13 BLANK, D13 70, E13 BLANK, F13 45, G13 BLANK, H13 50, I13 BLANK, J13
63,
K13 BLANK etc.

Thanks
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200512/1



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gord Dibben
 
Posts: n/a
Default Return Single Row of Numeric Data to Single Column

Select row 13.

F5SpecialBlanksOK

EditDeleteEntire Columns or Shift Cells Left, your choice depending upon
what's in other columns/rows that you may not want deleted.

Re-select the row and Copy.

Select a cell in column A below your data and Paste SpecailTransposeOKEsc.


Gord Dibben Excel MVP

On Fri, 16 Dec 2005 22:56:28 GMT, "Sam via OfficeKB.com" <u4102@uwe wrote:

Hi All,

I have a Row of numeric data ( in individual columns) that I would like
returned to individual cells in a single column.

My data starts in Column C Row 13 to Column DV Row 13. However, every other
Column in the Row of data is blank. Is it possible to return the data
excluding the blank columns, so there are no blank cells when the data is
returned to a single column?

Sample Data Layout:
C13 BLANK, D13 70, E13 BLANK, F13 45, G13 BLANK, H13 50, I13 BLANK, J13 63,
K13 BLANK etc.

Thanks
Sam

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sam via OfficeKB.com
 
Posts: n/a
Default Return Single Row of Numeric Data to Single Column

Hi Biff,

That was brilliant, thank you.

=INDEX(C$13:DV$13,ROWS($1:1)*2)

Copy down as needed.


Cheers,
Sam

Biff wrote:
Hi!

Try this:

=INDEX(C$13:DV$13,ROWS($1:1)*2)

Copy down as needed.

Biff

Hi All,

[quoted text clipped - 14 lines]
Thanks
Sam


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200512/1
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sam via OfficeKB.com
 
Posts: n/a
Default Return Single Row of Numeric Data to Single Column

Hi Gord,

Thank you for reply and insight using F5:

F5SpecialBlanksOK
EditDeleteEntire Columns or Shift Cells Left, your choice depending upon
what's in other columns/rows that you may not want deleted.

Re-select the row and Copy.

Select a cell in column A below your data and Paste SpecailTransposeOKEsc.


I'm sure the above will be of use to me. However, I've used Biff's nifty
solution and I also avoid deleting any columns.

Cheers,
Sam

Gord Dibben wrote:
Select row 13.

F5SpecialBlanksOK

EditDeleteEntire Columns or Shift Cells Left, your choice depending upon
what's in other columns/rows that you may not want deleted.

Re-select the row and Copy.

Select a cell in column A below your data and Paste SpecailTransposeOKEsc.

Gord Dibben Excel MVP

Hi All,

[quoted text clipped - 12 lines]
Thanks
Sam


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200512/1
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Match Each Numeric occurrence and Return Individual Rows of Data Sam via OfficeKB.com Excel Worksheet Functions 4 October 13th 05 04:22 AM
Return Single Instance of Numeric Values from a Column Sam via OfficeKB.com Excel Worksheet Functions 4 August 26th 05 03:10 AM
Using a Vlookup to return values in a data list? rtjeter Excel Worksheet Functions 2 April 26th 05 05:56 AM
merge data from multiple columns to single column triggerthehorse Excel Worksheet Functions 2 January 17th 05 08:19 PM
how to convert multiple columns of data into one single column? Ah 3 Excel Worksheet Functions 1 November 12th 04 03:33 PM


All times are GMT +1. The time now is 04:43 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"