Excel Formula to Sort Table Order: Novice “Part – Timer” Member Would appreciate some help form a Board Formula Master..

[DocAElstein]

Excel Formula to Sort Table Order: Novice “Part – Timer” Member Would appreciate some help form a Board Formula Master..


. Hi,
. Novice “Part – Timer” Would appreciate some help form a Formula Master
. I have almost got there answering a Thread but have stretched myself “One formula too far..”
. Appreciate a last bit of help from one of the Formula Master

. As example:
. I should like one spreadsheet formula or two spreadsheet formulas that would work such that when I paste something of this form in A1:B10,

Using Excel 2007
[Table="width:, class:grid"][tr][td]-[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr]
[tr][td]

1​

[/td][td]Part No.[/td][td]Description.[/td][td][/td][/tr]

[tr][td]

2​

[/td][td]AE001[/td][td]Cashews[/td][td][/td][/tr]

[tr][td]

3​

[/td][td]AE001[/td][td]PeeNuts[/td][td][/td][/tr]

[tr][td]

4​

[/td][td]AE008[/td][td]Pecanuss[/td][td][/td][/tr]

[tr][td]

5​

[/td][td]AE007[/td][td]DgsNuts[/td][td][/td][/tr]

[tr][td]

6​

[/td][td]AE002[/td][td]Wallnusse[/td][td][/td][/tr]

[tr][td]

7​

[/td][td]AE007[/td][td]hdshg[/td][td][/td][/tr]

[tr][td]

8​

[/td][td]AE009[/td][td]AlansNuts[/td][td][/td][/tr]

[tr][td]

9​

[/td][td]AE012[/td][td]PeeNuts[/td][td][/td][/tr]

[tr][td]

10​

[/td][td]AE011[/td][td]MixedNuts[/td][td][/td][/tr]

[tr][td]

11​

[/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]DgsNtsFormula[/td][/tr][/table]

.. I then get this:

Using Excel 2007
[Table="width:, class:grid"][tr][td]-[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

1​

[/td][td]Part No.[/td][td]Description.[/td][td][/td][td][/td][/tr]

[tr][td]

2​

[/td][td]AE001[/td][td]Cashews[/td][td]AE001[/td][td]Cashews[/td][/tr]

[tr][td]

3​

[/td][td]AE001[/td][td]PeeNuts[/td][td]AE001[/td][td]PeeNuts[/td][/tr]

[tr][td]

4​

[/td][td]AE008[/td][td]Pecanuss[/td][td]AE002[/td][td]Wallnusse[/td][/tr]

[tr][td]

5​

[/td][td]AE007[/td][td]DgsNuts[/td][td]AE007[/td][td]DgsNuts[/td][/tr]

[tr][td]

6​

[/td][td]AE002[/td][td]Wallnusse[/td][td]AE007[/td][td]hdshg[/td][/tr]

[tr][td]

7​

[/td][td]AE007[/td][td]hdshg[/td][td]AE008[/td][td]Pecanuss[/td][/tr]

[tr][td]

8​

[/td][td]AE009[/td][td]AlansNuts[/td][td]AE009[/td][td]AlansNuts[/td][/tr]

[tr][td]

9​

[/td][td]AE012[/td][td]PeeNuts[/td][td]AE011[/td][td]MixedNuts[/td][/tr]

[tr][td]

10​

[/td][td]AE011[/td][td]MixedNuts[/td][td]AE012[/td][td]PeeNuts[/td][/tr]

[tr][td]

11​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]DgsNtsFormula[/td][/tr][/table]

. I can think of many many ways to do this in Excel and Excel VBA. But I have got a long way with a more complicated total solution using a formula and would like a last try to finish the Formula version off. so

Important requirement is:-
. 1 ) Needs to be one “Normal” or “CSE” Formula, which would be placed in cell C2 and dragged down and across to fill the range C2 to D10
Or
One “normal” or “CSE” formula for C3 and another “normal” or “CSE” formula for C4, both of which would again be dragged down to fill the range C2 to D10

Preferred requirement is
. 2) Preferably formulas would be compatible with XL 2003.

Possible Mods to make it easier would be

. a) Headings can be removed or replaced with specific content – a mod in the practice I have sometimes found helpful

. b ) Modifying my data with a help column of the form B2=Right(A,2) so that my data for applying formula would be in B2:C10, and output then in D2:E10 , thus

Using Excel 2007
[Table="width:, class:grid"][tr][td]-[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td]

Part No.​

[/td][td]

​

[/td][td]

Description.​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr][td]

2​

[/td][td]

AE001​

[/td][td]

01​

[/td][td]

Cashews​

[/td][td]

01​

[/td][td]

Cashews​

[/td][/tr]

[tr][td]

3​

[/td][td]

AE001​

[/td][td]

01​

[/td][td]

PeeNuts​

[/td][td]

01​

[/td][td]

PeeNuts​

[/td][/tr]

[tr][td]

