#1
|
|||
|
|||
Need help sorting data in columns that will sort by number of Xs
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. |
#2
|
||||
|
||||
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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
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. |
#4
|
||||
|
||||
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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
@brenna.at.work
Did you get your workbook to work with any of the solutions offered? |
#9
|
|||
|
|||
Probably a cross poster and got their answer elsewhere!
|
Tags |
data, probz, sorting |
Thread Tools | |
Display Modes | |
|
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 |