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 RepliesI want to delete all the rows with values between -1 and 1 in column 'I'. I currently set up a macro to do the formatting and conditional stuff that highlights all values above 1 and below -1 - these are the values I need to conduct my analysis on, but I can't figure out how to delete the rows in between. The worksheet has approximately 5,000 rows (and growing) and this would help me clean up the data significantly.
View 7 Replies View Relatedi want to delete entire row if D2:D10000 has a numeric value e.g .111 to 100000.1114
View 4 Replies View RelatedThe 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 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.
I 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.
Frequency
Formula to show the final/total a numeric values appears in an adjacent column.
I am currently attempting (I've researched many posts on this), to count /show the final totals a duplicate numeric value appears in one column, in an adjacent column (example below).
I am currently using an array Frequency formula below (courtesy of the board) filled down in column Q. this works well to count the frequency of duplicate numeric values appear on the list.
Formula used in Column Q below
{=IF(C2>=0.1,MAX(FREQUENCY(IF(C2:$C$1000=C2,ROW(C4:$C$1000)),IF(C2:$C$1000C4,ROW(C2:$C$1000),""))))}
However my aim is also to show the final entry of a numeric value in adjacent column R, by displaying text to indicate this. My example below shows “final” in column R
NB: My list is sorted by column C to ensure all numeric values are in ascending order.
Example
Column C Column QColumn R
12567 1 final
15789 1
15789 2
15789 3 final
23456 1
23456 2 final
12678 1 final
18965 1
18965 2 final
I have a column of data where duplicate records appear, can I get a macro to review the column and delete the duplicate record, I need the line item number to still show although the serial number has been deleted, please see example below-
line_number Serial_number
14FM12A1QWINC-1A
14FM12A1QWINC-1B
14FM12A1QWINC-1C
14FM12A1QWINC-1D
14FM12A1QWINC-1E
14FM12A1QWINC-1F
14FM12A1QWINC-1G
14FM12A1QWINC-1H
14FM12A1QWINC-1I
14FM12A1QWINC-1J
14FM12A1QWINC-1Jdelete serial number in column B, keep line number 1 in column A
24FM12A1QWINC-2A
24FM12A1QWINC-2B
24FM12A1QWINC-2C
24FM12A1QWINC-2D
24FM12A1QWINC-2E
24FM12A1QWINC-2Edelete serial number in column B, keep line number 2 in column A
34FM12A1QWINC-3A
34FM12A1QWINC-3Adelete serial number in column B, keep line number 3 in column A
44FM12A1QWINC-4A
44FM12A1QWINC-4Adelete serial number in column B, keep line number 4 in column A
Is there a VBA code I can use that will delete all rows that contain "ABC" or "YXZ" or "HHH" in column A ?
View 2 Replies View RelatedMy code is always failing on the findnext method. I'm thinking that the findnext method fails because it loses it's cell reference when the row gets deleted.
Sub findwbs()
Dim rngFind As Range
Dim strFirstAddress As String
I'm about to start doing it manually, just wondering if there is a way to use functions in excel to do this automatically:
Here's my situation:
I have one column A, and it has 25 000 email addresses, 1 per each row, so 25 000 rows.
Then i have in another column ''B'' with 3000 email addresses.
I need to delete these 3000 email addresses from the master 25 000 list.
Is there a way to do it automaticaly?
I imagine if there was a way to set a script to delete values in Column A found in Column ''B''.
Least, I was going to try to copy the delete list and paste to master list, so I would end up duplicates of the emails that are to be deleted and they would appear highlighter with conditional highlighting.
Need a Macro to delete all rows containing values less than or equal 300 in column A
View 5 Replies View RelatedI am using this code below to delete individual matching values (the value in "A2") from a column.
Code:
Set Found = Columns("AH").Find(what:=Range("A2").Value, lookat:=xlWhole)
If Not Found Is Nothing Then Found.Delete Shift:=xlShiftUp
I am receiving error "Delete Method of Range Class Failed" in the highlighted line.
I have a excel file which contains dublicate rows. The duplicate rows can be identified based on few cell/column values. I need a macro to delete the duplicate rows when the below condition is satisfied: let us consider row 5 and row 6:
If column 7,12,13,16,17,18,19,23,24,27,28,29,30 in row 5 = row 6 then row 6 has to be deleted. This condition has to be followed for all other rows in the excel used range. Have attached the sample workbook.
I want to do, is search column A for claim numbers that match. When I do have a matching claim number, I want to concatenate the original cells ownership field with the said matching cells ownership field (or move into a column in the same row, I can always concatenate later). Once that is complete, I want to delete the row I took the information out of.
I want to join this data in ArcGIS, but as of right now, it's not a 1-to-1 relationship, so only a relate works. That doesn't help me as I want to display claims by ownership, and this can vary per claim. Company A may have 100% on one claim, and then split another claim 50% with Company B.
This causes a double entry on the claim field in this current spreadsheet I have, which requires me to clean it up by making multiple columns of ownership vs. an additional row for shared ownership.
My problem:
Column A Column B
1235555 Company A (50%)
1235555 Company B (50%)
1235556 Company A (100%)
1235557 Company A (33%)
1235557 Company B (33%)
1235557 Company C (33%)
What I would like to see
Column A Column B Column C Column D
1235555 Company A (50%) Company B (50%)
1235556 Company A (100%)
1235557 Company A (33%) Company B (33%) Company C (33%)
In a column I have data like 2W, 2BM, 4W, 6BM, 10W and 15BM. Question is how can I total all W and all BM. Total for W = 2W + 4W+10W = 16W and total for BM = 2BM +6BM +15BM = 23BM.
What formula should I used to get totals
When using the replace rule in Excel, you can use "?" to represent a standard variable, e.g. if you had the text "Bottle 100g", and you set the replace rule as "?g", it would delete the "g" and the four characters before it, leaving you with "Bottle".
However, if I have lots of replace rules to make in a file, and I only want to eliminate the numerical values before a letter, how do I go about doing this?
For example, if I had the text "Dog food 10g", and I made a replace rule "??g", it would leave me with " food ". What I would really need, is a way of taking away the numeric values before the "g", which would give me "Dog food".
Therefore, is there a unique identifier for numeric and/or text values?
I need a left side padding for numeric values to keep them in 4 digits
ie 1 becomes 0001, 25 becomes 0025 and 345 becomes 0345. Following is the custom number format I tried and it fails.
[<10]"000"#;[<100]"00"#;[<1000]"0"#
I have the following code and I am really confused to, why its not reading numeric values from the code below:
Code:
Sub MG08Aug32()
Dim Rng As Range
Dim Dn As Range
Dim n As Integer
Dim Num As String
Set Rng = Range(Range("E2"), Range("E" & Rows.Count).End(xlUp))
[code]....
I desire the following output below:
97.25-97.50a
97.25
97.89-90.50a
97.89
89a
89
98a
98
I have the following issue:
=AR85*36*75*6.29
The above formula does not return any value and it simply appears as it is keyed. Cell AR85 has 5. I know it is something to with this cell, but I cannot fix it. In fact the whole Column AR has these values downloaded. I tried "text to columns" but it still doesn't work.
The Macro below will not combine numeric values Just Text. I get a Type Mismatch Error When I open It. I need for this macro to run with Numeric Values 1,2,3,etc.
View 2 Replies View RelatedI'm trying to show learner progress during our half term collections but the values we track are similar to that below
2B+
2B
2B-
2C+
2C
2C-
1A+
1A
When I attempt to make a chart in the normal format it doesn't recognise the value.
My colleague has a problem where he needs to Extract numbers from alphanumberic strings. Here is the formula he is using:
=MID(B7,MATCH(TRUE,ISNUMBER(1*MID(B7,ROW($1:$297),1)),0),COUNT(1*MID(B7,ROW($1:$297),1)))
It works if alphabetic and numbers are clustered together such as scs987
It works if alphabetic and numbers are not clustered together such as scs987dtg
Problem: It does not work when numbers are not clustered together such as scs987dtg1234
Is it possible to convert :
Nine Hundred Sixty Three Thousand Seven Hundred Eighty One
Eight Hundred Seventy Eight Thousand Eight Hundred Seventy Eight
Eight Hundred Twenty Two Thousand Seven Hundred Eighty Four
Eight Hundred Twenty Six Thousand One Hundred Eighty Nine
Nine Hundred Three Thousand Nine Hundred Six
to numeric...
EG:
963781
878878
872784
903906
formula to add letters but with a numeric value. this is for a schdule sheet. where w would equal 7.5 and x would be 0.
i am using this
=SUMPRODUCT(--(ISTEXT(B3:H3)))*7.5
reads the w and adds up ok but need to be able to put w for work and x for off days and still add the total hours
I have lists of room areas in metres squared, I extract the web based lists to excel: for example.
10.56 m2
14.23 m2
21.34 m2
How do i calculate the total of these amounts?
Is there a formular to remove the "m2"?
I have tried using =SUBSTITUTE(A1,"m2","") which deletes the "m2" from the cell, but then I can't use =SUM(A1:A3) as this totals the 3 cells as 0.
If i use =SUM(A1+A2+A3) the total is correct, but I have over 3000 cells in one column to add together.
Is there a formula or something to remove all instances of "m2" or "m3" from the whole sheet or another way i can calculate the totals.
I'm currently trying to create a rota timesheet which automatically takes shift patterns and deducts for unpaid breaks.
Basically if a shift is less than 4.5 hours long then no break is deducted
If the shift length is 4.5 - 6.5 hours then 15 mins are deducted
If the shift is longer than 6.5 hours then 30 mins are deducted.
The problem I currently have is all numeric values only are not being recognized on my password column. I have tried changing the cell formats but still problem still there. The program work if an alphanumeric value is declared as password. However if you simply indicate plain numeric values only as password, it returns incorrect values, it like it doesn;t recognize all numeric values. Also how can I make 0000 as a declared password? when I place 0000 on my password column, only one zero value is being displayed instead of four another thing if I happen to place 0123 only 123 is being displayed.
View 4 Replies View Relatedwho can restrict the input values to numeric values only? I have 2 textboxes where the user enters employee id and numeric choice value of 1,2 and 3...I need help in making the textboxes restricted to numeric values input only. How Preventing the users from entering alpha and symbols values.
View 6 Replies View RelatedI have created a calendar in Exel for individuals to put in their time at the bottom for everyday I have put in a total. Is there a formula that I can sum the dates, pick a certain time from those date and get my total hours for that particular week?
View 6 Replies View Related