what I have is a spread sheet of over 3000 numbers.
Now this is what the number looks like:
0000123456000
what I need is basically a formula or something to take out the zeros (They are not really zeros on the spreadsheet, its just an example to make it easier to display). And leave the six digits in the middle. Is this possible?
I have a column full of 10 digit pone numbers. All different numbers and many different area codes (No dashes):
Example:
3305555555
4583651586
4128563254
Is there a way to remove the three leading numbers from all of the numbers at once for the whole column?
Is there a formula that can extract data by taking numbers within a specific range and assigning a number to the data within that specific range?
0-25=1, 26-50=2, 51-75=3, 76-100=4, >100=5
I need to take number in between cells. For example, I have numbers 1, 2, 3, 4, 5, 6, 7. When I type 5 in a column, I want to have 4 and 6, above and below 5. I am doing this to ease interpolation, so that whenever I want to do interpolation, I just need to type what number, I would like to have to be interpolated.
Attached file is the example : Matic Interpolation.xlsx‎
I have a dilemma. What I want to do is: If I input a date in a cell I want the adjacent cell to take the next highest number from a list on another worksheet. My example attached
View 3 Replies View RelatedI have created a simple spreadsheet to keep track of work hours. I simply enter in each days hours and then I get a total. I have each cell formatted for time (hh:mm). However it is annoying to have to type in the colon for each days time.
Is there a way I can just type in the three digits '9,3,0' into the cell and have it come out as '9:30'?
Formula that counts the number of people who are on my course and are taking their tests.
I have 5 classes, Class1, Class2, Class3, Class4 & Class5.
Column A details which class the pupil is in.
Column B details the pupils name.
Column C details the state of the course. This is either a DATE which they started, or "Paid" (they have paid for the course but not started), "Not Paid" (havent paid for course and havent started) or "In Progress" (Payment is in the process of being arranged).
Column D details the state of their final exam. This is either a DATE as to which they have finished and passed the exam, "Not Taken Yet" (Havent taken the exam yet) or "Fail" (They failed the exam).
This is where I am having problems, at the top of my sheet I want to summarise each classes statistics, but I am having problems with the COUNTIF. For example:
What formula can I use to add up the number of people in each class who have started the course?
E.g.
=countif(C10:C100," Is a Date ? ") AND is in Class1 ?
What forumla can I use to add up the number of people in each class who haven't passed the test ?
E.g.
=Countif(D10:D100,"Not Taken Yet" & "Failed") AND is in Class2 ?
I know I need an array to work this out.
And finally to really complicate things how about :
People in each CLASS who have STARTED the course (Date in C) AND haven't passed their test (Col D). I understand this is relying on a three part array whereas the others are 2 parts ?
I need to generate 8 (random) numbers which should range between 0 to 80 and the sum of those generated 8 numbers should be 80.? How can I achieve this.
View 14 Replies View RelatedHowever data now takes far too long to enter; for example if I type the number 9 into a cell it takes around 30 seconds to let me enter more data, it is as if it is working on some calculations. The VBA I'm using is as shown below.
View 3 Replies View RelatedI have a code below to only allow a number to be entered into one of 2 cells. I'm trying to do conditional formatting based on these to cells, and the evidence is showing that even when I type a value into either I21 or I22, they are taking on a value of zero.
I have conditional formatting stating that if I21>0, then do one thing. And in a separate cell, I have =I22>0, then do another thing. Neither works, and even using =I22<>0, then do formatting, and it doesn't work, telling me that the assumed value is always zero.
I need to calculate the average of 5 grades from A,B,C,D, and E. However I want it to the average even if any number of grades (i.e. up to 5) are entered.
e.g
grade1 grade 2 grade 3 grade 4 grade 5
A A C B E
I have assigned a vlookup table to convert to numerical values, and got it to calculate the average, but it falls apart when any grade is missing.
I have used the iferror command on the different calculations, but when i put them to gather into one large string, again it falls apart.
I've got a sheet which I need to analyse and split into several different sheets but the raw data format leaves a lot to be desired as there are blank rows inserted randomly between rows of data. I need the 'good' data to stay in the same order so I've written a macro to sort through the data and delete any blank rows leaving the good stuff behind.
The problem is this takes ages as there can be up to 30000 rows that need to be checked and I need to do this 5-6 times a day. I just wondered weather there was a quicker way to do this? The code I've got is detailed below:
Sub Prep2()
'Delete all blank data rows
Dim Rows As Double
Dim Rownum As Double
Application.ScreenUpdating = False
Rows = Selection.SpecialCells(xlLastCell).Row
For Rownum = 2 To Rows
If Cells(Rownum, 11) "" Then GoTo NxtRownum Else
Cells(Rownum, 11).EntireRow.Delete shift:=xlUp
Rows = Rows - 1
NxtRownum:
Next Rownum
Application.ScreenUpdating = True
End Sub
Is there a fomula that would take data from a cell.
I am looking to take everything before and includuing the word LTD
For example if a cell contained
Joe Bloggs Ltd (Mr Jones) C/o USA
I would like to be able to take "Joe Bloggs LTD"
I have a column of 96 numbers (observations every 15 minutes for a 24 hour period), and I want to take the average of these numbers in groups of four (the hourly average). My data starts in cell A2 and goes to cell A97. My first batch of averages are labeled as follows:
Range("A2").Select
startCell = ActiveCell.AddressLocal
ActiveCell.offset(3,0).select
stopCell = ActiveCell.AddressLocal
Now, clumsy programming aside, I just want to say something like this
averageCell = AVERAGE(startCell:stopCell)
I have a string, specifically a file path, and I want to take the last 5 characters of the string. How would I got about doing this?
View 3 Replies View RelatedHow i can take just team names in A column as home and away to B-C columns ?
View 4 Replies View RelatedI have a huge spreadsheet where after every 12 columns there are two "special" ones, which contain certain entries. In the first of these two, the entries from the second one are numbered in a certain way. Please see the example.
I need a formula in a separate column (yellow) which will gather all these entries from all columns, and put them in order, as I did manually in this example.
Instead of 4 "groups" of columns, in my real file I have 200 of them, with 400 rows each, which makes it more complicated..
I am an excel novice trying to create a list of local churches. There are several online lists that I am taking the data from but, being a novice, I am entering the data manually. How to set up a method to bring the data into excel and order it where I want it.
I am attaching the spreadsheet.
These are the websites I am taking my data from: [URL]
Currently I have 3 Columns. The first column is a list of phone numbers that have confirmed and bought our product. The second column holds the phone numbers of the initial leads that we generated. And the third column is the keyword that the lead used to find our website.
What I need to do is match the phone number from Column A with the phone number of Column B then record the frequency of the keyword that was used to produce that lead.
I've attached a screen shot with the first few rows of my table (Nearly 1000 in total) as well as an example of what I imagine the result looking like.
First Few Rows Of The Table:
Excel-Keyword-Matching.jpg
What I Am Looking To Do:
Keyword-Frequency-Complete-Example.jpg
Debug error points to:
BuildPlan(Worksheets(SourceWorksheet).Cells(j, "A"), Worksheets(SourceWorksheet).Cells(j, "E"),
Worksheets(SourceWorksheet).Cells(j, "F")) = Worksheets(SourceWorksheet).Cells(j, "K")
Sub is below:
Sub UpdateMonthForecast()
Dim Month As Integer
Dim Year As Integer
[Code]....
I have a bunch of spreadsheets in a folder and I need to take one line of each spreadsheet (which is in the same place on every one) into a master spreadsheet.
I am always adding to the spreadsheets in the folder.
Is there a way to have my master spreadsheet look out for changes in the folder and when there are changes add the row into it?
I have a column where a lot of the fields match. What I want to do is set up a formula where all of the matching fields are numbered (1,2,3,etc). For instance:.......
I want the numbering to read the matching items and count them (not a sum but rather just a numbering/ordering column)
This is what I would want it to look like:...........
I have autofiltered a column to meet a certain criteria (which hides some of the rows), then I want to put a cell with the sum at the bottom of that column. When I do this, it takes the sum of all of the rows in that column, even the rows that are hidden. Is there a way to only take the sum of the rows showing?
View 5 Replies View RelatedI have a userform that searches a data base for an address an pulls up the corresponding information. I was wondering if there was a way to get it so that if I put 1234 5th Street when it puts the information into the data base it would separate it into two cells so 1234 would be in one section and 5th street would be in another.
View 1 Replies View RelatedI have a number of reports in excel (all in the same format) which I need to take certain data out of and store it into one main spreadsheet. Searching through the forums I found some code which I think will do this:
Sub test()
Dim myDir As String, fn As String, ws As Worksheet
myDir = "C: est"
fn = Dir(myDir & "*.xls")
If fn = "" Then Exit Sub
Do While fn ""
Set ws = Workbooks.Open(myDir & fn).Sheets(1)
ws.Range("a7", ws.Range("a" & Rows.Count).End(xlUp)).EntireRow.Copy
ThisWorkbook.Sheets(1).Range("a" & Rows.Count).End(xlUp).Offset(1)
Workbooks(fn).Close False
fn = Dir
Loop
End Sub
I have tried using this code, altering the Dir String to the folder the documents are in.
However, when I run the code, nothing happens. I don't get any errors, and nothing appears in the spread sheet.
To be honest, I am not totally familiar with macros in Excel. I can get the gist of what the code is doing having using VB before, but am not totally familiar with some of the functions being used here.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Application.EnableEvents = False
If Intersect(Target, Range("E:E")) Is Nothing And _
Intersect(Target, Range("H:J")) Is Nothing Then
Exit Sub
End If
Is there a way to make this run a little slicker , as at the moment it is taking almost 10 mins to update RngDate is $L6:$FB1038
I hope my title was clear enough, but I’m not sure how to set this up. I have numbers in cells B4 and B13. I want to take the largest of these two numbers and multiply it by 0.85. I then want to take this number and compare it to a number in cell B23. If B23 is greater than the larger of B4 and B13 X 0.85, I want B33 to display “No” in a boldfaced red font. If B23 is less than the larger of B4 and B13 X 0.85, I want B33 to display “Yes” in a normal black font. This formula will be in cell B33.
View 5 Replies View RelatedI'm matching ISO country codes to country names.
This works great as long as the 'lookup_value' portion of the match fuction matches the 3 digit code on Sheet3.
Is it possible to return my desired result without this matching perfectly.
So a value in 'Sheet1 Column A' that begins A344xxxxxxx will return the same result in 'Sheet1 Column F' as if 'Column A' just had a 3 digit number.
I have a couple shreadsheets I have created, one is for Purchase order's and has 4 different sheets. with one sheet holding addresses linked a drop down menu in the first sheet, all in the same workbook. Also there is a macro within this sheet to print to a specific printer, and a specific number of copies when CTRL p is pressed. The total file size is approx 9 mb. Opening this file takes approx 30 to 45 seconds. There are no links to any outside files, only links within the workbook itself.
The second File I have recently started having problems with (Our Quotation log) is A workbook containing 3 sheets, 2 sheets independant and one sheet linked to the second that Summarizes the first and also has cels to enter information on the person the quotation has come from. I have this sheet formatted as a table so I can sort by name etc when doing followups.
This workbook also takes approx 30 to 45 seconds to open, and on occasion longer. All workbooks are stored on my computer. The PO workbook has had the problem since I created it, although the Quotation log workbook has only had the issue this week.
Is it possible to take text from 2 different cell and inserting into one cell?
For example:
Cell A1 reads 'John' and cell A2 reads 'Smith' can I make cell A3 read 'John Smith' by taking those two bits of information?