With my limited knowledge I am having trouble constructing formula to break the following info that I receive frequently from my credit card company into 3 columns - Date, Details, Value
21 Apr 2007 SAINSBURYS PETROL BURPHAM £10.00
I have a file with a physician name and title in the cell as follows:
"James A. Jones, M.D."
I need a formula to reverse the last name, drop the title "M.D.", get rid of any additional periods after the middle initial and put it in upper case:
"JONES, JAMES A"
There are some names without middle initials also so this may present an additional problem.
I would like to fill in a a form on page /sheet one and have the same info on every sheet that follows is it possible?
View 3 Replies View RelatedI have attached a spreadsheet and I am trying to capture the info in lines 2,7,12,17 and return the info into column d,e,f,g
The info in these columns at present has been manually entered but I am sure it could be automated.
OOL Roster Final 18-31Aug14.xlsx
I am looking for a formula or Macro if its the only way which does the following:
I have a set of locations in the below table which also has exactly the same name as a tab in the same workbook. So for example I have 1 tab called "summary" which contains the below table and than another tab called "London453" & another tab called "New York45" & another tab called "Paris3232" & last tab called "Vancouver43"
In the "Reconcilation needed" column I would like a forumula/macro to start in Cell B2 and look at the cell next to it in Location column [Cell A2] and then go to the corresponding tab in the workbook and if there is a value in cell G22 in that tab, than I would like the formula/macro to take the first 7 charactors of Location value otherwise put nothing.
Example :
in tab "London453" there was a value in cell G22 --> hence 7 charactors "London4" in below table
No value in Cell G22 in the "New York45" tab --> hence nothing in below table
Value in "Paris3232" tab in cell G22 -> hence "Paris32"
No value in "Vancouver43" tab -> hence nothing
Location
Reconcilation needed?
London453[CellA2]
London4 [Cell B2]
New York45
Paris3232
Paris32
Vancouver43
I need a formula that will take the value of one cell and put it in another cell other than the one in which I am typing the formula. I can't think of how to do this.
I have two columns, A & B (no headers), of information. The columns are not identical, and column B has empty cells scattered throughout the column. For each empty cell in column B, I want to copy the same row of information into it from column A, as if I'm just scooting the information in column A that's on the same row as the empty cells in column B.
This is simple with a cut/paste, but when there's thirty empty cells, cutting and pasting takes time. (What's really frustrating is that I've figured out a few formulas that will copy the information from column A but all the information gets pasted in the column in which I'm creating the formula (say, column F, for example). THAT doesn't help me because the empty cells in column b are still empty!)
In column A I have Product Codes, They start at A3 and go down to A169.
I need to have a formula which i can drag across from CQ3 to DH3 which corresponds in the following manner.
CQ3 = A4
CR3 = A5
CS3 = A6
etc etc.
I then want to be able to copy the formula into another place, eg CQ20 = A21, CR20 = A22 etc
I have attached a screen shot.
Untitled-1000.jpg
I have a formula in column J that is the following:
=IF(I541="",IF(ISNUMBER(SEARCH(LEFT(G541,4)&"*"&RIGHT(G541,4),H541)),G541,""),"")
See screenshot:
I need to replace "G542" in the formula above. For example, in cell J541 shown above, I need it to output "1996-1999|2000-2001" instead of "1996-2001" I need the output to include LEFT(G541,4)&"*"&RIGHT(G541,4)
i have a list of names which also contain e mails addresses after the names, they are displayed as (In column B onmy spreedsheet)
Armani Stevens/GB/companyname/GB@soso
what i need from the above is just the name (up tp the first backslash)
so i would need Armani Stevens and disregard the rest.
What i would then need to do is to take the name and then see if the names is in the list which is situated in column A,
so to sum
once i have Armani Stevens extracted from Coulmn B i would want to see if this name is in Column A
Names are obviously of all different lengths and there is always a space after the first name and surname
I have a Date in Cell A1 Page 1 and I need a formula to put in Cell A2 on Page 1 that if there is a "matching" Date in any cell in Row B1-B7 of Page 2 that the data in the corresponding row C1-C7 on Page 2 will be placed back on Page 1 Cell A2.
View 9 Replies View RelatedI need to be able to take someones name which is in a single cell in the format "First Middle Last" (could be more than one middle name) and then split it up so that it fits the following format:
Last name (must be 50 characters)
Middle name (must be 15 characters)
First Name (must be 15 characters)
I'm unsure on the right combination of operators to use to variably concatenate strings in this way. I'm confident I can figure out how to add the white spaces after a name to make it fit the character requirement but I don't know how to split the initial name up into it's 3 parts. Names longer than the character requirement will just be truncated.
Column A
Column B
Column C
Economy
BRITZEconomy
Question
Compact
BRITZCompact
What formula do I need to use in column A to get the desired results as per Column C?
Medium
BRITZMedium
Large
BRITZLarge
Bus
BRITZBus
Bikes
BRITZBikes
4X4
BRITZ4X4
I am looping through and copying screens of mainframe data and appending them to the clipboard. Once done, I start a Notepad and paste it for future massaging. Now I need to perform a check on each screen which requires copying something from the screen, assigning to a variable, and checking it. This overwrites what I've already stored on the clipboard. Is there a way to store what I've got on the clipboard, then use the clipboard for something else, and then reload the clipboard?
View 3 Replies View RelatedI would like for the result of a formula to stay in the cell even after the first cell changes.
in cell G1 I use this formula, if(C1=D10,A1,""), I would like result of formaula to remain in G1 even after C1 is changed.
The data is arranged as such, there are a list of call types and in another column, there are a list of call lengths. Currently I have a COUNTIF which searches the call type column for certain call types ie BA is off peak but this only tells me how many off peak calls they have made, I need to make it tell me how many minutes of off peak calls there are.
IE this is an example
Call Types Call Lengths (minutes)
BA 1
BA 1
BA 3
BA 2
at the moment my formula would only count this as 4 but I need it to calculate it as 7. This is a very big data set containing around 900 entries per sheet.
I have a macro that loops through a column of text and removes all text after a hyphen. Example: AU9929-PK becomes AU9929. I need to set up some exceptions that when it processes certain text it does not truncate it. Example: AU9929-ASST stays as AU9929-ASST. So far there are twelve exceptions but this could increase.
Note: The list which includes shop number, description, sku, sku description, quantity, unit price and more is sorted in a particular order as other operations are performed on it that requires it be in a specific order.
Below I have a table with dates. The actual dates will be added in over time. I want to display the amount of days in the column btwn planned and actual but since it is subtracting a date from a blank cell, the #VALUE function is displayed. What function can I use to tell excel to keep the cell blank until a value other than #VALUE appears.
PLANNED Spud
ACTUAL Spud
Days
5/30/12 12:00 AM
5/30/12 2:30 AM
(0)
12/15/12 12:00 AM
#VALUE!
1/19/13 12:00 AM
#VALUE!
2/23/13 12:00 AM
#VALUE!
I am trying to let the user call my subroutine in the following 5 ways but am lost using string manipulation -
Call FindReplaceAll(C, 123, 1.23) In Col C replace 123 with 1.23
Call FindReplaceAll(2, 123, 1.23) In Row 2 replace 123 with 1.23
Call FindReplaceAll(C:E, 123, 1.23) In Col C-E replace 123 with 1.23
Call FindReplaceAll(2:4, 123, 1.23) In Rows 2-4 replace 123 with 1.23
Call FindReplaceAll(C9:E19, 123, 1.23) In region C9-E9 replace 123 with 1.23
Here is what I am trying to do in psudo
Sub FindReplaceAll(sType As String, sFind As String, sReplace As String)
If sType does not have a colon in it then
If sType = A-IV or sType = a-iv only then
Columns(sType &:& sType).Select
If sType = 1-65000 then
Rows(sType &:& sType).Select
Else
Is it possible to create a formula that looks at a cell (say A20) and if TRUE changes the colour of A22 to green if false to red?
View 9 Replies View RelatedI have test.txt file. The data in test.txt file so messy (there are headers and some data lines start with letter "A" and need to ignore). There's no <TAB> in data file, all are single space and <enter>. I can do nothing about data files.
Task:
Open test.txt file
SUM all product quantity, put result in xls file in A1
SUM all Total price in the txt file, put in xls file (same file) in B1
Close test.txt file
Example:............
Where all question 1 fields are put into a row, 2 in another row, etc (this may be more than 2 sets of questions), grouped by the name field. I have had a look at Pivot tables but they dont seem to do what Im after.
Im Creating a email based on useform control values. Here is a part extract of how im creating it.
View 4 Replies View RelatedI have a sizable spreadsheet, which has one column where the cells are mostly numeric strings of up to 6 numbers - there are variations, which I will mention.
What I need to do, is to treat the numbers as individual figures, and add them, then divide by the number of figures. e.g. cell value '123456' I need to process as (1+2+3+4+5+6)/6.
The variations are that '0' has to be added as 10, and any letters need to be added as 12. '-' and '/' which are the only non-alphanumeric values should be ignored.
My pivot table is not auto summing each column properly, and I can't manually enter a formula to do so. I tried adjusting the settings of the rows and columns through the pivot table wizard, but no luck. Is there a way around this limitation?
View 6 Replies View Relatedi have a worksheet called 'week 1' and want to write vba code so that a new sheet called 'week 2' can be created. However i want to write code so that it doesn't matter what sheet i am on, a new sheet would be created with the name week and the next number up. ie. the last worksheet is 'week 7' then the code should be able to create a new worksheet called 'week 8' ect. I currently have this code but it keeps returning an error message. anyone know how i could do this???
Sub nSheet()
Dim nm, i As Long
nm = Worksheets(Worksheets.Count).name
i = 1
Sheets.Add
Do While WorksheetExists(Left(nm, Len(nm) - 1) & i)
i = i + 1
Loop
ActiveSheet.name = Left(nm, Len(nm) - 1) & i
End Sub
I have a program that updates values quarterly when run. The spreadsheet that it works with also contains a couple of pie charts that correspond to the updated data. the charts only have one series with category values. I recorded a macro to see the code excel uses for creating a new chart and tried to modify the statements to my needs but have been running into runtime error 1004 ("Method ' Cells' of Object '_Global' failed"). The code is as follows I don't have any trouble until the last two statements.
Sub Chart_Updater()
Sheets(Chart1var).Select
With ActiveChart
.HasTitle = True
If Chart1var = "COLI VUL 1 Fund Chart" Then
.ChartTitle.Text = "COLI VUL 1 Allocation by Fund Provider " _
& sday & "-" & sday & "-" & Lyear
ElseIf Chart1var = "COLI VUL 2 Fund Chart" Then
.ChartTitle.Text = "COLI VUL 2 Allocation by Fund Provider " _
& sday & "-" & sday & "-" & Lyear
ElseIf Chart1var = "COLI VUL 7 Fund Chart" Then
.ChartTitle.Text = "COLI VUL 7 Allocation by Fund Provider " _
The variables fundtr1, fundtr2, and emptycolvar are public, and are all returning values, so there is no problem there. Also I can't use ranges like "A1:F7" because the range needs to be variable.
I've created a humongous spreadsheet in Excel- 26 million cells and a file size of about 500mb. The result of these calculations is a pattern derived by conditional formatting. I'd like to get the pattern into Photoshop- It would actually simplify image manipulation in Photoshop to lose the cell data, and the underlying formulas are of no use.
The file seems to be too big to save into a PDF file. Is there another way to get the Image into Photoshop?. The other alternative is to reduce the file size by stripping out unnecessary cell data and formulas without losing the pattern.
I have a table which is extracted from a database using 2 tables.
1 Header and 1 details table.
I want to display the latest date in column E.
As Column A,B,C is the header table and column D,E is the details table.
Can this be done in vba or a pivot?
Data manipulation question here: how to dynamically filter and sort a multi-column data set? My end goal is to be able to (1) quickly collapse all columns into one single column, (2) remove all duplicates, and (3) sort the information in ascending order. A reference sheet is attached in case it's useful.
View 5 Replies View RelatedI am trying to create a macro which gives me an input box. I need to enter 2 numbers into the input box. It would be as follows:
Input Box Msg 1 - "What is your labor cost?" (NUM1)
Input Box Msg 2 - "What is your productivity rate?" (NUM2)
Then I need the macro to take this info and enter it into a formula which changes according to the row which you are on. (I need the macro to work based on which ever cell I currently have selected). The formula would be this if I currently had a cell on row 10 selected:
=(NUM1*(NUM2*$H10))/$H10
So, if I currently had cell L10 selected, and I ran the macro and put 100 in for "NUM1" and 10 for "NUM2" and my sheet had 20 in cell H10, then the macro would enter the number 1,000 into cell L10. If I had L500 selected, then the macro would enter the final number into cell L500 based on what value H500 contained.