Multi column data sorting problem

peterlam84

New Member
Joined
Aug 15, 2024
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hello, I want to sort the data sheet "A" and aim to get the result sheet "C":
result.png

  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.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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...
 
Upvote 0
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~ :)
 
Upvote 0
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?
 
Upvote 0
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:
Excel Formula:
= SORTBY(A1:C16,
         A1:A16,1,
         IFERROR(VALUE(RIGHT(C1:C16,2)),VALUE(RIGHT(C1:C16,1))),1
         )&""
 
Upvote 0
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].
 
Upvote 0
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:
Excel Formula:
= 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].
 
Upvote 0
See if this works for you:
Excel Formula:
= SORTBY(A1:C16,
         A1:A16,1,
         IFERROR(VALUE(RIGHT(C1:C16,2)),VALUE(RIGHT(C1:C16,1))),1,
         B1:B16,1)&""
 
Upvote 0
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
ABCDEFG
1Item B0045Item A0044DEF2
2Item C0024CD5568Item A0041A5
3Item C0014Item A0001CDE6
4Item B0020C10Item A0005FGH6
5Item A0001CDE6Item A0036ABC6
6Item B0009Item A0011ABC10
7Item C0018ABC13Item A0027A15
8Item A0004Item A0043BC15
9Item C0029Item A0002
10Item B0023B14Item A0004
11Item C0004AB4Item B0011BCD7
12Item B0011BCD7Item B0020C10
13Item C0017BCD15Item B0001BC12
14Item B0019Item B0007D14
15Item A0002Item B0009C14
16Item C0026D13Item B0023B14
17Item A0027A15Item B0030BC14
18Item C0002D2Item B0042DEF14
19Item B0001BC12Item B0006B15
20Item C0033Item B0009
21Item B0007D14Item B0013
22Item B0032Item B0019
23Item C0021Item B0032
24Item C0025DE369Item B0045
25Item C0003CD5Item C0002D2
26Item C0015BCD8Item C0014C3
27Item A0043BC15Item C0004AB4
28Item C0011ABC14Item C0003CD5
29Item A0041A5Item C0015BCD8
30Item C0014C3Item C0032B12
31Item B0009C14Item C0018ABC13
32Item B0030BC14Item C0026D13
33Item A0011ABC10Item C0011ABC14
34Item A0044DEF2Item C0017BCD15
35Item B0042DEF14Item C0025DE369
36Item C0011Item C0024CD5568
37Item B0013Item C0011
38Item A0036ABC6Item C0013
39Item C0032B12Item C0014
40Item C0013Item C0021
41Item B0006B15Item C0029
42Item A0005FGH6Item C0033
Sorting
Cell Formulas
RangeFormula
E1:G42E1=SORTBY(A1:C42,A1:A42,1,IFERROR(--TEXTSPLIT(C1:C42&"",CHAR(SEQUENCE(26,,65)),,1),""),1,B1:B42,1)&""
Dynamic array formulas.
 
Upvote 0

Forum statistics

Threads
1,224,818
Messages
6,181,151
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top