Forum Discussion
Sort Column according to another column with blank rows in between
Hey there,
I‘m struggling with a table I‘d like to create, without killing myself using copy paste for hours.
I‘ve got the following data:
- every X minutes 11 parts have to be delivered
- I‘ve got the 11 parts in one table with a column looking like: 30, 60, 90, 120, …5000+
- some of the X minutes are weird numbers like 17
I‘ve used the formatting to mark the identical numbers red, but also I‘d like to sort the columns according to the time.
It could look like this:
Time - Part A - Part B
5 - null - null
10 - null - null
15 - 15 - null
20 - null - 22
25 - null - null
30 - 30 - null
35 - null - null
40 - null - null
45 - 45 - 44
…
Null could also just be empty and the time should be regarded as an interval.
I‘d very much appreciate a suggestion or method on how to implement this.
Thank you for your help.
Kind regards,
Tim
To sort the columns in Excel according to the time column while keeping the blank rows in between, you can use a combination of sorting and formula-based approaches. Here's a step-by-step guide:
- Create a new column next to your existing data. Let's call it "Sort Helper."
- In the Sort Helper column, enter the formula =IF(A2<>"",ROW(),"") assuming your time column is in column A. This formula assigns a unique number to each non-blank cell in the time column, and blanks will be assigned an empty string.
- Drag the formula down to cover all the rows with data.
- Select the entire table, including the Sort Helper column.
- Go to the "Data" tab in the Excel ribbon and click on "Sort."
- In the Sort dialog box, select the Sort Helper column as the sort column and choose "Values" as the sort type.
- Click "OK" to apply the sort. This will rearrange the table based on the time column while keeping the blank rows intact.
- You can remove the Sort Helper column if desired.
This method leverages a helper column to assign unique numbers to non-blank cells, allowing you to sort the table based on this helper column. The blank rows will retain their position in the sorted table.
Note: If you have formulas or references in other columns that are affected by the sorting, make sure to adjust them accordingly to maintain their intended behavior after sorting. The text and the steps are the result of various AI's put together.
My answers are voluntary and without guarantee!
I hope this helps!
