A Real Challenge - Dynamic Arrays?

[denzo36]

Hi guys,

A real head scratcher today.

I have codes like the below in the 'Rules Codes Triggered' column.

When I pivot this, Excel treats each combination of the three codes as unique and only counts each combo once. What I want to do is count the number of times those code appear together in any order. So really for the above, codes 2044, 2045 and 2048 are appearing with each other 6 times but excel is counting each unique combo of the 3 codes only once.


Is it possible to split each cell and order it lowest to highest like the below?

I have used this to split the string at each character, removing the pipe symbol but the sorting bit I can't figure out

=MID(SUBSTITUTE(B2," | ",""),SEQUENCE(,LEN(SUBSTITUTE(B2," | ","")),1),1)


Thanks in advance

 

[Peter_SSs]

Is this what you are looking for?

25 04 04.xlsm
	B	C
1	Original	Sorted
2	2045 | 2048 | 2044	2044 | 2045 | 2048
3	2045 | 2044 | 2048	2044 | 2045 | 2048
Sort

Cell Formulas
Range	Formula
C2:C3	C2	=TEXTJOIN(" | ",,SORT(--TEXTSPLIT(B2," | "),,,1))

 

[bobsan42]

something like this:

Book1
	B	C	D
4	123456	2045 | 2048 | 2044	2044 | 2045 | 2048
5	123457	2045 | 2044 | 2048	2044 | 2045 | 2048
6	123458	2044 | 2048 | 2045	2044 | 2045 | 2048
7	123459
8	123460
9	123461
Sheet1

Cell Formulas
Range	Formula
D4:D6	D4	=TEXTJOIN(" | ",TRUE,SORT((TEXTSPLIT(C4," | ")),1,1,TRUE))

 

[denzo36]

Wow Peter, 100% what I needed, that is amazing.

Thanks so much for the solution and quick response!

 

[denzo36]

something like this:

Book1
	B	C	D
4	123456	2045 | 2048 | 2044	2044 | 2045 | 2048
5	123457	2045 | 2044 | 2048	2044 | 2045 | 2048
6	123458	2044 | 2048 | 2045	2044 | 2045 | 2048
7	123459
8	123460
9	123461
Sheet1

Cell Formulas
Range	Formula
D4:D6	D4	=TEXTJOIN(" | ",TRUE,SORT((TEXTSPLIT(C4," | ")),1,1,TRUE))

thanks for your reply as well!

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.

If all the numbers are exactly 4 digits then the "--" can be removed from my formula (as in @bobsan42's formula). I included the "--" in case some numbers were longer or shorter than 4 digits

=TEXTJOIN(" | ",,SORT(TEXTSPLIT(B2," | "),,,1))

Also, if you into saving a few characters, the formula could also be written like this.

=TEXTJOIN(" | ",,SORT(TEXTSPLIT(B2,," | ")))

 

[bobsan42]

Thanks for the mention @Peter_SSs

You're welcome. Glad we could help. Thanks for the follow-up.

If all the numbers are exactly 4 digits then the "--" can be removed from my formula (as in @bobsan42's formula). I included the "--" in case some numbers were longer or shorter than 4 digits

=TEXTJOIN(" | ",,SORT(TEXTSPLIT(B2," | "),,,1))

Just to clarify maybe (for future reference): -- turns (or casts?) the strings to numbers (as if using the VALUE function), then the SORT function sorts them as numbers as opposed to sorting as text.
numbers: 24 ,123 ,1221
text: 1221, 123, 24

However, in this particular case the idea is only to obtain identical strings so the order doesn't really matter.

 

[Peter_SSs]

However, in this particular case the idea is only to obtain identical strings so the order doesn't really matter.

Good point.