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.

 

[Caribeiro77]

If your data is in this range (if not the range part can be adapted) the name of the sheet may have to be changed to in this code sheet3 may have to be changed to your sheet name...

Sub Copyandfilter()


    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("D2").Select
    ActiveSheet.Paste
    Range("D2").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Add Key:=Range("D2"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet3").Sort
        .SetRange Range("D2:E10")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Columns("C:C").Select
    Selection.Delete Shift:=xlToLeft
    Range("A2").Select
End Sub

 

[DocAElstein]

If your data is in this range (if not the range part can be adapted) the name of the sheet may have to be changed to in this code sheet3 may have to be changed to your sheet name...

........

Thanks cabibeiro,
. Greatly appreciate your efforts

[color=blue]Sub[/color] Copyandfilter()
 
 
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("D2").Select
    ActiveSheet.Paste
    Range("D2").Select
    Application.CutCopyMode = [color=blue]False[/color]
    ActiveWorkbook.Worksheets("DgsNtsFormula").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("DgsNtsFormula").Sort.SortFields.Add Key:=Range("D2"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    [color=blue]With[/color] ActiveWorkbook.Worksheets("DgsNtsFormula").Sort
        .SetRange Range("D2:E10")
        .Header = xlNo
        .MatchCase = [color=blue]False[/color]
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    [color=blue]End[/color] [color=blue]With[/color]
    Columns("C:C").Select
    Selection.Delete Shift:=xlToLeft
    Range("A2").Select
[color=blue]End[/color] [color=blue]Sub[/color]

….



Greatly code, tried it … it works. -- ( But I do have some similar myself.. as I mentioned, Post #1:

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

. 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...
Many Thanks
Alan.

…

…. ) so I am really in this case needing a Formula along the lines I asked for.

. But I am very grateful for Yous taking the time to reply.

Alan

 

[Scott Huish]

FYI, there is usually no need to Select in VBA you can work with ranges directly.

So, instead of doing stuff like this:

Range("A2").Select
Selection.Copy

just copy the range:

Range("A2").Copy

there is also a destination parameter so you can do it all in one step:

Range("A2").Copy Range("D2")

 

[Caribeiro77]

Scott please don't punish me......I'm just a VBA begginer....
Thanks for the tips....

 

[DocAElstein]

Hi Again Scott

FYI, …..
there is also a destination parameter so you can do it all in one step:

Range("A2").Copy Range("D2")

.. There can be some issues there….
http://www.mrexcel.com/forum/excel-...ypevisible-copy-only-values-not-formulas.html

Post # 25
http://www.mrexcel.com/forum/excel-...-value-paste-worksheet-same-name-value-3.html

From Post # 32
http://www.mrexcel.com/forum/excel-...le-worksheets-based-column-4.html#post4039255

… but agreed the Destination would be adequate here, initially at least for my short demonstration example, ( I greatly simplified it for clarity in my request for help for a Formula solution )

…. And following on

FYI, there is usually no need to Select in VBA you can work with ranges directly.
…….

. I Expect the start point form Claudio’s code was a macro recording. It would explain all the selection stuff.. and also as he is using XL 2013 his code ties up with that from a macro recoeding… In particular…… after 2003 there was, I believe, all the new stuff to add lots of sort fields, needing then clearing and the like. … so you have to add all the sort fields, do initializing, setting up… etc... - All of this which the macro „guesses Yous want. But he was trying to help, so I am very grateful for that. FWIW, or rather to wrap up maybe the digression here, if I did want a VBA code , then I would probably stick with a simple XL 2003 compatible version, . The 3 “keys” or (“Fields” or “columns” or wotever) are adequate and the command sequence is simple, something like this … (working on my original data , Post # 1 ) ……

SHimpfGlified Monochrome version:

Sub CopyandfilterSHimpfGlified2003()
Set Rng = Range("A1").CurrentRegion
Let[/color lr = Rng.Rows.Count
Rng.Copy Destination:=Range("C1")
Rng.Offset(0, 2).Resize(lr, 2).Sort key1:=Rng.Offset(0, 2), Key2:=Rng.Offset(0, 3), Header:=xlYes    End Sub

Or Full with comments etc..

[color=blue]Sub[/color] Copyandfilter2003()
[color=blue]Dim[/color] ws [color=blue]As[/color] Worksheet: [color=blue]Set[/color] ws = ThisWorkbook.Worksheets("DgsNtsFormula")
[color=blue]Dim[/color] rngOrg [color=blue]As[/color] Range: [color=blue]Set[/color] rngOrg = ws.Range("A1").CurrentRegion [color=lightgreen]'Select current Table in which A1 is in[/color]
ws.Activate: ws.Range("A1").Select [color=lightgreen]'Not allways necersarry, but Occasionally a good idea before a code runs to acivate, and select the sheet of interest..[/color]
 
[color=lightgreen]'Copy original range to output Range , by copying original and pasting it offset 2 columns to the right[/color]
rngOrg.Copy [color=lightgreen]'Copy range to clipboard' could now "Destination:= it, but following line is better to controll format:[/color]
rngOrg.Offset(0, 2).PasteSpecial Paste:=xlPasteAllUsingSourceTheme [color=lightgreen]'Being very Explicit here with an extra line enabling us to Paste Special with arguments to make sure the correct version from Clipboard is copied[/color]
[color=blue]Dim[/color] rngSorted [color=blue]As[/color] Range: [color=blue]Set[/color] rngSorted = rngOrg.Offset(0, 2) [color=lightgreen]'The offsett range will become the Sort - ed Range (No need to wory about header- there is sort argument to specify that)[/color]
 
[color=lightgreen]'Slice out columns from offset Output range for use in sorting (These will be the "keys:= for sorting[/color]
[color=blue]Dim[/color] ClmC [color=blue]As[/color] Range, ClmD [color=blue]As[/color] Range [color=lightgreen]'Part No, Description[/color]
[color=blue]Set[/color] ClmC = Application.WorksheetFunction.Index(rngOrg.Offset(0, 2), 0, 1) [color=lightgreen]' >> Index Function with second argument (column co - ordinate) set to 0 will return the entire column given by third argument ( column - co ordinate ), applied to the first argument which is the grid, ( Array , Row_Number, Column_Number)[/color]
[color=blue]Set[/color] ClmD = Application.WorksheetFunction.Index(rngOrg.Offset(0, 2), 0, 2) [color=lightgreen]' >> Index Function with secon argument (column co - ordinate) set to 0 will return the entire column given by third argument ( column - co ordinate ), applied to the first argument which is the grid, ( Array , Row_Number, Column_Number)[/color]
  
[color=lightgreen]'Sort Offset copied range:-[/color]
rngSorted.Sort key1:=ClmC, Key2:=ClmD, Header:=xlYes [color=lightgreen]' Sort first by column C then by column D[/color]
 
[color=blue]End[/color] [color=blue]Sub[/color] [color=lightgreen]'Copyandfilter2003()[/color]

. Thanks again for all the input but back to my ( one of them !!!) problem………

Bumpety…….

 

[DocAElstein]

Hi..........
. Thanks again for all the input but back to my ( one of them !!!) problem………

Bumpety…….

....
Bump ...... Bump



Hi, I Bump very rarely, not wanting to nerve the people so kind as to help here…..

….. but the well meant but slightly distracting responses have wiped out my chances of someone picking it up as a zero reply thread despite that my request for a Formula as not answered …

.. So, again if anyone could help I am still desperately seeking help with a Formula solution , along the lines asked for in Post # 1

….. If anyone can help , note please the request at the end of Post # 1 - I would be very grateful if someone offering a Formula could “open it up ) and explain it. This is not just for my learning benefit but helps me to help the OPs on the Threads I am trying to answer

Many thanks again, also again to the other contributions..

Alan

 

[Scott Huish]

I am aware that the .Select comes from the macro recorder, not trying to punish anybody ???

I mention it, and so do a lot of other people, because a lot of people learn VBA from the macro recorder and don't realize they don't have to write the incredibly verbose code like the macro recorder does. I am just trying to help.

But I have read your problem, but I don't know how you would make a sorted copy of your data via formula.

 

[FormR]

Check here for the source and explanation of the C2 formula:

Sorting text cells using array formula in excel | Get Digital Help - Microsoft Excel resource

Excel 2012
	A	B	C	D
1	Part No.	Description.
2	AE001	Cashews	AE001	Cashews
3	AE001	PeeNuts	AE001	PeeNuts
4	AE008	Pecanuss	AE002	Wallnusse
5	AE007	DgsNuts	AE007	DgsNuts
6	AE002	Wallnusse	AE007	hdshg
7	AE007	hdshg	AE008	Pecanuss
8	AE009	AlansNuts	AE009	AlansNuts
9	AE012	PeeNuts	AE011	MixedNuts
10	AE011	MixedNuts	AE012	PeeNuts
Sheet1

Cell Formulas
Range	Formula
C2	{=INDEX($A$2:$A$10, MATCH(SMALL(COUNTIF($A$2:$A$10, "<"&$A$2:$A$10), ROWS($C$1:C1)), COUNTIF($A$2:$A$10, "<"&$A$2:$A$10), 0))}
D2	{=INDEX($B$2:$B$10,SMALL(IF($A$2:$A$10=C2,ROW($A$2:$A$10)-ROW($A$2)+1),COUNTIF($C$2:C2,C2)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[DocAElstein]

… not trying to punish anybody ???.... I am just trying to help.
………

….. The efforts from people like yous is amazing and invaluable, .....in my opinion probably more than you probably realise.. our existence on this earth relies on it!!!
Post #44
http://www.mrexcel.com/forum/lounge-v-2-0/814398-how-important-excel-forums-5.html

… I think Claudio is like me, amazingly grateful for the help from people like Yous that tirelessly help so much. And you help so many people. Amazing. I try to give a bit back, but I am astounded how many Threads you and other’s answer per Day.
. It is inevitable that you very occasionally skip / miss a reply and so come in slightly from the wrong angle. So I lost my chance of a getting a zero reply response to my unanswered question. Indeed that is what Claudio did by answering here in post # 2 . I see he is trying very hard to help many people, so again he occasionally misses the point , as was the case here.
. Bottom line is that I am just so amazingly grateful for this Board and the help I receive. I sometimes get a bit put off by people that always just ask, - or the endless “one post new member” that appear , get a good answer, then vanish… coming back with a new made up Email and name to get a better “New member chance”, or disguise their ungrateful response in “their” last Thread…… But again that is covered elsewhere..
Post #41
http://www.mrexcel.com/forum/lounge-v-2-0/814398-how-important-excel-forums-5.html
….. I don’t want to get ( rightly ) warned for digressing again

…. Also there are wot I call the “twitchy chatter’s” that seem to be on a quest to get there replies made up – they rarely answer fully a question if at all, and again wipe out a valid questioner getting their “zero reply chance” (I Definitely DO NOT think you or Claudio are that sort)

. So many thanks again Scott. It has all turned out great anyway with this problem……. See next post..