ROW sorting / Numbers

[ricardoveras]

Hey Guys,

Firstly, I want to welcome myself to the forum and thank you in advance for all those that read and reply. I am completely new to EXCEL so please bare with me. I would like to know if its possible to sort NUMBERs from lease (1,2,3...) to greater than numbers (...10,11,13) within rows, I know its possible within a COLUMN. My file is attached but let me explain. So I have the following data in an EXCEL sheet as followed:

CELL1- 11,9,7,3,1,22,25,13,9
CELL2- 24,12,9,1,5,3,2,18,25
CELL3- 3,2,9,6,14,19,25,11,12

etc,... Now I want to be able to highlight all CELLS and SORT so it is like this...

CELL1- 1,3,7,9,11,13,22,25
CELL2- 1,2,3,12,18,24,25
CELL3- 2,3,6,9,11,12,19,25

I know how to do it individually (ROW by ROW, but I have over 1,000 rows)

Also guys, is there a formula to find any NUMBERS sequence that was given within the data that repeated, and how many time?



Example: Using the same data

CELL1- 1,3,7,9,11,13,22,25
CELL2- 1,2,3,12,18,24,25
CELL3- 2,3,6,9,11,12,19,25
CELL4- 1,2,3,12,18,24,25

I dont know hot, but the formula will let me know that CELL2 (row) and CELL4 repeated and the numbers are as followed: 1,2,3,12,18,24,25

Hopefully I am making sense. Thanks again Guys!

P.S. I am using EXCEL version 2003.

[FDibbins]

In 2007 onwards, after you highlight the range and go into the SORT window, there is an Options button that will allow you to sort by row, but this will only do it 1 row at a time.
Another option might be to create a 2nd table next to the 1st, and use the SMALL() function, copied down and across

For the counting, I would suggest set up a small table with 1-to-your-largest-number,

[mmmarks]

Hi
Below is code to sort Row By Row. Try in sample file

Please Login or Register  to view this content.

[ricardoveras]

MMMARKS,

This might be the code I am looking for. But I don't know how to add this CODE in EXCEL I might be asking too much, but can you maybe add this code into the file I attached in the original post?

[ricardoveras]

FDibbins, Thank you for the reply. Yes I am aware of the SORTING by ROW but I haave over 1,000 ROWS, and that will take forever.

Another option might be to create a 2nd table next to the 1st, and use the SMALL() function, copied down and across

For the counting, I would suggest set up a small table with 1-to-your-largest-number,

I don't understand what you are trying to say? Can you provide a little bit more of information?

[CGillitt]

FDibbons answer is how I would have approached (before resorting to VBA). The SMALL function returns the nth smallest item in a range of cells, so SMALL($A6:$O6,1) would provide the smallest. FDibbons used Column(A$1) to return 1 (i.e. column number one) to easily increase smallest from 1 to 15 in your example.

For the counting - depends how often you need to do this. I would take the results of the adjacent table created using SMALL() and paste into next sheet as values (paste special - values). Sort that table (by col A then by B then by C etc etc through all the columns) then compare each pair of rows using the following formula:

Please Login or Register  to view this content.

This must be entered as an array formula (use ctrl-shift-enter) and assumes you've pasted the values into A1 on another sheet. This will show you which rows have a match - none actually do in your example.