I would like to create a formula that will sort the numbers in column B, there are also equal numbers in this column
The difficult part is that there is text in column A, and is linked to the numbers
So if numbers are sorted the names should be sorted in the same way as well.
I have a list of football fixtures that I want to pace in date order. The fixtures are however linked to a grid which has the following formula =IF(FIXTURES!C1="","",FIXTURES!C1 )
so that a zero doesn't appear until a result is actually added. Unfortunately when you sort the fixtures into date order the grid still looks at the original square, NOT the moved one appertaining to the fixture.
I am looking for a VBA to sort rows which include actual numbers and text representing decomposed CTQs (or procedures in IT development)
Code:
Col A Col B
1Billing Accuracy
2Billing Time
3Credit Check Accuracy
4Credit Check Time
2.1Bill preparation
[Code]....
This is the order in which the data is copied and saved from worksheets in which they are developed. Note that 3 rows (8.1.2.1 through 8.1.2.3) are below 8.1.3 (because the three come from Worksheet 8.1.2 which came after worksheet 8.1). The first four rows came from a Top Level Worksheet. I would like to see them intermixed but in proper order.
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
I have a column of data that contains alphanumeric ID codes, some of which contain no alpha characters (i.e., Excel treats them as numbers). When I do a data sort, Excel puts all the purely numeric codes first, then the alphanumeric codes. I want to sort the numbers among the alpha codes.
Example Excel sort order:
06090
10400
28198
34078
43321
0BKV9
1C7K4
2P776
3BTW3
4U744
Desired sort order:
06090
0BKV9
10400
1C7K4
28198
2P776
34078
3BTW3
43321
4U744
I know there's a way to do this, but I've spent half an hour looking and can't find the answer. And no, manually editing each numeric entry with an apostrophe is not an option.
Take for example ABC10 in Cell A1, ABC199 in cell A2 and ABC9 in cell A3
How am i able to sort such that ABC9 appears in cell A1 followed by ABC10 in cell A2 and ABC199 in cell A3?
I've got data in a table that has am induvidual "job number" assigned to each row, but this job number could have a variation such as 1000A 1000B 1000C but they may not necessarily be one after the other in the table (other job numbers may appear in between) so i need a to macro to remove all job numbers lets say that start with 1000 and paste them and the end of the table. so i end up with consecutive numbers. Can this be done? I've played around with the find funciton to loop through the column and find all relating job numbers then cut the entire row but cant quite seem to get it to work
View 5 Replies View RelatedI have data in column C of rows 25 to 45. The data varies in number form.
What i would like to do is have some VBA code that i can place into a macro which will sort the numbers in column C of rows 25 to 45 putting the highest number at the the top (C25). But i need to move the complete row.
In a spreadsheet I have a text column with entries such as:
2012
10 Times
10.5 Times
101 Times
25 Times
A Pure Text Entry
When I sort by this column I expect to get:
10 Times
10.5 Times
101 Times
2012
25 Times
A Pure Text Entry
Instead I get the 1st order. Why? I've selected 'Sort numbers stored as text' separately so it shouldn't be treating 2012 as a number. Besides I've checked & the cell formatting is Text.
I have a list of files in folders.
When I make the index of those folder, i have an excel list with in column A these numbers
1.1.1.1 (so the file 1 in the 1st subsubfolder in the 1st subfolder in the first folder)
1.1.1
1.2.1.1
1.2.2
1.3.1
etc... The list goes on with 1000s rows.
And in column B I have the names of the files
Unfortunately, when i sort the spreadsheet with the number on column A, the order is not good....
So I would like to find a solution to sort things in the good order.
I have a data set that shows numbers for categories over time. I would like to add the data label to the numeric value and then sort the data by the original number. Is this possible?
Here's an example
A 1 2 3 4
B 5 7 4 1
C 8 0 2 1
D 3 4 9 5
I would like each column sorted independently based on the data in that column, but I want the results to have the label from column 1 concatenated with the value in the other columns such as this:
A1 C0 C2 B1
D3 A2 A3 C1
B5 D4 B4 A4
C8 B7 D9 D5
Each column is sorted by the numeric value, but has the name label from column 1 attached to it.
I have three columns of values that are in no particular order that need to be sorted in ascending order in another column.
View 9 Replies View RelatedI have 3 columns of data: col. A = name (random order), col. B = Net #, Col. C = Gross #.
I am using =LARGE(C$1:C$4466,ROWS($D$1:D1) to Automatically sort col C in decending order.
I would Like to do another decending sort but only the values in Col C that corespond to a particular name in Col A. Can I imbed a index match function combination within the large function to do this?
Name
Net
Gross
All Sort
SortA
SortB
SortC
SortD
A
508
-200.129
101.685
[Code] ..........
I am attempting to sort a column with mixed letters and numbers. However, I have been totally unable to get them to sort the way I want them.
What I have is:
K600
K2
K2A
K2B
K159
K159A
K159B
K1
K1A
K1B
K428
K8
K8A
K8B
etc, etc. The prefix letter is only a K - no other letters. However, the suffix letters can be anything from A to E (at the present time)
I need to sort them numberically from K1 in descending order ie:
K1
K1A
K1B
K2
K3
K4
K4A
K4B
etc etc etc
Have a spreadsheet that contains a column of 3 digit numbers as well as 3 digit numbers with 2 trailing alpha characters.
Example:
376
377
421
376AB
376XY
377NC
421GQ
421EF
Need to sort by this column, but, with the parameter of sorting first by the numeric only, and then by numeric with alphas. So, the above list would look like this sorted properly:
376
376AB
376XY
377
377NC
421
421EF
421GQ
I'm creating an excel sheet that is linked to several others. Basically my salesmen will enter their new prospects into their own sales sheets and that will transfer via a link to my main one. The problem I'm having is mainly with my sort.
When the information is transferred, I need the sheet to auto sort the Company names alphabetically and move any blank cells to the bottom.
I'm using links to pull our sales prospects from our salesmen's individual sheets into one master sheet for reference. Once the prospect information arrives in the sheet, I need it to sort alphabetically by customer name so that we can see which customers have been visited and don't visit the same customer twice.
So far in VB, I've been working on this
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A5:L" & Cells(Rows.Count, "L").End(x1Down).Row).Sort Key1:=Range("B6"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
But it's not sorting the way that I need to. All I really need is for the sheet to sort automatically by Company Name and for all of the 0's to go to the bottom. They are 'blank' data that will eventually be imported from the salesmen's individual sheets as they encounter new customers.
I have a column (B) of randomly generated numbers 1-14, and am using this formula range to sort in descending order and return the relevant value from column (c).
=VLOOKUP(LARGE(B3:B20,1),B3:C20,2,) to =VLOOKUP(LARGE(B3:B20,14),B3:C20,2,)
Works great, except when a number is duplicated, (E.G. 14,13,13,12,11,10,10,9,8 etc). It then returns the first value from(C) repeated, and not the value from the second and subsequent duplicated reference numbers.
I'm working on a spreadsheet with about 400 rows and 10 columns. All the numbers in the columns are formatted as text, which doesn't allow me to use AutoSum. I have selected the cells and formatted them as number with no decimals, but AutoSum still doesn't work because the formatting hasn't changed and they're still formatted as text. I know if I double click each cell, the cell changes the format from text to number, but that will take a lot of time to change 400 cells in each of the 10 columns. What can I do to quickly reformat the cells from text to number?
View 1 Replies View RelatedI was using the formula below and it returns a count of how many times a salesman is quoted. Now I am looking something very similar. This time though I am looking to total the price of the second column instead of just counting how many times there is something in the column.
SUMPRODUCT(--(Active!$D$4:$D$200=$A35),--(TEXT(Active!$C$4:$C$200,"mmm")=E$32))+SUMPRODUCT(--(Cold!$D$4:$D$200=$A35),--(TEXT(Cold!$C$4:$C$200,"mmm")=E$32))+SUMPRODUCT(--(Wins!$D$4:$D$200=$A35),--(TEXT(Wins!$C$4:$C$200,"mmm")=E$32))+SUMPRODUCT(--(Sold!$D$4:$D$200=$A35),--(TEXT(Sold!$C$4:$C$200,"mmm")=E$32))+SUMPRODUCT(--(Lost!$D$4:$D$200=$A35),--(TEXT(Lost!$C$4:$C$200,"mmm")=E$32))+SUMPRODUCT(--(Dead!$D$4:$D$200=$A35),--(TEXT(Dead!$C$4:$C$200,"mmm")=E$32))
I have found two macros to do the job that I want. I would like them put into one macros What I want is to text to column and sort my data. Below are the two macros
[Code] .....
I have a list of names in 1 column and I would like to be able to find out what name is most frequent and I'd also like to know how many times that name appears in that column.
Here is an example of what I'm looking for:
NAME
Most Frequent
No. of times they appear
[Code].....
I'm trying to count if there are situations where column a is equal to a specific number and column b is equal to another number.
both columns are numbers. Does anybody have a good idea how to do this, maybe countif, but i am not sure how to do more than one item with that.
I have a linked document, only 2 sheets. On the first sheet there is a column for + or - signs, I need a formula for the second sheet to show the + as a 6 and the - as a 5 instead of just the symbols carrying over.
View 3 Replies View RelatedI'm trying to get a random sequence of numbers e.g. 1-20 so that no number is the same i.e. no number appears twice.
I used Int(20 * Rnd + 1) + 1 but this gives me same numbers. Any ideas how i can do that?
Using the following data
R10-12128
R11-12x12x8
R11-12x12x8
R1-12x12x8
R1-12x12x8
R12-12x12x8
R14-12x12x8
R16-12x12x8
R18-12x12x8
R2-12x12x8
I want it to sort like this:
R1-12x12x8
R1-12x12x8
R2-12x12x8
R10-12128
R11-12x12x8
R11-12x12x8
R12-12x12x8
R14-12x12x8
R16-12x12x8
R18-12x12x8
What is the formula to achieve this?
I have list of dollar amounts given to me and a general ledger sum which some of those in the list must equal. How can I determine which of these amounts will equal a given ledger total?
View 12 Replies View RelatedI have three workers for which I am counting the data from using a countas formula. The cells are A1,B1,C1 and I want to highlight in another formula to the right if these three values do not equal each other, I have tried and if formula referencing each of the combinations of the compares however it does not work in the desired fashion.
View 3 Replies View RelatedIf I have A1 = 1, A2 = 3, A3 = 5, A4 = 7, and A5 = 9 in column A, Would someone know how to get the combination of numbers in column A that equal 13, or 24 for example?
View 9 Replies View RelatedI have a range of values in a1:f500 the values are all numbers between 1-25. I then have a value in cell h1. what i need to do is find all numbers that equal to 9 in the range a1:f500 and replace all these 9's with the formula =$h$1 so that i can simply change the values in the range a1:f500 by changing the value to say 12 in cell h1. could someone please text me the vba code that needs to be entered for sheet 1?
View 9 Replies View Related