I have an excel spreadsheet where each data set covers three rows. I want to sort the data based on the value in column K, which is in the third row of each data set, there are no values in rows 1 and 2 of each data set. I want to keep the data sets together, and in row order.
I have tried grouping but this doesn't work, and looking up 'how to' on internet has suggested that having a helper row might assist but I can't work out how to do it.
Any suggestions would be greatly appreciated. I am not a 'superuser' by any stretch of the imagination.
2 Answers
If I am understanding your spreadsheet layout correctly you have three rows of grouped data is columns A - J and then some sort of identifier in column k of the third row. My suggestion would be to copy that identifier into column K of the first and second rows also. You can then highlight all of the data and run a custom sort based on column K.
Two approaches:
- If you have an "ID" column which is unique for your the row data (let this column be "A" and suppose you enter your data from second row):
=sumif(a:a,a2,b:b)
(I know max with array formula would be more elegant but sumif is easier to understand and also it's quicker in big datasets). - You can use a formula just based on positions of data (supposing again that your data starts in second row)
= indirect("k"& row() - mod(row()+1,3)+2,true)
This has the drawback that if you sorry your data to split your groups then it'll be corrupted, SDHI you may want to convey your formulas to read values.
