I have a branch name in column D, which varies in length and want to combine this with Text
For eg C672 may contain Bedforview and I want to combine this with Net profit Workings
I have tried to use the following formula, but it comes up as text. I also need to incorporate the len function as the length of the regions vary in column C
="&(left(C672,10) "&"Net Profit Workings"
I need to combine data from two adjacent columns into one in a condensed version of a spreadsheet. As the spreadsheet itself is quite big (over 20,000 rows) I was wondering if there was a quick and efficient way to doing this.
Here is a sample of what I'm working on (I know this would be better off as an image but unfortunately Photobucket is blocked at work):
Contract: Apr-2007 4000 Calls
TimeStampDeliveryDateStrike
18-Apr-07Apr-074000
19-Apr-07Apr-074000
20-Apr-07Apr-074000
Contract: Apr-2007 4000 Puts
TimeStampDeliveryDateStrike
18-Apr-07Apr-074000
19-Apr-07Apr-074000..............
concatenating a variable range of data. Attached are examples of what I have and what the desired outcome is.
View 2 Replies View RelatedI'm getting the generic 1004 error on the Range.FormulaR1C1 line, but I can't seem to see the problem.
Code:
Sub UpdateFormulas()
Dim stockFund As String
For i = 2 To finalRow
[Code]....
The mouse-over on the stockFund variable in that last line shows the correct cell address as the value and I checked the If statement to ensure it actually finds the number. I would guess that it would be a syntax error with that line, but it looks correct to me.
I have an excel spreadsheet that should have one record for each artifact in a museum collection. The problem is that the museum has consolidated this information from several different sources into one spreadsheet and now there are many duplicate records. They want all the duplicate records removed so that there is just one record for each artifact, BUT there may be different pieces of information in each of the duplicate records. So I want to do the following:
- sort records based on Accession Number (column A)
- find duplicate Accession Number records
- determine which fields (columns) within a duplicate record are unique and concatenate those entries into one master record for each Accession Number
- delete the duplicate Accession Number records
In the attached sample sheet, for Accession Number 66-1-100, we have 6 duplicate records. In the columns, we have information which in some of the records is duplicated, in some it is unique and in some it is missing completely. The museum wants just one master record for each Accession Number and they want all the data from the duplicate records concatenated into one and all the duplicates and blanks discarded.
What I've done so far:............
I have 3 columns.
Column A = 1 d 03:32:42
Column B = MID(A2,1,FIND(" d ",A2,1))
Column C = IF(OR(B2 > 2, B2 = 2)," 2 days or more", " OK")
I used MID(A2,1,FIND(" d ",A2,1)) to extract the days out of a a Date + Time string.
I got column B (days)correct. But in Column C,this does not seems to work. ( where i want to indicate if it is OK or EQ /more than 2 days.)
=IF(OR(B2 > 2, B2 = 2)," 2 days or more", " OK"
S 03 01/22/08 03:09:55 3 ---- ----
A 03 01/22/08 03:10:01 4 .175 .175
P 03 01/22/08 03:10:15 3 ---- ----
A 03 01/22/08 03:19:09 3 .107 .107
A 03 01/22/08 03:19:34 4 .360 .360
A 03 01/22/08 03:19:53 1 .157 .157
the first single letter is the test evaluation code - "A" for accept - "P" for Stopped - "S" Severe Leak - Among others.
the "03" stands for the program number which there are three of - "01" "02" "03" --- These can be changed at anytime but by the user of the tester.
then of course the date
the the time - military style
the next number represents the test station - station 1,2,3, or 4
and the last group stands for the "leak rate"
------------
A couple questions I have...
1.) right now all the data is being placed into Cell A1 only. I need to have each data string in its own row. How can this be done.
2.) It would be great if I would be able to break up the string into separate columns. -- Like "P" in column A. "03" in column B. date in column C. etc...
3.) And maybe if those are simple enough to accomplish would I be able to separate into different sheets? preferable separate them by the "station number" which is the 5th group in the data string.
I maintain a spreadsheet to track monthly sales of a few thousand items (see attached sample data). I'd like to have a formula that would sum only the last 12 months in the range of data. It would need to ignore all of the data before and the blank cells after the 12 months.
It's difficult to update the range each month for all of the products.
I have a string (as below - Call them A1:A4) which I would like to seperate into 4 columns (Call them B1:E4).
I have successfully seperated the first part using MID (It's always 5 digits) but the second part has a varing length which then impacts on the third and fourth parts of the string.... Any ideas?
87261 WIMBLEDON 10:08 10:10
87169 NEWMALDEN PASS 10:13
87171 SURBITON PASS 10:15
87177 HMPTNCTJN PASS 10:16
To add to this I am using the POCKET PC version of Excel which does not have all functions so at the moment I am limited to which functions I can use (Can you add functions to the PPC?).
I have strings of data pumped out of a database like so "!OV !IPV ABL (850) !VL SM (150) !AD !PW !QT CC (-350)" If an exclamation point is listed, then no value follows however if no exclamation point is present, then each item will be followed by a value. I am trying to break this data out into a table. I am not sure if this is even possible. I am also attaching an example.
View 5 Replies View RelatedI am trying to extract the ounces (OZ) data from a string: example, BOX 15OZ 1819106287, CONTAINER 12.3OZ 1818176234. I need everything from OZ prior until there is a space.
I.e.
15OZ
12.3OZ
I have a file which contains one field showing all of the changes to our data. It shows the Field Name followed by a colon: then the Before value followed by a line | then the After value. If multiple fields changed then they are separated by a semicolon;.
I am interest in extracting the Before and After values for changes to the MP.COST field only. Here are examples of the data:
A1 = "MP.COST :4.00|3.50;MP.FLAG :Y|N;"
A2 = "MP.COST :4.25|4.12500;"
A3 = "MP.CODE :125064|200009;MP.COST :4.79|4.66000;"
For A1 I want to pull back 4.00 and 3.50
For A2 I want to pull back 4.25 and 4.12500
For A3 I want to pull back 4.79 and 4.66000
These are all of the Before and After values associated to MP.COST.
Kow I can accomplish this either through an excel formula or piece of code?
I have two variables 1st is a ETD (Estimated Time of Departure), ie 0510 which is string. 2nd is a KRT (Kitchen Ready Time), ie 03:00 which is date, time.
I need to put these two items in a cell as string, ie 0510 03:00
I tried .cells(rowindex, column) = me.etd & " " & me.krt
and I get 0510 0.1223243
then I tried
I tried .cells(rowindex, column) = me.etd & " " & format(me.krt,"hh:nn")
and I get compile error: expected function or varible
So how do I set the KRT to "hh:nn" and combine it with the string ETD to show 0510 03:00
I recently had to convert a text file to an Excel file. The text file had to be converted as delimited data since the fixed width column could not convert correctly.
Now that I have the data converted, I have several rows of data strings.
The data I have looks similar to the examples below:
41 AAITQ08082901PER0041 ABC v1.0 NES ABC P111 - Blue 7706 6547 Yes No 140 5 AAITQ08082901PER0005 ABC v1.0 NEG ABC Z113 - Silver R 9222 2743 Yes No 156123 My question is, how do I extract the numbers that follow the word "No" at the end of each string?
Assuming the data starts in column A, is there a formula that I could type into column B that would allow me to return the value of those specific numbers?
I have info displayed like this in cell c3
Bat 6Fm C6Hc 1K
Asc 8Gd C13yG1 198K
Chs 10GS C13yG3 34K
What I want is in cell J3 to return the first 3 letters and the numbers next to them three letters so in the example above it would return
Bat 6
Asc 8
Chs 10
I have an example sheet attached with the value I need manually typed in B and C.
What I would like is the formula to do this without me having too manually (as the full workbook has over 5,000 lines)
The ID will be between 1 and 4 digits and always in the same position
The name cane vary in length, and also be in a different position (depending on the length of the ID)
I have put a formula in excel to count how many times the word 'administration' appears in a column:
=COUNTIF(K2:K99,"Administration")
Unfortunately, the output that I am searching has mulitple words in it, separated with a colon and no space. My formula skips the count if the word Administration is not completely on it's own
e.g. Administration counts 1
Administration;Cardiology does not count
I have a few hundred rows of text in the fomat below: 1.23456 xxxxxxxxxxxxxxxxxxxx. The “x’s” represent text which is unique to each row. what the formula I need to extract the number (1.23456) at the start of the string? To complicate things the number may be reported to any number of decimal places, so the formula needs to be able to extract the first block of digits at the start of each row and report it as a number that can be used in calculations.
View 2 Replies View RelatedI want to extract data from array string and then sum the values. For reference attaching the excel.
View 14 Replies View Relatedhow to get a validation list to a string. I know how to do it with a formula but I do not know how to do it with a validation list. I tried this and it errors out on the first line saying not a supported method:
VDL1 = Worksheets("new").Range("J3").Validation
Worksheets("Lookup").Range("L6") = _
Worksheets("Lookup").Range("Y1") + _
VDL1 + Worksheets("Lookup").Range("Z1")
I have a spreadsheet of location names that look like this:
UT-04560803-DF3
AZ-57611564-S32
etc...
I'm looking for a way to pull the last four digits out of the middle section of these entries, so that I end up with:
0803
1564
etc...
I am accessing a ratings system for horse racing and trying to extract the top-rated runners for each race using a database query. The problem is that every runner and rating is in one cell and separated by spaces. I have tried using text to columns but obviously can't use space as a delimiter as the horse names have spaces in them sometimes. The one cell basically contains the following string...
COCONUT MOON 100 CARIBBEAN CORAL 100 HOWARDS TIPPLE 97
A2 = COCONUT MOON
B2 = 100
A3 = CARIBBEAN CORAL
B3 = 100
A4 = HOWARDS TIPPLE
B4 = 97
So that I have each horse and it's rating alongside it in the adjoining cell, I figure I somehow need to use LEN, RIGHT, LEFT or something but can't think how to do this
I need to be able to take a string & remove all non numeric data. If I had "(123) 456-7890" I would want it to return "1234567890".
View 6 Replies View RelatedI have a sheet that I need to combine data from three cells into one and then get rid of original data.
Data to be combined:
A1=650
B1=1234567
C1=1998
D1=Desired Output
Desired Output:
A1=
B1=
C1=
D1=650-1234567-1XXX
I have a string like this:
VB:
test = "banana|apple|limon"
I did this:
VB:
test_2 = split(test,"|")
The code returned the test_2 var like a matrix with 3 data inside.
But when I try to copy the data inside with:
VB:
For i = 0 To UBound(teste_2)
test_3 = teste_2(i)
Next i
The code editor returns a ByRef error.
How to solve?
how to figure it out this lookup problem (lookup using partial string of match)...
View 5 Replies View RelatedI am trying to extract data from a text string by using formulas.
View 6 Replies View RelatedI have the data string below:
Career:25: 1-0-2 $13,765
I would like to extract the 1 between the : and - and as a seperate extraction would like te 2 between the - and the $ I have tried a few things but end up with the - as the length of the data changes
I'm currently working on a spreadsheet (see attached) which will have both numeric and text string data. This will be sorted by calender month and linked to another spreadsheet.
Could someone please list 2 functions for me.
The 1st would allow act like a filter allowing only data to be counted for a selected month from a drop down list.
The 2nd allows a counting of pre-set (drop down list) text strings to be counted.
Sorry seems a bit complicated and really I should have either a created a pivot report or used access but I'd like to continue with Excel for this exercise.
I have a worksheet with over 10,000 records. The column that lists where a person is willing to relocate can have up to 60 city/state entries in one cell.
Here is an example of what appears in one cell - this is exactly how it appears:
ASAI Los Angeles (XX , CA
DFO Pacific (XX ONLY), CA
DFO Pacific Area Analyst Laguna Niguel (XX ONLY), CA
SAI Los Angeles (XX ONLY), CA
Ldr Los Angeles El Segundo POD (XX ONLY), CA
Ldr Los Angeles Long Beach POD (XX ONLY), CA
Ldr Los Angeles POD (XX ONLY), CA
Senior Ldr (XXXX) Washington (XX ONLY), DC
What I need to do is be able to sort on city and state, so I wanted to be able to extract and separate the city and state. I tried using a find/replace (CTRL J) to enter a semicolon between each entry and thought I could do text to columns to separate, but that doesn't work.
How I could extract this information? Notice that the first entry is missing ) - that is throughout the records.