The Data that I have is Grades:
Gade 1/Class A
Grade 2/Class B
Grade 3
Etc..
I wanted to use if function, but I couldn't as there's a limit on the characters that can be used in "IF Function", but unfortunately, I can't because I exceeded the limit, as I have too many grades to include in "Logical_Test",
What I am looking for if one of this data was found write 45 Days, if other grades that is not listed was found write 30 Days.
I am trying to write a code that will classify based on criteria and color a cell. The code is below and it runs, but does not give the desired results that are in column K of the attached spreadsheet.
[Code] ....
TestBook.xlsm
I would like to classify the chemical data into 16 groups based on their value. how to construct a formula to do the job?
My data look like this:
A B C D E F G
AsSbAgNiGroupcondition
0.10.50.080.6 14 1If As <0.1, Sb<0.1 , Ag<0.1 , Ni<0.1
2If As >=0.1, Sb<0.1 , Ag<0.1 , Ni<0.1
3If As <0.1, Sb>=0.1 , Ag<0.1 , Ni<0.1
4If As <0.1, Sb<0.1 , Ag>=0.1 , Ni<0.1
5If As <0.1, Sb<0.1 , Ag<0.1 , Ni>=0.1
6If As >=0.1, Sb>=0.1 , Ag<0.1 , Ni<0.1
[code]....
I need to classify a number into range
e.g.
D13 = 5101
if D13 range is 1 to 1000, write at D23 1000.
if D13 range is 1001 to 5000, write at D23 5000.
if D13 range is 5001 to 10000, write at D23 10000.
and so on until it reach 100,000
I have been trying to work with to get my stock table classified into 3 different stock statuses and it comes out, it does not work I have attached the table as it is, the formulae I wanted to use and the end result I would like to have.
View 7 Replies View RelatedI have 5 columns of numbers and at the bottom of each column I want to list every number in order from least to greatest and how many times it occurred in the column.
I'll just provide you with 5 rows of numbers so I don't waste a ton of space.
0628313841
0814364045
0203193042
0105101518
2021233335
I have data in table which look like this
A
B
1
NAME
AV
2
SONA
18
[Code] .........
I Want a formula that will enable excel to give me the number of students with Av.
>= 18
>= 15 but not > than 18
>= 10 but not > 15
I have some code which copies down a formula in a range of data. After this I want it to sort the data by this column in question. However my data range will differ in rows all the time and if I record the sort through the macro method it saves the VB code as a fixed range i.e:
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A1967" _
), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
The formula code beforehand does acquire the last row number if that's of any help:
Dim rng As Range
Set rng = Range(Cells(ActiveCell.Row, 1), [A50000].End(xlUp)).Offset(0, 1)
rng.FormulaR1C1 = "=IF(RIGHT(RC[-1],2)=""XP"",""5"","""")"
In a column I have loads of numbers like this except there are 5000 of them.
3
2
0
0
0
0
2
0
3
0
0
2
0
9
how could i sort it into another column so it stays in the same order but counts the 0 values so it would look something like this.
3
2
0 (x4)
2
0 (x1)
3
0 (x2)
2
0 (x1)
9
I would like to have the duplicate numbers in position one ( cell D9:D18), position two (cell E9:E18) and position three (cell F9:18) all counted and then ranked based upon date range in column A.
The number order of precendence is 0-9 with zero being the lowest.
Column (I9:I18) counts the duplicates, draws and no draws of digits 0-9. Column (J9:J18) converts results from column I9:18 into a percent.
I need to add a sort button at the bottom of position 1 (columns H & I 22), position 2 (columns L & M 22) and position 3 (columns P & Q 22)
When the sort button is selected it will sort the data by # of repeats, draws, and no draws as illustrated in (columns I, M & Q) or sort data by Position number from 0-9 as illustrated in (columns H) which will then show data of repeats and percentages out of rank order, because it is in order of number precedence.
Last but not least a graphic bar chart that shows the results ranked highest to lowest from when the sort button is selected with the number at the top of the bar colored black. Bar graph colors to be: top 3 red, middle 4 yellow and bottom 3 blue. This should show a vertical cascade effect.
Ive written the macro below to sort Column A according to the length of the cell values in Ascending order. Ive done this by writing the length to Column B for each value and then sorting on column B. Surely there is a more elegant method, perhaps using arrays.
Sub SortIt()
Range("A1").Select
Do Until IsEmpty(ActiveCell)
ActiveCell.Offset(0, 1).Value = Len(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
Columns("A:B").Sort Key1:=Range("B1"), Order1:=xlAscending
End Sub
I have provided an attachment. what I am trying to accomplish. I am trying to have a worksheet that if I input multiple 3 number combinations into the input cell range, after pressing the sort button, it would then sort, rank and count each 3 number combination for me. So as my attached file illustrates, the input cells would be A9:D14. In this sample the ranking consists of cells A19 - A31 as the ranking columns. Cells F19 - F31 show the counted and sorted results and are ranked accordingly. I need a sort button as illustrated in cell F10 to make the worksheet function after the 3number combinations are inputted in cells A9:D14. How do I get started to make this work? I do not know VBA codes or macros so I will need guidance along the way if this is what is needed. I do have some working knowledge of formulas (e.g. countif, rank, etc.)
View 8 Replies View RelatedI 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 Relatedsee my attached sheet cotaining the following questions. in a day report sheet how should i count request matching the crateria of date and other conditions. in a monthly report a heavy conditional sum calculation which make slower sheets how can i make it faster.
View 2 Replies View RelatedI need to count the number of equal cells in col D beginning at the top of the column. The counted cells must begin with a text prefix of "Category:" without the quotes.
Some but not all of the cells in col D begin with a prefix of "Category:" without the quotes, followed by a word or words following the word "Category:" See examples below. All of the terms prefixed with "Category:" in col D are in alphabetical order. I need to count the number of identical cells in col D with the "Category:" prefix.
Examples of the contents of cells in col D with the "Category:" prefix are as follows:
Category: Adversity
Category: Answers
Category: Assurance
Category: Blessings
Category: Build
Category: Change
Category: Children
Category: Choices
Cells above and below cells with a prefix of "Category:" in col D are not adjacent.Cells above and below cells with a prefix of "Category:" in col D are separated by 3 to an undermined number of rows.
I need to count the number of equal cells in col D and insert the count in col A at the last equal term. For example, col A above would have 93, 1, 1, 5, 10, 8, 3, and 12 inserted into col A.
I want is a field (e.g Large Parts Used) where I can enter in a number, then basically this number is subtracted from current stock field for Large Parts so I get an updated field of current stock on hand.
But what I want to do is once I've entered the number in the Large Parts used field, I can then clear that field but have the corresponding Current stock field to maintain what was last enetered.
E.g
Large Parts Current Stock = 50
(enter in) Large Parts Used = 2
Large Parts Current Stock = 48
(Clear field where 2 was entered into Large Parts used)
(Field still stays at Large Parts Current Stock = 48 although field where 2 was entered was cleared, so need it to save the information so can continually clear and re-enter amounts and have the stock continue to reduce)
going down are stores a, b, c, d.... what i'm filing in across is the square feet of each store and what quartr or year each store came into place. so there will either be a 0 or a number Now, I want to be able to count the number of nhew stores each quarter. how do i create a formula that just recognizes it the first time there is a number and not a zero... because i will put the square feet in subsequent quarters after it opens so i can see yearly how many square feet the store had. then also, how can create a button on the page that will say quarterly numbers and a button that is annual. so that i can hide the quarterly columns and just see an annual spreadsheet... and for the quarterly button so i can hide the annuals and just see the quarters....
View 6 Replies View RelatedColumn A has current building, column b has future building. Would like to count the number of changes without adding a separate column with an if statement.
View 3 Replies View RelatedI am trying to come up with a formula that will count everything excluding 1 in one row, while looking at another row to determine the group.
The attached example explains things a lot better.
I am going to have 2 formulas. 1 for the "Big" group and one for the "Small" The formula needs to look first at the column that has the group in it. Then it needs to count everything is column A excluding "Snake" And return the value.