I want to be able to sort the columns in a worksheet based on their cell values, eg. column of text sorts A to Z and column of numbers sorts largest to smallest. Cells are formatted as General, number or currency but the sort button only offers A to Z or Z to A.
View 2 RepliesThe attached picture shows part of an excel file. It contains about 50.000 rows which I want to run a pivot on. The problem is though that the file contains some rows which stop my pivot from running correctly. These data which I mention are in rows 4, 7, 22-25. Seeing as deleting them manually would take me hours and hours I was wondering if there would be a macro which I can run which delete rows automatically. I think it should filter on the following: whenever there is not a numeric value in column A, the entire row should be deleted.
View 6 Replies View RelatedI am wondering if it is possible to format a cell that contains text - based on a number in the cell next to it. Here is what I have:
Category......Mean
Results.........3.3
Morale..........3.8
Buy-In..........4.0
I would like the word 'Results' to display in BLACK text because the mean score is in the range 3.0-3.9 (same goes for the word 'Morale'). I would like the word 'Buy-In' to disply in GREEN text because the mean score is in the range 4.0-5.0. Basically, the mean scores are calculated using formulas and I would like the color of the category to change based on the mean score number. Is there a way to use conditional formatting to do this? Or another way to automatically make this work? File is attached.
I have to create 1000 dynamic ranges and do not want to do this manually. See the list below for the syntax I am using, I am only showing the first 10.
Name Range
KPI_01=OFFSET(Date,0,1)
KPI_02=OFFSET(Date,0,2)
KPI_03=OFFSET(Date,0,3)
KPI_04=OFFSET(Date,0,4)
KPI_05=OFFSET(Date,0,5)
KPI_06=OFFSET(Date,0,6)
KPI_07=OFFSET(Date,0,7)
KPI_08=OFFSET(Date,0,8)
KPI_09=OFFSET(Date,0,9)
KPI_10=OFFSET(Date,0,10)
I have tried the following code which works to an extent...
Range("=OFFSET(Date,0,1)").Name = "KPI_01"
..but the final result of the named range is an actual range, i.e. "A1:A13".
How can I keep the named range as a dynamic range?
I would like to total numeric amounts based text in another column.
For example, I have a bunch of rows in "column F" with the same text (company name) and in "column I" i have numerics. Is there a way to total "column I" based on the same text of "column F"?
I have an application where there is a column A with values of F1 through
F83. There is data in columns B,C,D,E,F AND G.
From time to time there is an addition in column A, the value being F1A or F2A etc.
The rows then have to be sorted so that F1A follows F1 and the next row would start with F2 rather than F10.
I'm doing a sort of product codes which are of different character length with some numeric and others alpha-numeric.
A small section of the sort is below.
I want it to end up like this:
4040
4040A
4040B
4040BTO
4040C
4040E
40400
40409
41402
42402
42402A
43400
But the sort ends up like this:
4040
40400
40409
4040A
4040B
4040BTO
4040C
4040E
41402
42402
42402A
43400
In other words, I would like product codes where the last character is a letter to be sorted ahead of product codes where the last character is a digit. The way Excel is normally set up, it sorts in just the opposite way.
I'm looking to sort one column based on the values of another.
For example, if Column A has a list of names, and column B has a list of their ages, I just want to sort Column A based on the ages, but I want the ages column to stay as it is.
Is it possible to do this without a Macro? And if it requires a macro, how can I write a macro to make this work?
Currently I have a macro set up that sorts a range.
It sorts numbers first in ascending order and then text comes at the bottom of the sorted range.
Is there any way of reversing this so that text appears first and then numeric values in ascending order below?
I have a spreadsheet that track orders. I want to be able to add a bit code to my current VBA script that sorts the data based on the values in a particular column (smallest to largest).
Current table looks like this:
Order Number
Date
Quantity
Colour
Priority
548562
12-09-2012
56
VBA formula
2
[code]....
I want to add be able to sort the data, smallest to largest based on Column E (tittle Priority)
I would like to sort words or data which has the First capital letter, words which has spaces and words which contains number... i have attached a sample file.
View 9 Replies View RelatedI want to rearrange(sort asscending) columns based on numerical value in column header string through VBA macro. Please check attachment.
i.e. (Present Data)
# A B C D
1 col.1 col.4 col.3 col.2
(Output Data )
# A B C D
1 col.1 col.2 col.3 col.4
test.bmp‎
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.
I am stuck on this one. In the attachment I have values pulling into N3:N7 based on a drop down choice. Some of the numbers are numeric (no decimals), others are currency (no decimals), others are percentages (2 decimals), etc.
Is there a way to pull values I'm currently pulling into column N and have those values match the format of the source? I've tried converting it into TEXT but then I cannot place conditional formatting data bars on the values and have it work.
I'm trying to add up some values based on a criteria in another column. I have numbers in column A ranging from 0 - 100 and in column B have totals for those numbers. What i am trying to do is Add up the totals in column B based on the following criteria's in Column A.
<7, >7 & <=30, >30 & <=60, >60.
I have a summary page to display the individual results in separate cells.
how I can get the userform to close when another worksheet is selected. what I really need is for the userform to just show on one worksheet (not close) Is that possible? If not I want to be able to re-size the userform when another worksheet is selected (like getting it to minimise)
View 2 Replies View RelatedI have a list of numbers in which I need to return the last 5 digits; the length of the string may vary.
However, if the last two digits of the number ends in a specific value then it should skip over the last two numbers and return the preceding 5 digits.
I have tried and have used the very basic Left, Right and Mid functions. My problem is I dont know how to code the formula to identify the last two digits and skip over them, if necessary.
I have provided an attached example.
Extract numeric value based upon ending characters.xlsm
I have an excel 2008 sheet and want to add cells in an odd way. Here is the best way I can describe it.
I want a formula to add cells based off a number value in another cell.
So it would be something like
IF M3= 1 add C5 to SUM ; If not then go to next step
IF M3= 2 add F5 to SUM ; If not then go to next step
IF M3= 3 add I5 to SUM ; If not then go to next step
IF N3= 1 add C5 to SUM ; If not then go to next step
IF N3= 2 add F5 to SUM ; If not then go to next step
IF N3= 3 add I5 to SUM ; If not then go to next step....................
I have a spreadsheet that has (amongst other things) 2 charts based on dynamic data with ranges that redraw using vb... The charts will draw envelopes that correspond to an aircraft's moment or c of g envelope - there will typically be 2 envelopes drawn, "normal" and "utility" or "aerobatic".
Plotted on the charts are an aircraft's takeoff and landing weights and moment/C of G.
I'd like to be able to use a formula or VB to determine which envelope takeoff/landing information falls in (or if it falls outside all envelopes). This data would then be used to generate an error message/possibly also in conditional formatting, etc.
I have a spreadsheet and I’m trying to create several different staffing scenarios
Basically what I want is to populate column E with the amount of staffing based on the number of classes in column B. The range that I want to apply is in the green box in the sample. I’m trying to have excel give me the staffing value if the current number in cell B5 is a certain value – so in the example 1 through 4 = .2, 5 through 8 = .4 and 9 through 12 = .6. this site gave me some ideas so I tried V lookup and Index but I seem to be missing something and wonder if it is not combinations of nested functions?
In the attached excel file in column C, beginning on row 4, are numbers. These numbers will automatically update on my bloomberg terminal.
(1) I would like to then in column E to have a rating given to those numbers based on the below scale. The way the scale works is that if its 0.6 its a 10. If its anything slightly above 0.6 exactly, its a 9. A 10 is also a 0.5 and any number in between 0.5 and 0.6. The scale is below.
(2) Given that the formula that is being updated is a bloomberg excel formula (in the attached document I pasted it as values), will an excel formula be able to read the updated number in the bloomberg formula? If not, in column D is there a way to have it automatically paste D as values (and update each time column C changes or workbook is saved), and then have column E read off of those numbers.
Scale:
10 = 0.5 to 0.6
9 = 0.6 to 0.7
8 = 0.7 to 0.8
7= 0.8 to 0.9
6 = 0.9 to 1.0
5= 1.0 to 1.1
4= 1.1 to 1.2
3= 1.2 to 1.3
2= 1.3 to 1.4
1= 1.4 to 1.5
0 = anything greater than 1.5
I am trying to do is extract the volume size of products in 'ml'
from 10k plus products from a description field cell.
this description field could also contain the weight of the product in grams
so I cannot just do a search for a numeric string ,
it has to be associated with the milli-litres statement .
is is possible to do a sort of ' *ml ' search and then select and copy to another cell ???
i want to delete entire row if D2:D10000 has a numeric value e.g .111 to 100000.1114
View 4 Replies View RelatedSheet 1 has a column with verbiage in it. Sheet 2 has a column of possibel verbiage with a second column of numeric values for the verbiage On sheet 3, I want the numeric value for the verbiage in cell b19 on Sheet1
Long way, create a new column on sheet1, run a VLookup to insert the numeric value into the new column, then do an "equals" on sheet 3 that point to the Vlookup results. How can I get the value onto sheet3 without creating the extra column?
I have the following issue. I have a file, which contains about 7 000 columns, each with 8000 rows, mostly filled with numbers (with decimals). But sometimes, numbers will be replaced by "N/A" where a value was not available. What I want to do, is delete columns, where all the rows are just "N/A" to reduce the size my file.
View 2 Replies View RelatedI recorded a macro to perform a graphing operation which will be used in a For-Next Loop which will run eight times. I need to modify the “ range” portion of one of the recorded lines to allow the graphing data to be dependent on variables I have set up as integers. See the applicable part of the recorded code below.
Dim firstrowno As Integer
Dim ltcol As Integer
Dim lastrowno As Integer
Dim rtcol As Integer
‘code which sets the values of the above variables Is Not shown
ActiveChart.SetSourceData Source:= Sheets("PLOTS").Range("AN27:AQ33"), PlotBy _
:=xlColumns
How do I replace/modify the ("AN27:AQ33") in the code above using the variable integers; firstrowno, ltcol, lastrowno and rtcol ?
How do you shade a cell based on multiple ranges of data? e.g. A value of...
1 shades red
2-3 shades orange
4-5 shades yellow
6 shades green
I have a small sheet that has a number in Column A and a larger number in Column B along with some data in Columns C and D. I wish to make a separate sheet that has rows numbered using the numerical range from Column A and Column B along with the data from Columns C & D
View 2 Replies View RelatedHow do I select or highlight cells from row 1 through row x (a numeric variable) in column A? I'm eventually hoping to shift those cells in column A (from row 1 to x) to the right by one.
View 1 Replies View RelatedIn cell A1 I have a countdown timer from an external source that counts down to 00:00:00 approximately every 10 minutes and then resets.
I need a macro to be triggered whenever 00:00:00 is reached.
The macro will select column AL and copy a range of cells (within AL) that contain numeric values only and paste these numeric values in the adjacent cells in column AM. It is important that the paste is values only and not formatting or formulas.
Column is being fed dynamic data from an external source and displays either #N/A or numeric values.