

LinkBack  Thread Tools  Display Modes 
#1




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 
#2




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. 
#3




Quote:
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? 
Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Sum values within a single cell  shabbaranks  Excel  17  05132014 06:47 PM 
Trying to autofilter 810 rows of numbers to sort in a group  JackPWinner  Excel  0  11252013 10:55 PM 
How to sort word text in alphabetic order .  crossing  Word  8  01202012 09:51 AM 
Outlook 2007 Contacts Sort Order  bianson  Outlook  2  01272011 10:32 AM 
list of documents come up in random order  any ideas how to sort this?  tallscot49  Word  0  07182010 12:40 AM 