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]

Solution IV by feildsun a member of http://club.excelhome.net

This solution use a code value,so it is rather limited.

Item =$A$1:$A$20
Row =ROW(INDIRECT("1:"&ROWS(Item)*3))

Item_F =LEFT(T(OFFSET($A$1,MOD(Row,ROWS(Item)),0)),INT((Row-1)/ROWS(Item))+1)
Order_F =MMULT(CODE(MID(Item_F&"00",{1,2,3},1)),{10000;100;1})

=LOOKUP(MATCH(SMALL(IF(MATCH(Item_F,Item_F,0)=Row,Order_F),ROW(INDIRECT("1:"&COUNT(1/(MATCH(Item_F,Item_F,0)=Row))))),Order_F,),Row,Item_F)

 

[fairwinds]

Hi,fairwinds

Waiting for your some names in your solution.

I give you a revised formula instead, It's shorter so it does not require any names and it can be adjusted to any lengt of the levels.


=LEFT(INDEX($A$2:$A$21,MATCH(SMALL(IF(CHOOSE({1,2,3},MATCH($A$2:$A$21,$A$2:$A$21,0)=ROW($A$2:$A$21)-1,MATCH(LEFT($A$2:$A$21,2),LEFT($A$2:$A$21,2),0)=ROW($A$2:$A$21)-1,MATCH(LEFT($A$2:$A$21),LEFT($A$2:$A$21),0)=ROW($A$2:$A$21)-1),CHOOSE({1,2,3},COUNTIF($A$2:$A$21,"<"&$A$2:$A$21),COUNTIF($A$2:$A$21,"<"&LEFT($A$2:$A$21,2)),COUNTIF($A$2:$A$21,"<"&LEFT($A$2:$A$21)))),ROW()-ROW($C$1)),COUNTIF($A$2:$A$21,"<"&$A$2:$A$21),0)),MOD(SMALL(IF(CHOOSE({1,2,3},MATCH($A$2:$A$21,$A$2:$A$21,0)=ROW($A$2:$A$21)-1,MATCH(LEFT($A$2:$A$21,2),LEFT($A$2:$A$21,2),0)=ROW($A$2:$A$21)-1,MATCH(LEFT($A$2:$A$21),LEFT($A$2:$A$21),0)=ROW($A$2:$A$21)-1),CHOOSE({1,2,3},COUNTIF($A$2:$A$21,"<"&$A$2:$A$21),COUNTIF($A$2:$A$21,"<"&LEFT($A$2:$A$21,2)),COUNTIF($A$2:$A$21,"<"&LEFT($A$2:$A$21)))+{0.3,0.2,0.1}),ROW()-ROW($C$1)),1)*10)

is 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
22
Sheet2

 

[apolloh]

Hi,fairwinds

Thanks for your new solution on this topic.
I wish you wouldn't mind that I had defined sevel names to make it easy to understand for other people and myself.

Solution IV By fairwinds

Item	=$A$1:$A$20
Unique	=CHOOSE({1,2,3},MATCH(Item,Item,0)=ROW(Item),MATCH(LEFT(Item,2),LEFT(Item,2),0)=ROW(Item),MATCH(LEFT(Item),LEFT(Item),0)=ROW(Item))
Rank	=CHOOSE({1,2,3},COUNTIF(Item,"<"&Item),COUNTIF(Item,"<"&LEFT(Item,2)),COUNTIF(Item,"<"&LEFT(Item)))

Result=LEFT(T(OFFSET(Item,MATCH(SMALL(IF(Unique,Rank),ROW(INDIRECT("1:"&COUNT(1/Unique)))),COUNTIF(Item,"<"&Item),0)-1,)),MOD(SMALL(IF(Unique,Rank+{0.3,0.2,0.1}),ROW(INDIRECT("1:"&COUNT(1/Unique)))),1)*10)

And let's look forward to more solutions:D

 

[fairwinds]

I dont like that.

I think naming makes it less transparent.

To make it more transparent I would use several columns with formulas instead. The only reason I crammed everything into a long formula was that your challenge stated a single cell formula. I don't see the point in alowing names but not allow extra columns.

Furter you have added OFFSET wich is volatile and slows it down and apparently you need dereferencing or why else the extra function call for T().


I do like these types of challenges but you need to be very specific about the rules and they also need to have some realism i.e. something that could happen IRL.

 

[apolloh]

Hi,fairwinds

Yes.A name is less transparent than several helper ranges of cells.And it's more difficult to move/copy a formula with a name to another workbook than in cells.

But if we need a formula only in one cell,a name should be easy to read than a long formula with many duplicated-parts.

One-cell formula usually creates some arrays in rememeber in the procedure of calculaiton.It is one difficult thing what we do challenge for because it impels us continuously to think deeply and be exicting.

If we put parts of formula into severl columns,the question become to be easy,because not more arrays are needed and the mid-result can be stored in the cells.

Sometimes we do need a formula only in one cell,because it is not useful for us to display the procedure of calculaton and not convenient to be adjust accordingly.Certainly,if the mid-result is valuable for reports-reader,we should show them out.

As we know,OFFSET is a volatile function,but sevel applications of them does not affect the formulas' speed so much.We can use a multiple-cells array equation to reduce times of applying them.

Another way to avoid using OFFSET is to use LOOKUP function

=LEFT(LOOKUP(MATCH(SMALL(IF(Unique,Rank),ROW(INDIRECT("1:"&COUNT(1/Unique)))),COUNTIF(Item,"<"&Item),0)-1,)),ROW(INDIRECT("1:"&COUNT(1/Unique))),Item),MOD(SMALL(IF(Unique,Rank+{0.3,0.2,0.1}),ROW(INDIRECT("1:"&COUNT(1/Unique)))),1)*10)

Maybe INDIRECT is necessary here.

I do like these types of challenges but you need to be very specific about the rules and they also need to have some realism i.e. something that could happen IRL.

Is IRL "In Real Life"?

eg.I am an accountant in China.It is useful to me to prepare a expense report from a list of many detail entries as following table.

<TABLE1>			<TABLE2>				
Date	Code	Amout		Code	Summary
01-01	AAB	534		A	9136
01-02	AAB	1088		AA	4418
01-03	BBB	1198		AAA	1798
01-04	AAB	998		AAB	2620
01-05	BBB	519		AB	4718
01-06	ABB	1050		ABA	1197
01-07	AAA	679		ABB	3521
01-08	BAB	559		B	8311
01-09	ABB	1052		BA	1741
01-10	BBB	1240		BAB	1741
01-11	BAB	631		BB	6570
01-12	ABB	582		BBA	1206
01-13	BBB	955		BBB	5364
01-14	BBB	1452			
01-15	ABB	837			
01-16	AAA	1119			
01-17	BBA	1206			
01-18	ABA	539			
01-19	BAB	551		
01-20	ABA	658

MORE CHALLEGES! MORE INTERST! MORE EXCITING! MORE REALISM! ON THEIR WAYS! YOU HER HIM ME! COME TOGETHER! JUST JOIN IN! FOR MORE EXPERIENCE EXCHANGING!