Multi column data sorting problem

[peterlam84]

Hello, I want to sort the data sheet "A" and aim to get the result sheet "C":

1. Column B - order Smallest to Largest basically;
2. Column A - order A to Z;
3. Column C - group the same number digit together base on order of Column B

I can only get the result sheet "B" after single or multi sorting. Is there any method, code or formula can satisfy it?

Thank you.

 

[hagia_sofia]

Hello, could you please answer the question whether you have two sheets and you want to sort one (A) based on another (C) or is it just one sheet that is supposed to be sorted as shown above? If it is the latter, then what is the pattern as column B is not sorted from smallest to largest - e.g. 5 - 8 - 25 - 9...

 

[peterlam84]

Hello, could you please answer the question whether you have two sheets and you want to sort one (A) based on another (C) or is it just one sheet that is supposed to be sorted as shown above? If it is the latter, then what is the pattern as column B is not sorted from smallest to largest - e.g. 5 - 8 - 25 - 9...

Hello, there is only one sheet supposed to be sorted. "sheet A" is the original data which I have to sort and "sheet C" is the final target result which I wanna to have.

Column C in "sheet C" is grouping the same number digit together manual by myself.
It bases on same Item (Column A) group together, then order smallest to largest in Column B
(e.g. [0005/IN1, 0008/MID1, 0025/OUT1] grouping count as "0005", [0010/OUT2, 0017/IN2] grouping count as "0010"......, etc)
So the order of logic in Column B is 0005 > 0009 > 0010 > 0011 > 0015 ......, etc.

This order of logic is a little bit complex for normal sorting. I can only get the result as "sheet B" after custom sort. I am finding the method to sort it automatically. There is time consuming by manual for huge data.
It that clear for my problem? Cheers~

 

[hagia_sofia]

My apologies but I still do not follow what is the pattern - e.g. why is the Item A - 0025 - OUT1 where it is? 0025 seems to be higher than 0009?

 

[Alex Blakenburg]

Your column B logic doesn't make sense to me, nor does having blanks in Column C in different places for the same Item.
The closest I can get is this:

= SORTBY(A1:C16,
         A1:A16,1,
         IFERROR(VALUE(RIGHT(C1:C16,2)),VALUE(RIGHT(C1:C16,1))),1
         )&""

 

[peterlam84]

My apologies but I still do not follow what is the pattern - e.g. why is the Item A - 0025 - OUT1 where it is? 0025 seems to be higher than 0009?

Maybe let's ignore the character "IN/MID/OUT". If [Column C] has data, the priority will be [Column A] group "Item A" > [Column C] group "1" then [Column B] group "0005/0008/0025". If there is no data in [Column C], ordering will be according to [Column B].

 

[peterlam84]

Your column B logic doesn't make sense to me, nor does having blanks in Column C in different places for the same Item.
The closest I can get is this:

= SORTBY(A1:C16,
         A1:A16,1,
         IFERROR(VALUE(RIGHT(C1:C16,2)),VALUE(RIGHT(C1:C16,1))),1
         )&""

Maybe let's ignore the character "IN/MID/OUT". If [Column C] has data, the priority will be [Column A] group "Item A" > [Column C] group "1" then [Column B] group "0005/0008/0025". If there is no data in [Column C], ordering will be according to [Column B].

 

[Alex Blakenburg]

See if this works for you:

= SORTBY(A1:C16,
         A1:A16,1,
         IFERROR(VALUE(RIGHT(C1:C16,2)),VALUE(RIGHT(C1:C16,1))),1,
         B1:B16,1)&""

 

[Peter_SSs]

I think that it might need one more 'sort by array' to account for ..

the priority will be [Column A] group "Item A" > [Column C] group "1" then [Column B] group "0005/0008/0025"

and

Column C - group the same number digit together base on order of Column B

For example, the coloured sections in column F below.
This sorting also allows in case the final number part in column C could consist of more than 2 digits (eg orange cells)

24 08 19.xlsm
	A	B	C	D	E	F	G
1	Item B	0045			Item A	0044	DEF2
2	Item C	0024	CD5568		Item A	0041	A5
3	Item C	0014			Item A	0001	CDE6
4	Item B	0020	C10		Item A	0005	FGH6
5	Item A	0001	CDE6		Item A	0036	ABC6
6	Item B	0009			Item A	0011	ABC10
7	Item C	0018	ABC13		Item A	0027	A15
8	Item A	0004			Item A	0043	BC15
9	Item C	0029			Item A	0002
10	Item B	0023	B14		Item A	0004
11	Item C	0004	AB4		Item B	0011	BCD7
12	Item B	0011	BCD7		Item B	0020	C10
13	Item C	0017	BCD15		Item B	0001	BC12
14	Item B	0019			Item B	0007	D14
15	Item A	0002			Item B	0009	C14
16	Item C	0026	D13		Item B	0023	B14
17	Item A	0027	A15		Item B	0030	BC14
18	Item C	0002	D2		Item B	0042	DEF14
19	Item B	0001	BC12		Item B	0006	B15
20	Item C	0033			Item B	0009
21	Item B	0007	D14		Item B	0013
22	Item B	0032			Item B	0019
23	Item C	0021			Item B	0032
24	Item C	0025	DE369		Item B	0045
25	Item C	0003	CD5		Item C	0002	D2
26	Item C	0015	BCD8		Item C	0014	C3
27	Item A	0043	BC15		Item C	0004	AB4
28	Item C	0011	ABC14		Item C	0003	CD5
29	Item A	0041	A5		Item C	0015	BCD8
30	Item C	0014	C3		Item C	0032	B12
31	Item B	0009	C14		Item C	0018	ABC13
32	Item B	0030	BC14		Item C	0026	D13
33	Item A	0011	ABC10		Item C	0011	ABC14
34	Item A	0044	DEF2		Item C	0017	BCD15
35	Item B	0042	DEF14		Item C	0025	DE369
36	Item C	0011			Item C	0024	CD5568
37	Item B	0013			Item C	0011
38	Item A	0036	ABC6		Item C	0013
39	Item C	0032	B12		Item C	0014
40	Item C	0013			Item C	0021
41	Item B	0006	B15		Item C	0029
42	Item A	0005	FGH6		Item C	0033
Sorting

Cell Formulas
Range	Formula
E1:G42	E1	=SORTBY(A1:C42,A1:A42,1,IFERROR(--TEXTSPLIT(C1:C42&"",CHAR(SEQUENCE(26,,65)),,1),""),1,B1:B42,1)&""
Dynamic array formulas.