I'm trying to sort numbers from an outline, so I want to sort it as 1.1, 1.2, 1.3...1.10, 1.11, and so on. Excel sorts them as 1.1, 1.10, 1.11...1.2, 1.3.
I have tried changing the format to several different types, including Number, Text, Decimal, and cannot get Excel to sort it in the correct order for an outline.
I have a list in rows where I have a ranking formula =COUNT($G$5:$G$81)-(RANK(G5,$G$5:$G$81)+ COUNTIF($G$5:G5,G5)-1)+1 When I sort the rank, ascending. All of the unique numbers sort ascending, but the non-unique numbers sort descending
ex) 1.751
2.52
3.753
418
417
416
Using DataEntry sheet for data.
Trying to rearrange the data to DataFormatedProperly sheet.
So far all I can accomplish is DataFormatedWrong sheet.
Edit: Not sure what happened but file was NOT understandable before. It should be correct now.
Is it possible to make excel sort ascending or descending but from mid way through alphabet and then loop through the alphabet again. for example. If Cell A1 had "A" in, it would sort as normal.
Cell A1 = A
Cell A2 = B
Cell A3 = C
Cell A4 = D
Cell A5 = E
If Cell A1 had "C" in, it would sort from "C" through the alphabet and then loop to the start of the alphabet as shown below.
Cell A1 = C
Cell A2 = D
Cell A3 = E
Cell A4 = A
Cell A5 = B
As of right now these are the steps i do to sort...i click custom sort choose My data has headers and then i select from the drop down list the word FRNAME.
is there any way i can setup a macro to do this for me? i tried recording the macro but it just is recording me choosing the column FRNAME is in. This does not work for me since FRNAME end up being in different columns all the time but will always be in row 1.
I would like to ask if it is possible to sort a range of row? What I mean is, if I sort like Row 1, the entire block of row 1 will move as well? Like if i have column A to F, then row 1 of column A to F will move together at the same time.
View 3 Replies View RelatedI am looking for a macro to sort my colums in ascending order the range i need sorted is from A7:F107, it will be sorted with the numbers in the A column ie 1 - 107. sometimes different numbers get added and it has to be sorted in number sequence.
If possible i would also like a macro to protect this range after the sort,
I have an Overview sheet within my workbook that contains info from all other sheets.
What I want to do is when you click on the Sort button in the General sheet it will activate the Overview Sheet and
- first off sort all sheets starting with AJ together, then all sheets starting with CJ together and then all sheets starting with PJ
- then the next thing i want it to do is sort all Ajs by their start dates, sort all CJs by their start dates and the same for all PJs.
Now up until now I was keeping all sheets that began with AJs together in the workbook and so on so I was able to use the following sort function
Sub Overview_sorting() ...
I am trying to sort a long range of text that is placed horizontally in a spreadsheet. I can do it vertically with the sort function in Excel but it does not seem to work for text that is placed horizontally. Example is as below:
Inventory Accounts Human Resources
I have this massive spreadsheet, with the maximum rows excel will fit on one sheet. In the interest of simplicity, this is what the raw data looks like:
Last First Amount
Jones Jim $1000
Jung Joe $700
White Jon $100
Jones Jim $200
Jones Jan $300
Jung Joe $800
White Jon $200
What I want to do is automatically get excel to group all the same names together. Then I want it to sum all the values for each name, then order by largest total value for each person, then order that by name alphabetically. In other words, the above would look like this after the sort:
Last First Amount
Jung Joe $800
Jung Joe $700
Jones Jim $1000
Jones Jim $200
Jones Jan $300
White Jon $200
White Jon $100
Or, if necessary, there could be sum total rows under each name...although I don't have spare rows, so IDK. I could delete some rows if I had to, but would rather not.
I've located the following color sort VBA that sorts worksheets by color and it is working well. I'm trying to modify the code to sort each of the "color grouped" sheets alphabetically. That is, sort by color and then each color group sorted alphabetically.
VB:
Sub SortWorksheetsByColor(Optional ByVal SortByAsc As Boolean = True)
Dim i As Long
Dim j As Long
Dim ShtC() As Long
Dim ShtN() As String
Dim t, n As Long
Dim lngSU As Long
[Code] .....
I havet he following code which sorts data. If there is no data to sort I keep on getting a run time error. Could I add something to my code to prevent the run-time error, as sometime there won't be any data to sort. The code runs when I switch to the worksheet in question.
Sub SortMeetings()
Dim iCTR As Integer
Dim yCTR As Integer
Dim zCTR As Integer
zCTR = 11
For iCTR = 12 To 23
For yCTR = 1 To 10
If Len(Range("D" & iCTR).Offset(0, yCTR)) 0 Then
Range("AA" & zCTR).Value = Format(Range("D" & iCTR).Offset(0, yCTR), "HH:MM") & " " & Range("D" & iCTR).Value
zCTR = zCTR + 1
End If
Next yCTR
Next iCTR
Range("AA11:AA" & zCTR).Select
Selection.Sort Key1:=Range("AA11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
I would like to sort columns B and C (keeping the two lined up together) so that A and B match up numerically.
View 3 Replies View RelatedI need some code to attach to a button that will sort data in H3:CA770 in descending order, the catch is I need this to sort on by the column to the most right with data in it.
I use rows 1 & 2 as a header so there will always be data in that, so I need the button to sort my worksheet based on the last column with data in it. The sheet will be updated monthly so I dont want to creat 12 different sort buttons.
I have a column of values:
56 WELLINGTON
76 WELLINGTON
65 WELLINGTON
well I'm looking to sort these values so that they will be in order
so
result would be
56 WELLINGTON
65 WELLINGTON
76 WELLINGTON
I would like to copy Sheet1, col C starting from C7 to the last cell of data entered to Sheet2 from D7.
The list of data would change as and when I enter more data.
After Copy I would like the list to be sorted in ascending order.
Page Break after every 44 lines
Next page should begin after 6 lines and same as above , page break after 44 lines
Secondly I would like to mention serial numbers, for eg. in sheet 2 from C7 if data if data is present in D7 then 1 should be mentioned and so on.
I need to sort by cells that are bold. Does anybody know a way I can do this?
View 10 Replies View RelatedI have one column of names that has been entered as "first and last name" as one entry and I would like to sort by the last name (the last word in the column). Some of the entries are simply first and last name (e.g. Jane Doe), some are spouses together (e.g. Frank and Elaine Smith) and some contain middle initials (e.g. John Q. and Amy X. Public).
View 2 Replies View RelatedThere are only 3 columns, A, B, C and only 10 rows but I would like it to sort automaticaly. I enter data on other sheets and it is loaded automaticaly back to sheet 1 after some calculations are made. I would like the data on sheet 1 to sort automaticaly on column C keeping the rows of data intact.
View 14 Replies View RelatedI am using a formula but when it sorts with the data in the adjacent cells it does not update the reference cells properly.
For example here is the =SUMPRODUCT(($F$12:$WWX$12>=C1)*1,($F$12:$WWX$12<=E1)*1,ABS($F$13:$ZZ$13))
This code is located on row 13, when I do a sort function with all the data, this code is then moved to row 30, that is fine but the code changes in the following way:
=SUMPRODUCT(($F$12:$WWX$12>=C18)*1,($F$12:$WWX$12<=E18)*1,ABS($F$13:$ZZ$13))
C1 & E1 should not have changed at all, F13 & ZZ13 should have changed to F30 & ZZ30.
I am not sure if there is a way to fix the formula so it updates correctly, or these cells can stay in the same place as long as everything else sorts.
Is is possible to sort all columns except a few?
In the attached version the "Print Version" tab sorts the values entered into the "Log Entries" tab. When entries are deleted from the "Log Entries" tab they are converted into 'blank' entries and moved to the end in the "Print Version" tab.
However, in earlier versions the "IFERROR" does not appear to work, therefore any blank entries are shown as "#NUM!", which is kind of annoying. If there any way around this? The forumla is shown below. =IFERROR(SMALL('Log Entries'!$A$1:$E$47,ROW('Log Entries'!A1)+COLUMNS('Log Entries'!$A$1:A1)*46-46),"")
I have a table which I am trying to sort by date, however at the end of each of the dates there are 2 spaces represented (I haven't a clue why or how) which means the data doesn't sort correctly. Other than going cell by cell and deleting the 2 spaces can anybody offer any recommendations?
View 2 Replies View RelatedI have a sheet that has some hidden calculations in the top 2 rows. My actual headers start in row 3 (the amount of data always changes so I chose to put the hidden constants at the top rather than the bottom). I want to sort by the headers in row 3 but I can't figure out how.
View 2 Replies View RelatedI have an excel sheet with about 500 words in 500 cells that I have generated automatically. Separately I have a 'blacklist' of words I have compiled manually that I don't want to appear in the excel list.
How do I ensure that any 'blacklisted' word is deleted from the excel files. Is there any other way than doing it manually?
I am trying to sort a document alphabetically but cannot. I have two lines in the doc that previously had headers on them. I was able to remove them, but am now left with a row that has what looks like a backwards L in the last cell. The bottom line is also darker, but there is not a detectable border set. If I choose the rows that include these rows in my sort option, it is disabled. Sorting is available if I do not. I need to sort everything.
View 3 Replies View RelatedI have a worksheet of 10 Columns and over 5000 rows. The worksheet has the word {Group} in brackets after the file name. When I pasted this worksheet into an old file I have been using for a long time it added the word {Group} to this file also.
I am unable to use sort or filter on this worksheet as they are both grayed out.
I am trying to sort a sheet based on the values in a column.
S, SI, M, MI. and it need to be sorted only in this order.
I have two arrays that output as two columns next to each other. Like this:
ID
Responses
1
4
2
3
3
2
4
5
5
1
I would like to sort the responses column and have it show as this:
ID
Responses
4
5
1
4
2
3
3
2
5
1
I am guessing that I need to some how merge them into one array so I can sort them as one entity rather than sorting two columns separately.
Here is my sample data and the macro I use to pull the data i need for the two arrays. sample.xlsm
I've attched my sample file.
The question is how to sort worksheet (VB) - see file for details
I need some help importing some data from one spreadsheet to another. I’m trying to basically have the data in “Harvest” imported into the “Import Here” worksheet. I’m really interested in using the data in columns A, K and M.
What this is, is a staffing program spits out the information in the “Harvest” worksheet (as a payroll report, which it does badly) but only what appears as shaded in grey or white. Anything in that worksheet shaded in green is carried out manually in order to assist importing the same data manually into the “Import Here” worksheet.
What I’d like to do is modify the “Import Here” sheet so it’ll take and process all the data from “Harvest”. Instead of doing the same thing twice I know there has to be a way to do it once but in a familiar format without it being 50 column’s wide.. although that wouldn’t matter if the print area was set correctly in "Import Here".