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.

 

[Norie]

Apollo

What's the logic behind the conversion?

 

[malcom]

thats also what i think.. i dont find any relation between the two..
except for
maximum of three characters...
A or B used..

 

[mortgageman]

thats also what i think.. i dont find any relation between the two..
except for
maximum of three characters...
A or B used..

I'm not sure, but I suspect that is suposed to be part of the puzzle.

Gene Klein

 

[Norie]

Gene

Without knowing the logic behind the conversion how can anyone supply formula?

 

[Loggie]

It appears excel already has this as a feature in Advanced Filtering.

Select the list range
Select the criteria range
Select the "copy to another location" button
select the "unique records only" button
Select the "copy to" location to put the results.
then execute.

The result will be as depicted, one occurance of each identical record, sorted ascending in the new location.

 

[mortgageman]

Without knowing the logic behind the conversion how can anyone supply formula?

One can deduce the logic and then the formula. Lets say I tell you set A ={1,2,3,4} and set B={1,4,9,16} and I say come up with a formula to make the elements in set A go to the elements in set B. You would "first" have to come up with the "logic" (squaring) and then come up with the formula.
In easy cases like the one I just did, it is hard to see the steps between logic and formula. In harder cases, like apolloh, thinking about the logic is a significant part of the puzzle.

Gene Klein

 

[Norie]

Gene

Why is A in C1?

Why is AA in C2?

 

[mortgageman]

Gene

Why is A in C1?

Why is AA in C2?

I have no idea. I'm not even sure that I'm right about the puzzle including figuring out the logic. (A couple of words from apolloh would clear things up for sure) But if I am right about that, then my lack of ability to figure it out doesn't imply that it can't be figured out. I can't figure most things out. My lack of ability only proves my lack of ability - not the existence or lack thereof of a formula. I spent years thinking about Fermat's Last Theorm - to no avail. And yet it was proven. All it took was someone (a whole lot) smarter.

It would also help to know why Column C is shorter than Column A. Did apolloh just not copy it down (unlikely) or do more than one elements of the domain map to the same element of the range (that would be my guess- but then I could be wrong)


Gene Klein

 

[apolloh]

Oh，Sorry!My Poor English!

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

Level3 items are no duplication items from colomn A
Level2 items are Left(Leve3,2)
Level1 items are Left(level3,1)

And Column C sort on ascend.

In Column C，there is only one formula which could be copied down。