How sort grouped cells

[ABC3132]

Hi
I Have been trying to sort a grouped rows

Sorting Group.xlsx
	A	B
1	Number	Letter
2	6	B
3	9	B
4	5	C
5	8	A
6	3	B
7	2	A
8	10	C
Sheet2

Nonetheless when I do they loose the groups and became mixed up
How I can fix it?
Cheers

 

[GlennUK]

What groups? Can you explain what you mean by groups? And also say in detail what sorting you require.

 

[ABC3132]

Hi GlennUK
Sorry it looks like I haven´t been clear.

The spreadsheet is like that and the numbers are grouped by letter

Sorting Group.xlsx
	A	B
1	Number	Letter
2	8	A
3	2	A
4	6	B
5	9	B
6	3	B
7	5	C
8	10	C
Sheet2

When I group them my table looks like that

Sorting Group.xlsx
	A	B
1	Number	Letter
2	8	A
4	6	B
7	5	C
Sheet2

If i sort them now by number keeping the groups "wrapped", it looks like everything goes fine

Sorting Group.xlsx
	A	B
1	Number	Letter
4	5	C
6	6	B
7	8	A
Sheet2

Nonetheless when I have opened the table all the rows are mixed up among the different groups

Sorting Group.xlsx
	A	B
1	Number	Letter
2	9	B
3	3	B
4	5	C
5	2	A
6	6	B
7	8	A
8	10	C
Sheet2

So what I try to do is when I sort groups to keep all the groups´ rows together.
Many thanks!

 

[GlennUK]

Your required logic bears no resemblance to any series of actions that can be done in Excel to produce the results you want. I cannot suggest a solution for this.

 

[ABC3132]

Your required logic bears no resemblance to any series of actions that can be done in Excel to produce the results you want. I cannot suggest a solution for this.

Thanks anyway...it is what I was afraid of

 

[GlennUK]

To get the sorting you want, you must not use grouping - have extra columns for original order (1,2,3, etc etc), and first number per letter (that is easy, logically, as you can just test whether letter is same as row above, and if propagate from cell above, else propagate from number field of same row). Then sort the whole list by first number field, then original order (2 levels in the same sort, not 2 different sorts). How does that sound?

 

[GlennUK]

Example of sorted list:

 

[ZVI]

Hi,

Another solution with aux Tmp column:

Wb1
	A	B	C
1	Number	Letter	Tmp
2	8	A	8
3	2	A	8
4	6	B	6
5	9	B	6
6	3	B	6
7	5	C	5
8	10	C	5
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=INDEX($A$2:$A$8,MATCH(B2,$B$2:$B$8,0))

Sort A:C by Tmp to get this:

Wb1
	A	B	C
1	Number	Letter	Tmp
2	5	C	5
3	10	C	5
4	6	B	6
5	9	B	6
6	3	B	6
7	8	A	8
8	2	A	8
Sheet1

 

[ABC3132]

Thanks GlennUK & ZVI. I solved as you suggested me. Now it looks like that:

Sorting Group.xlsx
	A	B	C	D	E	F
1	Number	Contact?	Company	Name	Position	TMB
2	100	C	A	Roxanne	Manager	100
3	110		A	Alba	R&D	100
4	200		B	Glenn	CEO	200
5	210	C	B	Zvi	COO	200
6	220	C	B	Rudy	Engineer	200
7	300		C	Maria	Manager	300
8	310	C	C	Elena	Assitant	300
Sheet1

Cell Formulas
Range	Formula
F2:F8	F2	=INDEX($A$2:$A$8,MATCH(C2,$C$2:$C$8,0))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:E8	Expression	=$B2="C"	text	NO

The key reason why I wanted to keep them all together, it was not only because they are a group but also because in the real table they are different names of the same company. The conditional colouring I have added

. is because I want to easily see in the table who the right contact is.
Thanks both for your time.
Have a nice weekend

 

[ZVI]

Glad we can help, thank you for the feedback.
And special thanks for the CEO&COO positions in your table for Glenn and Zvi ?