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

brenna.at.work · #1 04-03-2014, 09:29 AM

Good morning and thanks for clicking!

I have a workbook with 100+ columns that are headed by a person's name. Below, there are Xs that mark whether or not that particular person answered a polling question during a training session. Each X is on its own row and there can be up to 16 Xs under each named column.
What I want to do is sort this data to show me how many each person answered. I want the people with the most Xs on the left and then the people with the least on the right. I don't need Excel to tell me a numerical value of each person's earned Xs but it is okay if it does.

Please reply back if I can offer any further clarification. I've attached a sample workbook to make my request a little more clear. I have two tabs on the sample worksheet - one with what I have and one with what I want. I did the "What I Want" tab by hand but I would not be able to do that with the type of large group that I will actually be working with.
I really appreciate any and all help. Have a great day everyone!

FYI: I'm using Excel 2007 and working on a PC.

macropod · #2 04-05-2014, 08:49 PM

What you need to make this work is an otherwise-unused row to hold the X-count for each person. If you make that row 1, you can tally the Xs for each person with a formula like:
=COUNTIF(A2:A100,"X")
copied across as far as needed. You can then do a highest-to-lowest, left-to-right sort based on row 1 as the criterion.

Kevin@Radstock · #3 04-06-2014, 01:51 AM

Perhaps something along these lines using a helper row. In this case I have the helper row in J3:P3 and formatted as ";;;" (Without the double quotes) to hide the helper row.

The formula in J1 is an array formula and copied across.

macropod · #4 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 · #5 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 · #6 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 · #7 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 · #8 04-11-2014, 09:01 PM

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

Kevin@Radstock · #9 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!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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