4​

[/td][td]

AE008​

[/td][td]

08​

[/td][td]

Pecanuss​

[/td][td]

02​

[/td][td]

Wallnusse​

[/td][/tr]

[tr][td]

5​

[/td][td]

AE007​

[/td][td]

07​

[/td][td]

DgsNuts​

[/td][td]

07​

[/td][td]

DgsNuts​

[/td][/tr]

[tr][td]

6​

[/td][td]

AE002​

[/td][td]

02​

[/td][td]

Wallnusse​

[/td][td]

07​

[/td][td]

hdshg​

[/td][/tr]

[tr][td]

7​

[/td][td]

AE007​

[/td][td]

07​

[/td][td]

hdshg​

[/td][td]

08​

[/td][td]

Pecanuss​

[/td][/tr]

[tr][td]

8​

[/td][td]

AE009​

[/td][td]

09​

[/td][td]

AlansNuts​

[/td][td]

09​

[/td][td]

AlansNuts​

[/td][/tr]

[tr][td]

9​

[/td][td]

AE012​

[/td][td]

12​

[/td][td]

PeeNuts​

[/td][td]

11​

[/td][td]

MixedNuts​

[/td][/tr]

[tr][td]

10​

[/td][td]

AE011​

[/td][td]

11​

[/td][td]

MixedNuts​

[/td][td]

12​

[/td][td]

PeeNuts​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]DgsNtsFormula[/td][/tr][/table]

.. There is no rush as I am breaking my Nts just now getting the understandings and explanations of my complete Formulas a la
Post # 32
http://www.mrexcel.com/forum/excel-questions/818208-code-move-vertical-horizontal-4.html
…

. If someone could help I would be very grateful if they could also “open” up the formula and explain the workings as the OP has asked for explanations as Indeed I am always happy and keen to do anyway………..

Many Thanks
Alan.

 

[DocAElstein]

Using Excel 2007
[Table="width:, class:grid"][tr][td]-[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

5​

[/td][td]

AE007​

[/td][td]

DgsNuts​

[/td][td]

=INDEX($A$2:$A$10, MATCH(SMALL(COUNTIF($A$2:$A$10, "<"&$A$2:$A$10), ROWS($C$1:C4)), COUNTIF($A$2:$A$10, "<"&$A$2:$A$10), 0))​

[/td][td]

=INDEX($B$2:$B$10,SMALL(IF($A$2:$A$10=C5,ROW($A$2:$A$10)-ROW($A$2)+1),COUNTIF($C$2:C5,C5)))​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]DgsNtsFormula[/td][/tr][/table]

… There’s a couple of Dog’s Nuts formulas!!!!

Using Excel 2007
[Table="width:, class:grid"][tr][td]-[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

1​

[/td][td]

Part No.​

[/td][td]

Description.​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr][td]

2​

[/td][td]

AE001​

[/td][td]

Cashews​

[/td][td]

AE001​

[/td][td]

Cashews​

[/td][/tr]

[tr][td]

3​

[/td][td]

AE001​

[/td][td]

PeeNuts​

[/td][td]

AE001​

[/td][td]

PeeNuts​

[/td][/tr]

[tr][td]

4​

[/td][td]

AE008​

[/td][td]

Pecanuss​

[/td][td]

AE002​

[/td][td]

Wallnusse​

[/td][/tr]

[tr][td]

5​

[/td][td]

AE007​

[/td][td]

DgsNuts​

[/td][td]

AE007​

[/td][td]

DgsNuts​

[/td][/tr]

[tr][td]

6​

[/td][td]

AE002​

[/td][td]

Wallnusse​

[/td][td]

AE007​

[/td][td]

hdshg​

[/td][/tr]

[tr][td]

7​

[/td][td]

AE007​

[/td][td]

hdshg​

[/td][td]

AE008​

[/td][td]

Pecanuss​

[/td][/tr]

[tr][td]

8​

[/td][td]

AE009​

[/td][td]

AlansNuts​

[/td][td]

AE009​

[/td][td]

AlansNuts​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]DgsNtsFormula[/td][/tr][/table]

……. I am so very grateful FormR…. ( and thanks on behalf the OPs that may finally now get the waited answers form me !!! (….Later when I have convinced myself I understand it fully…).. and that link…it is a good one,… it is breaking the formula down in a way that I thought only I ever did !!( like when I littered the test area a few months back with pretty colored HTML Maker made formulas and copies of what I got by select portions of formula and pressing the F9 key to see the "insides" of the arrays……)

. I scoured Forums and the internet and never hit that one. I thought it should have been a fairly basic requirement , I did get close a few times.. But …there is just so much info out there and as someone so experienced as Scott also was not aware of it, …..it just emphasizes again how valuable these forums are and how amazingly great and commendable it is that you all give so much time voluntarily giving the priceless worth and benefit of your combined knowledge and experience..
. Thanks again to you and anyone else that looked in.

Alan