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"
However 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 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]
I 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.
There is a folder in a shared location which many people use at work. Within this folder, I have an excel document with all current stock (called "Current Stock"). There will never be more than 20 items in current stock. There is also other documents within this folder which relate to lots of other things, Interest, Deals etc.
What I want to do is every time a new item is added to the "Current Stock" document, a tab is created with that Item name within all the other documents.
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 use Excel 2007 in a stock sheet situation.
I have attached a picture of what the sheet will look like.
What I need is a formula that will take a value in column c and then add the value in colum a behind that. Preferably with a X or - between them ie.
If i took the first row and applied the formula it should kick out 5 x Jam
The main thing is it must output the answer to another file/page so that the main page stays the same. And if there is no value in Colum c it must ignore it.
I have a workbook that compares two sheets of data by taking the difference between the two. Some of the cells contain N/A for value, so when I take the difference of two sheets, it gives me #Value! error. My question is how can I format it, so when the cell contains text it displays N/A or if its a value: it takes the difference and populates the value.
View 9 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 RelatedIn the file attached there is only the data to look at, though file is little bit lager.
Now in cell G10 I've got the stock forecast in 3 months time, this stock forecast is calculated from a date rounded to the closest month (according to my criteria), then the formula looks at the reference number cell D1 to work out the formula. All the steps are in the file.
In order to be more accurate my boss and I want to use the exact date, so that delete all the round month process. My intention is to use the day of the date and then make a fraction of the months to calculate the average. Taking into account that a month have as an average of 30.4166 days.
In the file I did an example, to clarify my messy explanation.
Cells B2, C2, D2 won't be needed in order to do so.
Please have a look at the file. At the end my intention is to to such a formula or a similar one. The formula should be an automatic one when typing dates in cell A10
The result of it is in row 31 and 32.
trial.xlsx
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?
Excell file eWorked Example.xlsx
I am trying to set-up a formula that needs to be copied down and across and draws upon a data table that has a horizontal and vertical parameter that needs to be checked for the formula to return the right answer.
I have tried all manner of Index/Match, Offset etc but can only get one of either the vertical, or horizontal parameter working.
I have attempted a formula which is
=INDEX('PIP Input'!$C$17:$C$29,MATCH(H$1,('PIP Input'!$H$17:$H$29),0),MATCH($B4,'PIP Input'!$H$15:$L$15,0))
('PIP Input'!$H$17:$H$29) is column dependent on MATCH($B4,'PIP Input'!$H$15:$L$15,0)
If $B4 returned a different result from ('PIP Input'!$H$17:$H$29) then it would need to be ('PIP Input'!$I$17:$I$29)
How to nest the Horizontal Match inside the Vertical Match, such that the Vertical Match changes column depending on $B4?
I have 2 sheets: "MasterInventory" and "InventoryFeed". "MasterInventory", is my full list of all the item I sell on Amazon.com. "InventoryFeed" is populated when I download an update from amazon and paste the data into it. Each list has about 30 columns and each list uses identical headings in the top row. The third column in each sheet is for the product's SKU.
What I'd like to do is loop through each row in the "inventoryFeed" sheet, assign it's sku (the value in column 3) to a variable, and then copy the entire Row. Then I'd like to switch to the "MasterInventory" sheet, and look in column C for the identical SKU. If it is found, I'd like to overwrite the entire line by pasting in the data that was copied earlier. If the SKU is not discovered, I'd like to paste the information into the next blank row in the "masterInventory" sheet.
After the whole thing is looped through, I'd like to sort the MasterInventory Sheet from A to Z by the product's SKU.
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
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 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
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 RelatedI 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 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..
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 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'?
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