Need help sorting data in columns that will sort by number of Xs

macropod · #1 04-06-2014, 03:18 AM

Hi Kevin,

Interesting approach but, given the OP's 'real' workbook as 100+ columns of data, doubling that number might make the workbook rather unwieldy. Plus it still doesn't sort the data - it just gives a ranked report of them. It that were all that's required, I'd be inclined to use a separate worksheet and arrange the data in a single column.

Kevin@Radstock · #2 04-06-2014, 05:26 AM

Hi macropod

Sorry, misread the post and missed the other sheet with their example!!

Attached another version, I will have another go in the week, to see if I can shorten the formulas.

jolivanes · #3 04-06-2014, 02:02 PM

Or try this (on a copy of your workbook)
When you copy it into another workbook, change the Sheet references as required.

Code:

 
Sub Try_This()
    Dim lc As Long, lr As Long, i As Long, c As Range
    lc = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    lr = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Application.ScreenUpdating = False
    With Range(Cells(1, 1), Cells(lr, lc))
        .SpecialCells(4).Delete -4162
    End With
    For i = 1 To lc
        Cells(i, lc + 1).Value = ActiveSheet.Cells(Rows.Count, i).End(xlUp).Row
        Cells(i, lc + 2).Value = i
    Next i
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range(Cells(1, lc + 1), Cells(lc, lc + 1)), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range(Cells(1, lc + 1), Cells(lc, lc + 2))
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    i = 1
    For Each c In Range(Cells(1, lc + 2), Cells(1, (lc + 2)).End(xlDown))
        Range(Cells(1, c.Value), Cells(1, c.Value).End(xlDown)).Copy Cells(lr + 2, i)
        i = i + 1
    Next c
    Rows("1:" & lr + 1).EntireRow.Delete
    Application.ScreenUpdating = True
End Sub

jolivanes · #4 04-07-2014, 03:30 PM

This is better.
Make sure you save your workbook as macro enabled (.xlsm) after copying the code into it,

Code:

 
Sub Try_This_A()
    Dim lc As Long, lr As Long
    lc = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    lr = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Application.ScreenUpdating = False
    With Range(Cells(1, 1), Cells(lr, lc))
        .SpecialCells(4).Delete -4162
    End With
    Rows("1:1").Insert Shift:=xlDown
    Range(Cells(1, 1), Cells(1, lc)).FormulaR1C1 = "=COUNTA(R[2]C:R[15]C)"
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range(Cells(1, 1), Cells(1, lc)), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range(Cells(1, 1), Cells(lr + 1, lc))
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlPinYin
        .Apply
    End With
    Rows("1:1").Delete
    Application.ScreenUpdating = False
End Sub

jolivanes · #5 04-11-2014, 09:01 PM

@brenna.at.work
Did you get your workbook to work with any of the solutions offered?

Kevin@Radstock · #6 04-12-2014, 07:00 AM

Quote:

Originally Posted by jolivanes

@brenna.at.work
Did you get your workbook to work with any of the solutions offered?

Probably a cross poster and got their answer elsewhere!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Cannot Convert Text Cell to number format to be able to sort the data	jyfuller	Excel	10	06-19-2013 05:31 PM
How to sort table having three columns?	Bahir Barak	Word	2	01-20-2011 01:52 PM
Can I do this? sorting data in seperate columns	shumonsaha	Excel	0	07-04-2010 03:05 AM
CAUTION!! Sorting a spreadsheet with hidden columns will trash your data.	psmaster@earthlink.net	Excel	0	11-24-2009 11:54 AM
Sorting columns in Excel - please advise	Jonre	Excel	2	08-21-2009 02:38 AM