Formula Challenge III ! Convert The Code

[apolloh]

Formula Challenge I:
http://www.mrexcel.com/board2/viewtopic.php?t=156081
Formula Challenge II:
http://www.mrexcel.com/board2/viewtopic.php?t=160246

Book2
	A	B	C	D
1	AAB		A
2	AAB		AA
3	BBB		AAA
4	AAB		AAB
5	BBB		AB
6	ABB		ABA
7	AAA		ABB
8	BAB		B
9	ABB		BA
10	BBB		BAB
11	BAB		BB
12	ABB		BBA
13	BBB		BBB
14	BBB
15	ABB
16	AAA	Convert Column A To C
17	BBA
18	ABA
19	BAB
20	ABA
Sheet1

Rules：
1。Your formula must be a single cell formula. (e.g. no helper columns) But names are acceptable。
2。A “A” in “AXX” should be
thought as a product code not only a character，so if you use a function code("A"),it's invalid.

 

[apolloh]

Does anyone want to try it?

 

[Domenic]

Does anyone want to try it?

I'm able to return a list of unique values, in ascending order. But as far as Level 1 and Level 2 items, I'm not able to come up with a solution.

Now, from my limited experience, I don't think it's possible. But I'm hoping that someone can prove me wrong.

 

[apolloh]

To：Domenic

Haha。You should be wrong this time，There are 3 methods at least to solute this kind of problem。I think you can prove it by yourself。：）

 

[fairwinds]

Hi,

A little long...

=TRIM(CHAR(LEFT(SMALL(--CHOOSE({1,2,3},CODE($A$2:$A$21)&CODE(MID($A$2:$A$21,2,1))&CODE(RIGHT($A$2:$A$21)),CODE($A$2:$A$21)&CODE(MID($A$2:$A$21,2,1))&32,CODE($A$2:$A$21)&3232),1+SUM(COUNTIF($C$1:C1,CHOOSE({1,2,3},$A$2:$A$21,LEFT($A$2:$A$21,2),LEFT($A$2:$A$21))))),2))&CHAR(MID(SMALL(--CHOOSE({1,2,3},CODE($A$2:$A$21)&CODE(MID($A$2:$A$21,2,1))&CODE(RIGHT($A$2:$A$21)),CODE($A$2:$A$21)&CODE(MID($A$2:$A$21,2,1))&32,CODE($A$2:$A$21)&3232),1+SUM(COUNTIF($C$1:C1,CHOOSE({1,2,3},$A$2:$A$21,LEFT($A$2:$A$21,2),LEFT($A$2:$A$21))))),3,2))&CHAR(RIGHT(SMALL(--CHOOSE({1,2,3},CODE($A$2:$A$21)&CODE(MID($A$2:$A$21,2,1))&CODE(RIGHT($A$2:$A$21)),CODE($A$2:$A$21)&CODE(MID($A$2:$A$21,2,1))&32,CODE($A$2:$A$21)&3232),1+SUM(COUNTIF($C$1:C1,CHOOSE({1,2,3},$A$2:$A$21,LEFT($A$2:$A$21,2),LEFT($A$2:$A$21))))),2)))


Confirmed with Ctrl + shift + enter in C2 and dragged down.

Book1.xls
	A	B	C	D
1
2	AAB		A
3	AAB		AA
4	BBB		AAA
5	AAB		AAB
6	BBB		AB
7	ABB		ABA
8	AAA		ABB
9	BAB		B
10	ABB		BA
11	BBB		BAB
12	BAB		BB
13	ABB		BBA
14	BBB		BBB
15	BBB
16	ABB
17	AAA
18	BBA
19	ABA
20	BAB
21	ABA
Sheet1

 

[apolloh]

Hi,fairwinds

Your formula do work.But Rule 2。A “A” in “AXX” should be
thought as a product code not only a character，so if you use a function code("A"),it's invalid.

One solution first:

Define names:

Data_O=Sheet1!$A$1:$A$20

Row_O=ROW(INDIRECT("1:"&ROWS(Data_O)))

Data_Order	=LOOKUP(0+RIGHT(SMALL(COUNTIF(Data_O,"<="&Data_O)*100000+Row_O,Row_O),5),Row_O,Data_O)

Row_E=ROW(INDIRECT("1:"&ROWS(Data_O)*3))

Data_Ext=LEFT(LOOKUP(INT((Row_E-1)/3)+1,Row_O,Data_Order),MOD((Row_E-1),3)+1)

Result=LOOKUP(SMALL(IF(MATCH(Data_Ext,Data_Ext,)=Row_E,Row_E),ROW(INDIRECT("1:"&COUNT(1/(MATCH(Data_Ext,Data_Ext,)=Row_E))))),Row_E,Data_Ext)

CLG5.xls
	A	B	C	D
1	AAB		A
2	AAB		AA
3	BBB		AAA
4	AAB		AAB
5	BBB		AB
6	ABB		ABA
7	AAA		ABB
8	BAB		B
9	ABB		BA
10	BBB		BAB
11	BAB		BB
12	ABB		BBA
13	BBB		BBB
14	BBB
15	ABB
16	AAA
17	BBA
18	ABA
19	BAB
20	ABA
Sheet1

 

[fairwinds]

Your formula do work.But Rule 2。A “A” in “AXX” should be
thought as a product code not only a character，so if you use a function code("A"),it's invalid.

Does this mean that the codes and the part of the codes can have different numbers of charachters then?

In what order should the list be sorted in that case?

If you define parts of the formula to avoid too many nestings and reduce the length you could do something like:

=LOOKUP("zzzz",CHOOSE({1,2,3},INDEX($A$2:$A$21,MATCH(SMALL(CHOOSE({1,2,3},COUNTIF($A$2:$A$21,"<"&$A$2:$A$21)+1,(COUNTIF($A$2:$A$21,"<"&LEFT($A$2:$A$21,2))+1)*0.99,(COUNTIF($A$2:$A$21,"<"&LEFT($A$2:$A$21))+1)*0.98),SUM(COUNTIF($C$1:C1,CHOOSE({1,2,3},$A$2:$A$21,LEFT($A$2:$A$21,2),LEFT($A$2:$A$21))))+1),COUNTIF($A$2:$A$21,"<"&$A$2:$A$21)+1,0)),INDEX(LEFT($A$2:$A$21,2),MATCH(SMALL(CHOOSE({1,2,3},COUNTIF($A$2:$A$21,"<"&$A$2:$A$21)+1,(COUNTIF($A$2:$A$21,"<"&LEFT($A$2:$A$21,2))+1)*0.99,(COUNTIF($A$2:$A$21,"<"&LEFT($A$2:$A$21))+1)*0.98),SUM(COUNTIF($C$1:C1,CHOOSE({1,2,3},$A$2:$A$21,LEFT($A$2:$A$21,2),LEFT($A$2:$A$21))))+1),(COUNTIF($A$2:$A$21,"<"&LEFT($A$2:$A$21,2))+1)*0.99,0)),INDEX(LEFT($A$2:$A$21),MATCH(SMALL(CHOOSE({1,2,3},COUNTIF($A$2:$A$21,"<"&$A$2:$A$21)+1,(COUNTIF($A$2:$A$21,"<"&LEFT($A$2:$A$21,2))+1)*0.99,(COUNTIF($A$2:$A$21,"<"&LEFT($A$2:$A$21))+1)*0.98),SUM(COUNTIF($C$1:C1,CHOOSE({1,2,3},$A$2:$A$21,LEFT($A$2:$A$21,2),LEFT($A$2:$A$21))))+1),(COUNTIF($A$2:$A$21,"<"&LEFT($A$2:$A$21))+1)*0.98,0))))

 

[apolloh]

Hi,fairwinds

Thank you. The formula is too long and exceed 7 times of nesting.It too hard to read.Would you mind difine sevel names to reduce the lenght and nest of the formula.

 

[apolloh]

Thank for Erik to remind me to provide all solutions which I had known.If you have any else solution,please post it also.

Ctg5.xls
	A	B	C	D
1	AAB		A	A
2	AAB		AA	AA
3	BBB		AAA	AAA
4	AAB		AAB	AAB
5	BBB		AB	AB
6	ABB		ABA	ABA
7	AAA		ABB	ABB
8	BAB		B	B
9	ABB		BA	BA
10	BBB		BAB	BAB
11	BAB		BB	BB
12	ABB		BBA	BBA
13	BBB		BBB	BBB
14	BBB		Solution II	Solution III
15	ABB		wutong9988	apolloh
16	AAA
17	BBA
18	ABA
19	BAB
20	ABA
Solution

Names for all the solutions	
Item	=Solution!$A$1:$A$20
Row	=ROW(INDIRECT("1:"&ROWS(Item)*3))
	
Names only for the solution II	
Item_W	=LEFT(T(OFFSET(Solution!$A$1,ROUNDUP(Row/3,)-1,,,)),MOD(Row-1,3)+1)
Order_W	=COUNTIF(Item,">="&Item_W)-MOD(Row-1,3)
	
Names only for the solution III	
Row1	=ROW(INDIRECT("1:"&ROWS(Item)))
Row2	=ROW(INDIRECT(ROWS(Item)+1&":"&ROWS(Item)*2))
Row3	=ROW(INDIRECT(ROWS(Item)*2+1&":"&ROWS(Item)*3))
Item_A	=CHOOSE(INT((Row-1)/ROWS(Item))+1,LOOKUP(Row,Row1,LEFT(Item)),LOOKUP(Row,Row2,LEFT(Item,2)),LOOKUP(Row,Row3,Item))
Order_A	=MMULT(--(Item_A>=TRANSPOSE(Item_A)),Row*0+1)

PS:Solution I by gdliyy,solution II by wutong9988,solution III by myself, all the providor of the solutions are the members of http://club.excelhome.net
It is one of my challenges also in the competion forum of http://club.excelhome.net at Jul 1,2005

 

[fairwinds]

The formula is too long and exceed 7 times of nesting.It too hard to read.Would you mind difine sevel names to reduce the lenght and nest of the formula.

That's what I wrote when I posted it.

Seing the other solutions you posted, I take it is always one character per level. If so why would it not be ok to use the code value?

 

[apolloh]

Hi,fairwinds

Waiting for your some names in your solution.

When the levels of the code and the length of the each level are adjusted,the formulas in those 3 solutions are still easy to be changed.But if you use a code value,it must be fiexed as 1 chareater for each level.