I want to copy text to a new spreadsheet only if column A = "47"
The problem is, if I drag the if = formula down in a new spreadsheet, the data will have blank cells and will go down thousands of rows. I want the first "47" to be in the first row, the second "47" to be in the second row of the new spreadsheet and so on. Can this be done?
I have an existing spreadsheet with a column of strings (actually VIN numbers). These numbers correllate to a bunch of text files, that can exist in one of three folders (UsernameDesktop1, 2, or 3) on my desktop. What I need the macro to do is:
1) get the filename from A2 (A1 is a heading row)
2) Find the appropriate text file in one of the three folders
3) Put the folder name into I2
4) Scan the text file for some strings, and copy some data that follows those strings into J2:O2 (I can handle programming this)
5) Close the text file
6) repeat above for the remainder of filenames (about 1800 files)
I'm having a 6000+ records, (contacts DB) exported in Excel 2003 format from MS Outlook.
Except the "First" and "LastName", all other contact elements are in the field "Notes" (which is the BZ column according to the exported outlook layout) multiplied by 6469 (records in total)
Useful information are included !!!
I'll give an example of a record...
Column: BZ, Row: 543 says --> "2110000000-6989000000, 1TOK_TER:17-11-010(25 DAYS LESS),
KATERINA 25 (MANTAS KALNNNNN_HYPERTENSION)1000-150, W:95_105, , HR THE 16wks_US NEFRON OK,
NT OK_B EPIP OK(GOLF BALL)_KAMPILI ORIAKI(DIAITA)_DOPPLER OK, O+,TEST OK(TOX_), , , , , , "
Now, I want every time to take the part of the text says "TER:something..." (part of which is date, but not every time with the known format dd-mm-yyyy, as you see here is yyy, followed by something else, with parenthesis here and maybe more data) and copy it in a new cell..., e.g:CO Column, same Row...
Above and every field which by the way is formatted as General (and it is text mainly) are made by merging older excel fields where data laid here and there, that's why you see the commas...with the method of a module with the following code:
Function MyMerge(Rng As Range)
For Each Cell In Rng
Temp = Temp & Cell.Value & ", "
Next Cell
Temp = Mid(Temp, 1, Len(Temp) - 2)
MyMerge = Temp
End Function
Note1:Records with the above string (TER:dd-mm-yyy) are 771 from 6469.
Note2: As an alternative solution I can see an extraction of the TER:dd-mm-yyy string and the copy in a new place, like the:CO Column, same Row...
I have one column full of Item #s and Descriptions. I have another column that I want JUST the Item# in it.
For example:
Column 1:
Gyroscope REF#1234 Bike Thing
Column 2 Should Be:
1234
Column 1:
BallWall Bikes Ret# 12456 Helmet Thing
Column 2S hould Be:
12456
I don't know how to do this (I do know how to do VBA mildly, and I am pretty good with formulas). I also have a database of every Item# that could be in that cell.
I have a spreadsheet where a cell equals another cell. but i want to convert it to a value.
how can i do that for a range of rows. i know how to do that for one cell.
Code:
If Range("B4:B25").Value = "" Then
Else
Range("B4:B25").Value = Range("B4:B25").Value
End If
Here is my dilemma: I am opening an HTML file with Excel. There are text boxes that appear in excel from the HTML file with text in them. I would like to write a macro to copy the text from each text box and paste it into a cell. I have attached the excel file with the html text boxes in question.
View 4 Replies View RelatedHere's an interesting one:
A1 contains a text string which is both bold and unbold (
B1 is blank
I need a macro which scans the cells with text and copies the BOLD portions of the text string into the adjacent cell.
Example:
A1
The sky is blue
Macro is run
B1
sky blue
1. Search an excel sheet "column" for a particular type of text and insert values based on that text (if found) in another column.
e.g I have column A1 to A10 with different types of text. I would like to search for the keyword "Risk is high" OR "Risk=High" for each cell in the column and insert a "1" if found beside it's corresponding "B" column. If not found, I would like to insert a "0".
So, if the text "Risk is high" OR "Risk=High" was only found in A6, I would like B6 to be "1". Rest of the values in the B column would be "0's", since the text was not found in any of the other cells.
I have a macro which imports data from a mainframe dump text file and performs 'Text to Columns' on the imported data so that formula in the spreadsheet can act on the data. The code works perfectly well when I use it, but if a different user logs on and performs exactly the same mainframe dump and import macro the Text to Columns action splits the raw data in a different way and the result is that the split renders the formulae useless.
I've experimented a little and for some reason it appears that the 'Field Info' parameters which are produced when the Text to Columns function is recorded in a macro differ between users even though the raw data is exactly the same.
FieldInfo:= _
Array(Array(0, 1), Array(18, 1), Array(35, 1), Array(56, 1), Array(70, 1), Array(88, 1), _
Array(102, 1))
I have a list of text quotes in column A. I then have column B which will have a Y entered if the quote is used in a presentation. I currently have around 100 quotes.
On a separate sheet, I want to be able to effectively say in one cell - If column B has text in it, copy the quote from column A. But the difficult part is how do I make this happen so that I can have multiple quotes being pulled into one cell?
I'm trying to get something like - If a cell contains certain text AND certain text then display set text OR If a cell contain certain text AND certain text then display set text.
For example:
Vauxhall Corsa 1.5Litre Petrol = vauxhall corsa petrol
Vauxhall Corsa 1.5Litre Deisel = vauxhall corsa Deisel
Vauxhall Nova 1 litre Petrol = vauxhall nova petrol
So it the formula is effectively searching for the car type and the fuel type and then returning the relevant set text. So far I'ver only been able to find IF functions criteria to identify one feature of the text.
I'm thinking it's got to be variation on =IF(ISNUMBER(FIND("Vauxhall Corsa",A1)),"Vauxhall Corsa Petrol") but there should be something else in there like =IF(ISNUMBER(FIND("Vauxhall Corsa"&"Petrol",A1)),"Vauxhall Corsa Petrol")
I know this is wrong but just trying illustrate my thinking. I need it to pick up vauxhall corsa AND petrol. I understand that I would have to stick a lot of IF functions together in a string, but that is fine as long as I can ask it to pick certain text AND certain text within a cell.
I have 10 cells in a column. I have a drop down list in each that is the same in each. If all 10 cells have the same item selected from the drop down list, I want a separate cell to list Yes or No. I've tried a few variations with no luck.
View 4 Replies View RelatedIn a workbook I need to find all items belonging to a certain person and then copy those items to a new sheet, possibly separated by commas. To make it simple, see example :
Sheet 1 :
Fruit owner APPLEPEAR GRAPE BANANA
Fruit ownersJimJulia MirandaJason
JuliaMatthew MatthewJim
Sheet 2 : this is where I want to put the results
JimApple, Banana
JuliaPear, Apple
JasonBanana
MirandaGrape
MatthewPear, Grape
Below is the text that I use to copy AL if AJ is not blank:
If Range("AJ2").Value <> "" Then
Range("AL2").Select
Selection.Copy
Range("AL2:AL65000").Select
ActiveSheet.Paste
What I want it to do is copy the formula out of AL2 down to as many cells necessary to populate column AL as long as AJ is not blank. If AJ is not blank I want it copy AL2 down as much as needed to fill column AL. I currently have it running to AL65000 but don't want to leave the formula in that many cells. If there is nothing in AJ I don't want anything to be in column AL.
End If
End Sub
I am not familiar with using VBA and believe this could solve my problem, I would be glad of any assistance.
I am trying to copy text from sheet 1 of my workbook onto sheet two of my workbook, whilst consolidating into a nice neat list omitting any blank cells.
The text appears on multiple tables each table has 10 rows throughout worksheet 1
A1 = Good
A2 = Bad
" "
A10 = Fantastic
A15 = Good
A2 = Bad
A10 = Fantastic
I hope this makes sense, I do have a formula that will do this =IF(ROW()-ROW($E$500:$E$509)+1>ROWS($K$35:$K$44)-COUNTBLANK($K$35:$K$44),"",INDIRECT(ADDRESS(SMALL((IF($K$35:$K$44<>"",ROW($K$35:$K$44),ROW()+ROWS($K $35:$K$44))),ROW()-ROW($E$500:$E$509)+1),COLUMN($K$35:$K$44),4))) however I have multiple tables and the formula greatly reduces the speed at which the sheet is working.
On sheet1 have multiple groups of cells with drop down lists all with the default word "None" selected. After the user has gone through and changed some of these menus to other values in the lists, i am looking to copy the text only in the cells that have been changed to certain locations on sheet2. See attached spreadsheet for a better idea. If any additional info is needed.
View 5 Replies View RelatedThe problem is, these identifiers are in no discernable or predictable pattern. I cannot open the page directly in excel, nor can I use the Import Data from Web function (2007) ... results are simply a blank page.
What I thought I could do, then, is automate the procedure that obtains the source code, which I can parse and look for the current date. Once I have the line with the current date, I can extract the unique identifier, then paste it back into a string and resubmit to the browser.
I just can't figure out how to get to the source code... anybody out there have a way to get to it? Since this is going to ultimately be distributed to 20 or so analysts in different countries, I don't think I can use other tools (like the HTML Extractor from Iconico).
I'm running Excel 2003. I have a workbook and in Column L I have a text description that includes one or more 5 or 8 digit catalog id's. What I want to do, using a function or vba, is to copy the id and paste it into Column M.
In cases where there are multiple id's I would want all of them to be entered into the same cell.
Example:
00123456
12345
All of the 8 digit id's have either 2 or 3 leading zereos that must be retained.
I've attached a sample spreadsheet. Thanks in advance for any pointers and/or assistance.
I have written below code
Sheet1.Cells(1,2)=Shett2.Cells(2,1)
and my sheet1 cell has a value which is bolded and after running above code the sheet2 cell is displaying unbolded value
how to copy the format as well
I have the follwoing data:
A B
1 Job No. Job Date
2 33625 12/7/2011
3 Davis: Please contact Shine @ Prudential Insurance re: copy
4 336173/9/2012
5 Gaskill: Please contact Swiers @ SEC re: copy sale; emailed
and so on...
I would like to copy the text from B3, B5,B7 and so on to a a new column starting in C2 and then delete all of the empty rows.
I am trying to make a macro where when you click the Export button Macro it just takes anything that has a Yes in Column A2:A100
View 9 Replies View Relatedi have this picture of a list of chores..its about 150 different chores long
i can type all of it in..that would take a long time..
but anyway i was wondering if there was a way to take the picture and turn it into words..
Ex.
A1 Sweep Kitchen Floor
A2 Mop Kitchen Floor
A3 Empty Dishwasher
A4 Fill Dishwasher
method to copy some data to a variable place in the worksheet.
Column A has team names with data in columns B,C,D and E.
I would like to group together the data for each teamname - so have the team names in Cells H5,H10,H15 and H20.
I have attached some sample data
I have a column list that I need to seperate. The Text has both a title and location separated by an = sign. The cells look like this...
BlahBlahBlah = Location1
I would like to take the text after the = sign and place it in the next column so that all that is in it is...
Location1
The Location varies so the RIGHT function won't work.
My file is included with alot of cell in the following format:
aaaa. bbbbbbb (ccccccc)
I want to copy the first part (aaaa) into column A and to copy the second part (bbbbbbb) in column B. Both part can vary in length: in this case their length were 4 and 7 but basically they can have any length. How can i do this. See also the attached file.
clicking the platform button on sheet 2 runs a module that plots trains waiting in platforms 1-15 the value are got from sheet 1 columns
a = arrive
b = depart
c = platform
ideally i now need these shaded cells to show the departure code of the train they represent and i need every cell of the train to show the code (eg all 60 cells if the train stays for 1 hour). this is because some trains arrive as 1 but detach and depart as 2 or more trains. this would show where 1 train leaves but another part remains to leave later under another headcode, the codes i need placing over the shaded cells are the code in column I sheet 1 (ideally written in the invert colour of the shaded cell so they can be read easily) as an example of what i'm after i've manually added the code for 1 train in black on sheet 2 for the 1st train in the list http://us.f13.yahoofs.com/bc/466da4e...i.abGBkbJbSCH_
I want to do something similar but it is looking for values between parentheses "()" and won't work with the VB suggested. I've seen some other formulas that do find the content within the parentheses but returns #VALUE! if there is not a parentheses in the field. I'd like it to copy the original value of A2 into B2 for those instances. PROBLEM: Database with First Names in A2 where some have "nicknames" entered within () and I need a clean column of values to use. "IF A2 contains () then copy (contents) into B2, else copy A2" is how I'd write in english... not sure how to do correctly in code/vb.
VB:
Sub PartialText()
Dim MyValue$
Dim ChangedValue$
[Code]....
I am having a table like this
col1
col2
col3
Col4
[Code].....
I need to test each cell for text and to copy that text into another place (lets say sheet 2). Size of table is variable and here is what i come up up till now
VB:
Sub MoveText()
Dim WBook As Workbook
Dim WSheet As Worksheet
[Code].....
Is there a way of Copying the Characters in a Cell but excluding the text from the copy.
EG:
305 GP
1,328 GP
10,250 GP
1,337 GP
But i only want the numbers and not the GP.
GP is Always at the end.
I have an Excel problem which I have been trying to solve for a while but so far I have had no luck.
I need a VBA script that can copy only text that has a font color of red from a cell. The cell has both black and red text. I then need to copy the result into an adjacent cell.
An Example (A1):-
This is an example of a cells content.
It has both red and black text.
I want the macro to search through column A and copy only the text in each cell that is red.
The result in (B1) will be:-
example of a cells
red