Go | New | Find | Notify | Tools | Reply | |
Member |
I have some files that contain 4000 records ea, first column is numbered in sequence 1-4000. I need it sorted in this order (1, 161, 321, etc, then 2, 162, 322, etc) Basically needs to skip 160 #'s then start again. Can anyone help with that? | ||
|
The Karmanator |
Could you provide a bit more of a detailed spec? It isn't totally clear to me what you are trying to accomplish. | |||
|
| Member |
The file also has column headers. File is currently sorted like this 1 2 3 4 5 6 7 I need it sorted like this 1 161 321 481 641 801 ... 2 162 322 482 642. 802 .... | |||
|
Member |
The quickest might be to add an extra column with numbers from 1-160 in the cells and then copy that block of 160 repeatedly to the end of the table then sort by that column value...am I explaining it clearly. You might also want to create a column with the spceific row number in it...thats easy too..create a formula that adds 1 to the value of the number in the cell above it and copy that down to the bottom of the table. Remove the formula by copying the whole column and then "paste > special > Values" only. So sort first by row number then by 1 - 160. --------------------------------------- It's like my brain's a tree and you're those little cookie elves. | |||
|
| Member |
This is something I'm going to have to do regularly and repeatedly, so really hoping for a simple formula I can use or something. I really don't know excel at all. | |||
|
His Royal Hiney |
I got it. You need to add another column with the following formula. I'm calling it "Sorter" column. I assume you already have a column for the records going 1, 2, 3, down the rows, right? I further assume it's in column A. If it's not column A, just change the reference in my formula. Lastly, I assume the first data row is row 2. Beginning in row 2, enter the following formula then copy or extend down the row. = MOD (A2, 160) The resulting value is the remainder of dividing the value in column A by 160. Then you do a multiple row sort. First level sort is by the "Sorter" column then second level sort is by the record number column. If you want something "fancier" and being able to sort with just the sorter column, use the following formula: = MOD ( A2, 160) * 100000 + A2. This multiplies the result by 100,000 (multiples of hundreds greater than your highest number record) then add the record number. That way, a sort using just this one column takes into account the 160 rule and the record number together. You're welcome. If you need help with the specifics, let me know and we can just talk by phone if you want. But let me know in this thread. Below is a pic of what I just did in a Spreadsheet to show my solution.  "It did not really matter what we expected from life, but rather what life expected from us. We needed to stop asking about the meaning of life, and instead to think of ourselves as those who were being questioned by life – daily and hourly. Our answer must consist not in talk and meditation, but in right action and in right conduct. Life ultimately means taking the responsibility to find the right answer to its problems and to fulfill the tasks which it constantly sets for each individual." Viktor Frankl, Man's Search for Meaning, 1946. | |||
|
Don't Panic |
I like Rey HRH's elegant modulo solution above. ^^^^  I had a way based on Excel's underappreciated text processing functionality, but the modulo method would be a lot less work. | |||
|
| The Karmanator |
I took the example to be a reordering task not really a sorting task. This assumes the data is already sorted. I'll add it just in case. Here is my output.  So I took the first row is the ordering. The second row is the data. Both are already sorted. The goal is to get the data from the specific locations. Cell D1 is =A1 Cell D2 is =A161 Cell D3 is =A321 and so on. Then Cell D7 is =D1+1 Cell D8 is =D2+1 Cell D9 is =D3+1 and so on Once you have done this through D12 you can just select D7 to D12 and then drag the selection down all the values. Then Cell E1 is =INDIRECT("B"&D1) E2 = =INDIRECT("B"&D2) And so on.This message has been edited. Last edited by: Chance, | |||
|
| Member |
Thanks. I'm going to come back to this on Monday. My head is super fuzzy today from the arm long list of meds I have to take. I'm having a lot of trouble making sense of what y'all are telling me. And I've never done anything more in Excel than print something someone else sent. Appreciate the responses! | |||
|
| The Karmanator |
That is super cool. Way better than mine. One quibble. I would change the mod line so that you get 160 rather than 0 when the results = 0. Something like: =IF(MOD( A2, 160) = 0,160,MOD( A2, 160)) because right now the first values returned are when mod(X,160) is 0 which is when the row is a mod of 160. In the result, the first value is from row 160 and then the next values are when the row is a multiple of row 160. I think the first row should be first value to appear. I think these values (mod = 0) should come after row 159. The same logic applies to the more complex one =IF(MOD(A2, 160) = 0, 160 * 100000 + B2, MOD(A2, 160) * 100000 + B2) | |||
|
| His Royal Hiney |
Thanks for the compliment. The first thing that came to mind was the OP didn't mention the how he wanted the zeros handled which is what you brought up. I suppose your way would make the MOD zeros show up in the end whereas mine shows it in the beginning. At least now, the OP has a choice. I like collaboration. "It did not really matter what we expected from life, but rather what life expected from us. We needed to stop asking about the meaning of life, and instead to think of ourselves as those who were being questioned by life – daily and hourly. Our answer must consist not in talk and meditation, but in right action and in right conduct. Life ultimately means taking the responsibility to find the right answer to its problems and to fulfill the tasks which it constantly sets for each individual." Viktor Frankl, Man's Search for Meaning, 1946. | |||
|
| The Karmanator |
Yea, it isn't super clear from the example what the OP would like to happen. I really like the use of mod here. Very smart. | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
|
© SIGforum 2024