Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 09-05-2014, 01:12 AM
FromF FromF is offline Windows 7 64bit Office 2010 64bit
Novice
 
Join Date: Sep 2014
Posts: 2
FromF is on a distinguished road
Question Selecting values from different rows in a table and sort them in order in a single row

Hi,

I want to use a formula, or set of formulas, for selecting values from a data table were the number of values differ between rows (see attachment). I do not want to cut and paste or use any commands as I need to make this a executable function.

For instance, 9 rows and 21 cols (as in attachment), in the first row i have 21 values, in the 2nd only 4 and in the 3rd I have 5 etc. I want to "merge" these values in order in a single row, i.e. from B2 to V2 (in this case) and then continue with B3 to E3, and B4 to F4 etc, all in a single new row.

So in the table I have a total of 103 values in 9 rows, i want to sort them in a single row, i.e. from e.g. B15 to CZ15 (I think it will be CZ).


Thanks!
Fredrik
Attached Files
File Type: xlsx My table.xlsx (10.1 KB, 3 views)
Reply With Quote
  #2  
Old 09-05-2014, 05:38 PM
excelledsoftware excelledsoftware is offline Windows 7 64bit Office 2003
IT Specialist
 
Join Date: Jan 2012
Location: Utah
Posts: 458
excelledsoftware will become famous soon enough
Default

Its almost clear as to what you want. I good way of explaining is to include a desired result in your sample sheet. However if I understand right you want to "Concatenate" all of the values in each column into a single cell. The only thing not quite clear is how do you want each value separated. My assumption would be for row 3 with a cross section of 1 would result in 1.25;1.50;1.75;2.00

Is this correct and if so are you against it being a VBA script.
Reply With Quote
  #3  
Old 09-09-2014, 02:30 AM
FromF FromF is offline Windows 7 64bit Office 2010 64bit
Novice
 
Join Date: Sep 2014
Posts: 2
FromF is on a distinguished road
Default

Quote:
Originally Posted by excelledsoftware View Post
Its almost clear as to what you want. I good way of explaining is to include a desired result in your sample sheet. However if I understand right you want to "Concatenate" all of the values in each column into a single cell. The only thing not quite clear is how do you want each value separated. My assumption would be for row 3 with a cross section of 1 would result in 1.25;1.50;1.75;2.00

Is this correct and if so are you against it being a VBA script.
Not exactly. My question is not posed correctly. it is not Merge or Concatenate that i want to do, see attached file.

The function in AA3
=IF(COLUMNS($AA$3:AA$3)>COUNT($C$3:$Y$13);"";SMALL ($C$3:$Y$13;COLUMNS($AA$3:AA$3)))
works well for the defined area ($C$3:$Y$13), all height values are displayed in ascending order, each unique value in a single cell, from AA3 to DY3.
However, I would also like to write a function that is repeatable in the sense that i could just drag it downwards (or copy paste into cell AA20) for the next set of sub sample data (C22:Y39), here called Plot 8, without including the first set of data (Plot 7, ($C$3:$Y$13)).
So I need an altered version of the selection process in the function above. Or a completely new approach?
Attached Files
File Type: xlsx My_Table3.xlsx (17.6 KB, 0 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sum values within a single cell shabbaranks Excel 17 05-13-2014 06:47 PM
Trying to autofilter 8-10 rows of numbers to sort in a group Jack-P-Winner Excel 0 11-25-2013 10:55 PM
How to sort word text in alphabetic order . crossing Word 8 01-20-2012 09:51 AM
Outlook 2007 Contacts Sort Order bianson Outlook 2 01-27-2011 10:32 AM
list of documents come up in random order - any ideas how to sort this? tallscot49 Word 0 07-18-2010 12:40 AM


All times are GMT -7. The time now is 11:49 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft