Excel Sorting Data Keeping in Groups

Question

Hello, I'm trying to sort data in a spreadsheet that for example has rows and columns that look like this. I would like to be able to sort by different columns but need to keep the data in the two rows below associated with the header/sort row. See example below. Thanks for any assistance!

145	West Virginia				9/1/2022	7:00:00 PM	1
							-3.5
							42
141	Penn State				9/1/2022	8:00:00 PM	3
							-2
							44
143	Louisiana Tech				9/1/2022	8:00:00 PM	6
							4
							43

For example if I sort by column A I need it to look like this

141	Penn State				9/1/2022	8:00:00 PM	3
							-2
							44
143	Louisiana Tech				9/1/2022	8:00:00 PM	6
							4
							43
145	West Virginia				9/1/2022	7:00:00 PM	1
							-3.5
							42

Not like this

141	Penn State				9/1/2022	8:00:00 PM	3
143	Louisiana Tech				9/1/2022	8:00:00 PM	6
145	West Virginia				9/1/2022	7:00:00 PM	1
							-3.5
							42
							-2
							44
							4
							43

Answer 1

Hi Hayward

Well... see my friend

With the current table structure, It's complicated for Excel to determine automatically that the values (highlighted in yellow) in the last column, belong to the above data record row (group). Users unfamiliar with the scenario might say/assume, that we have some missing data rows (incomplete data input) for instance.

We understand you might be doing that for aesthetic reasons but that simple wish, brings many complications if further calculations and analysis are required.

To achieve the desired output, please follow these steps

1. Fill Down the records as shown in the picture below

2. Select the pertinent Data Range (in the sample picture =A2:G10) and apply the following Conditional formatting formula rule

=COUNTIF($A$2:$A2,$A2)>1

Select the format of the cells Fill Color:= Transparent/No color and Font:=White

This will mask ("apparently hide") the duplicated records as shown in the picture below

3. Only then we can apply the sorting in the Table in column A

I hope this helps you and gives a solution to your problem

Do let me know if you need more help

Regards

Jeovany

Answer 2

create temp table aa as select fillna(F_A) sort_by,* from FillnaSortMergeFirstColumn;select * from aa order by sort_by;

repeat the data to all rows firstly.

Answer 3

You'll have to repeat the data to all rows:

145	West Virginia			9/1/2022	7:00:00 PM	1
145	West Virginia			9/1/2022	7:00:00 PM	-3.5
145	West Virginia			9/1/2022	7:00:00 PM	42
141	Penn State			9/1/2022	8:00:00 PM	3
141	Penn State			9/1/2022	8:00:00 PM	-2
141	Penn State			9/1/2022	8:00:00 PM	44
143	Louisiana Tech			9/1/2022	8:00:00 PM	6
143	Louisiana Tech			9/1/2022	8:00:00 PM	4
143	Louisiana Tech			9/1/2022	8:00:00 PM	43