Special sort

[YANECKC]

I want to do a special sort where columns has values STOCK RECORD and ISSUES and A. I want to sort the first cell in column A with STOCK RECORD. then next the first time ISSUES appears and then the first time A appears. It would look like
STOCK RECORD
ISSUES
A
STOCK RECORD
ISSUES
A
STOCK RECORD
ISSUES
A
STOCK RECORD
ISSUES
A

Any thoughts on how I can sort like example

Yaneckc

 

[MartyS]

I want to do a special sort where columns has values STOCK RECORD and ISSUES and A. I want to sort the first cell in column A with STOCK RECORD. then next the first time ISSUES appears and then the first time A appears. It would look like
STOCK RECORD
ISSUES
A
STOCK RECORD
ISSUES
A
STOCK RECORD
ISSUES
A
STOCK RECORD
ISSUES
A

Any thoughts on how I can sort like example

Yaneckc

If you're happy to create another column to use as the SORT ORDER...


Excel 2010

	A	B
stock record	1A
a	1C
issues	1B
issues	2B
a	2C
stock record	2A
issues	3B
stock record	3A
a	3C
a	4C
issues	4B
stock record	4A

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=IF(TRIM(A1)="STOCK RECORD",COUNTIF(INDIRECT("$A$1:$A$"&ROW()),"STOCK RECORD")&"A",IF(TRIM(A1)="ISSUES",COUNTIF(INDIRECT("$A$1:$A$"&ROW()),"ISSUES")&"B",IF(TRIM(A1)="A",COUNTIF(INDIRECT("$A$1:$A$"&ROW()),"A")&"C","")))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "][/TH]
[TD="align: left"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

You can then sort on column

 

[YANECKC]

There are 11 columns and the first one coloumn has no header name.. ACCOUNT NUMBER - 10 ADP SECURITY CUSIP SETTL DATE QTY DESC TXT DISPLAY Variable/Fixed Rate INTEREST RATE MATURITY DATE Proctor Notes COA-OFFICE_CD.
The problem is the the account number column and adp security column has to be part of the sort . The first acct in my table is "0000000109"

Yaneckc

 

[MartyS]

There are 11 columns and the first one coloumn has no header name.. ACCOUNT NUMBER - 10 ADP SECURITY CUSIP SETTL DATE QTY DESC TXT DISPLAY Variable/Fixed Rate INTEREST RATE MATURITY DATE Proctor Notes COA-OFFICE_CD.
The problem is the the account number column and adp security column has to be part of the sort . The first acct in my table is "0000000109"

Yaneckc

Could you provide a clear example of what you're looking for - perhaps some anonymised data that is representative/demonstrative of the patterns involved, and what you'd like the final output to be?

 

[YANECKC]

Instead of special sort , Can I have a macro that insert 2 blanks rows if column B is not BLANK(NULL). The first blank row needs the word "ISSUES" in column A and next row is completely blank.
the excel file would look like

Vault Positions extracted form Stock Record
- ACCOUNT -ADPsecurity-CUSIP- -SETTQTY-DISCRIPTION-VARFIX-INTEREST-MATURITY -PROCTOR

STOCK RECORD-0000000109-B427050 -097373107--31.00 -BOISE CASA -VAR - 0.0 -00/00/0000-TEST DATA
ISSUES - - - - - - - - -
- - - - - - - - -
STOCK RECORD-0000000109-C000059 -216648301- 58.00 -COOPER CPY - FIX - 1.6 -00/00/0000- APPLE DATA
ISSUES - - - - - - - - -
- - - - - - - - -
STOCK RECORD-0000000182-9L50878 - - 99.00 -PACIFIC CO - FIX - 5.6 -00/00/0000- BERN DATA
ISSUES - - - - - - - - -
- - - - - - - - -

YANECKC

 

[MartyS]

Instead of special sort , Can I have a macro that insert 2 blanks rows if column B is not BLANK(NULL). The first blank row needs the word "ISSUES" in column A and next row is completely blank.

Yes!


NB: Please make sure you change the first two lines (CONSTS statements) to the correct sheet name and start row.

DataStartRow = 2 assumes Row 1 contains headers (and not data!)

Sub AddTwoRows()


    Const MyWorkSheetName = "Sheet2"
    Const DataStartRow = 2
    
    
    Dim EndRow As Integer, Cntr As Integer


    Application.ScreenUpdating = False


    With Sheets(MyWorkSheetName)


        'Get the max number of rows in the sheet by looking in column A for last piece of data.
        EndRow = .Range("A:A").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        
        'Work from the bottom up
        For Cntr = EndRow To DataStartRow Step -1
                    
            'Check to see if cell in Column B is blank
            If Len(Trim(.Range("B" & Cntr))) > 0 Then
                    
                'Insert two blank rows
                .Range(Cntr + 1 & ":" & Cntr + 2).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            
                'If it is NOT blank, add word "ISSUES"
                .Range("A" & Cntr + 1).Value = "ISSUES"
                
            End If
                
        Next Cntr


    End With


    Application.ScreenUpdating = True
    
    MsgBox "Task Complete", vbOKOnly, "AddTwoRows()"


End Sub

Excel 2010

	A	B	C
LABEL	ACCOUNT	ADPsecurity
STOCK RECORD	B427050
STOCK RECORD	C000059
STOCK RECORD	9L50878
STOCK RECORD	B427050
STOCK RECORD	B427050
STOCK RECORD	C000059
STOCK RECORD	9L50878
STOCK RECORD	B427050
STOCK RECORD	C000059

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]109[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]182[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]109[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]182[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]109[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]109[/TD]

</tbody>

​

becomes

Excel 2010

	A	B	C
LABEL	ACCOUNT	ADPsecurity
STOCK RECORD	B427050
ISSUES
STOCK RECORD	C000059
STOCK RECORD	9L50878
ISSUES
STOCK RECORD	B427050
ISSUES
STOCK RECORD	B427050
STOCK RECORD	C000059
ISSUES
STOCK RECORD	9L50878
ISSUES
STOCK RECORD	B427050
STOCK RECORD	C000059
ISSUES

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]109[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]182[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]109[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]182[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]109[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]109[/TD]

[TD="align: center"]21[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>