I have data in the following format-----
Name : XYZ
City : ABC
Place : sdfg
Error :
price : [X]
cost : [ ]
time : [ ]
[code]....
I want only name, place,desc and under error, i want that type which is marked cross in the brackets(In the above example it is price ) .
name, place,desc,error should be pasted to separate columns in second excel sheet.
I was trying to define a function that could fetch data from MS Access Db to Excel using the below code (from a very old thread in Mr Excel) but am getting no return value in Excel. The value displayed is #NAME? ...
View 9 Replies View Relatedimporting some selective data from txt file to excel. Only the following rows I need to pull out from text file into the Excel i.e. Project row, installation row, and two rows immediately below the installation row. This is my logical procedure..
1. Macro look for the rows that contains the word "PROJECT"
2. Open an excel file
3. Pull the row from txt file to this new excel file.
4. Macro look for the rows that contains the word "INSTALLATION"
3. Pull that row with INSTALLATION AS WELL AS Two rows immediately below the INSTALLATION row.
I have a ton of data coming into excel. I'm making a Pivot table of this.
From this tables everything goes to another sheet for a specific month. Right now January only.
The data will contain several of months later on in the future.
Where I can set the pivot to see the february date and insert into the right cells?
Excel01-file contains the Pivot table setup.
Excel02-file is the place where the pivot should insert the data specific to each month taken out from the data file in Excel03_data-file.
I need hyperlink to pdf (as this is given in one block how to give hyper link to pdf file).
I want to give give pdf hyperlink in in excel to locate the pdf file with specific page or topic number in pdf file (like pdf file name is 'ABC' and page number '10')
Can i give this type of specific link in excel?
I have a worksheet that contains two basic columns of data, A and B. What I would like to do is based on the value in column A, I would like to copy the contents of column B to a textfile (preserving the basic line structure and hopefully without any extra characters like quotation marks attached). So for all the values in column A = 'Account', Id like to take all the corresponding values in Column E (for example) and save them to the same file, preferably where the filename itself as 'Account.xyz'. And I would like to do this for all unique values in Column A, that is do it for 'Account' and 'AccountPrivilege' etc. Note Column A is essentially presorted alphabetically already. There are actually 1000s of rows with hundreds of unique A values so Id like to find a way to automate this process.
A
B
1
Account
Company Information
2
Account
Company
[Code] ........
I have created a macro in excel 2010 which enable the file to save (extract) data into separate location and name. The vba code for macro is as follows: Question: How can I save this workbook with reference to the value containing in cell B2? (it is named temporary now - as defined in the code)
Sub aaa()
'
' aaa Macro
'
[Code].....
I work for an environmental company and we do emission testing. We specifically use the analysis software CEMsoft and ProRATA. After each test/run we get a text file (.txt) that we print out which then has to put into a specific pre-made excel data file.
I am wondering if there is a way to export the data from the .txt file and into the specific fields within the excel sheet. Can I encode the .txt or excel file to do it for me? Trying to eliminate the need to manually put in the data from the printed out sheet.
I have a delivery time programm (clients to be delivered the next day) which I would like to run every night at a specific time. Is there any MS tool to trigger the Excel file? Or do we have to use the "old" batch file ( if yes, pls send example)?
View 2 Replies View RelatedI have a requirement to search workbooks in a particular folder with specific string in file name. For example, let us assume I need to find a file which contains the name 'RR' in it. The position of 'RR' will vary with files i.e. 'RR' might be present either in the beginning, middle or at the end of file name. All I wanted is to search for file with 'RR' and do some activity and close the file and then goto next file. Similarly, the next search has to be performed with the files containing the name 'BB' in it.
View 1 Replies View RelatedI am using Excel 2007. I have pasted my code below. It works fine unless it has a space, in which case it inserts %20. In this case, sRange2 = ActiveSheet.Range("E11") will always have a space in it since this is where i store a job's name.
Sub Macro1()
Dim sRange1 As String
Dim sRange2 As String
Dim sRange3 As String
Dim sRange4 As String
Dim sFullPath As String
[code]....
I have a spreadsheet witht the following design:
Column A = Date
Column B = Value 1
Column C = Value 2
I have a folder with excel files named by date (e.g. 081212 = 8th December 12). Within each of those files is Value 1 and Value 2. They are always in the same cell (B6= Value 1, B16= Value 2).
How can i create a macro/script where the Values in Column B and Column C in my spreadsheet are automatically updated where there is a Date in Column A but no values in Column B or C?
In other words, i need the script to read the date in Column A and if Column B and Column C are blank, then it needs to find the corresponding date excel file and copy Value 1 and Value 2 into the cells in my spreadsheet.
How to get "some specific holidays" highlighted by doing conditional formatting in series of "date columns"?? I got to know how to get "sunday" highlighted...but again problem with specific holidays...
View 9 Replies View RelatedI need to copy a specific column range K18:M180 to only columns that have an "X" on a specific row. Below is my small example.
In this example it would be in columns OPQ and WXY
Excel 2007IJKLMNOPQRSTUVWXY
121314xxxxxx1516$Units$/Units$Units$/Units$Units$/Units$Units$/Units
1718aaa180.0 0.00 19bbb0.0 0.00 20ccc1.6 0.11 14.74 21
ddd9.0 0.53 16.93 22eee7.3 0.45 16.22 232425Sheet1
Basically I have a big table containing several columns for the different quarters. (Each quarter has several columns which are not all next to each other)
I would like to use 4 checkboxes which the user can select the show or hide the columns for that quarter. E.g. if Q1 and Q3 are checked only those are visible)
(if quarter 1 clicked than all columns are visible if unclicked hidden).
The code I have written workes well for the first checkbox (Q1), but for the other quarters (Q2, Q3 and Q4) the columns get hidden when checked (which should be the other way round) and don't get unhidden when unchecked.
Sub CheckboxQone()
If Range("$A$1").Value = True Then
Call showQ1
Else: Range("$A$1").Value = False
Call hideQ1
[Code] ...........
If I have 2 excel files. One with data about electronic equipment and one with more general counting data, i will explain..
For example the electronic equipment excel file contains data about notebooks, desktops, epads etcetera and also prices and how old they are etcetera. The excel file also has a column for serial number, so for example a notebook serial number looks like this: 23N34ERT3 and an epad SN looks like 25OKE445EE. IF i filter the SN on text and begins with: ??N then it will show me only notebook data(because the N stands for notebook). Same if I only want the Epad I just filter on ??OK, so i get all the epad data.
now for the general counting data file, in this file i actually want to put data which i get from the other electronic equipment file. For example i want this data to be retrieved:
a. total number of rows of notebooks from the electronic equipment file
b. total number of rows of epads from the electronic equipment file
c. how many rows there are for notebook that are 0 - 1 years old(in electronic equipment file there will be a column called product_Years so in this column you have data like: 1,4,12,3) + that are from model: A from the electronic equipment file
d.how many rows there are for notebook that are 2-3 years old + are from model:A from the electronic equipment file and then going on for 3-4 years model:SD etc......
e. in the electronic equipment file there are prices for each model, i also want to calculate the prices for each rows which i get here in the list above.. These rows must be calculated with prices from electronic equipment file
Is there any easier way than constant filtering and copy pasting the data?
Use filter for 2 columns of a CSV file, column A and Column B for certain words. Say I apply a text filter: contains "word1" or contains "word2" to Column A. Then I get the results. After that, I remove the filter to Column A then apply the same filter to Column B. The thing is, I don't get the results in Column B, instead, I get the cells in Column A wherein Column B is filtered PHOTOS BELOW:
The data that I get for this filter are:
And Word1 you
Word2 is blank
Apply filter to Column B, get the cells filtered in Column A:
The data that get for this filter are from Column A (which are cells filtered from Column B filter)
And Word1 you
Is the blah
Word is all
The results should in a single Column for both filter:
I have more than 100 files that needs to filtered this way, is there any way I can do this automatically?
Here is what the file looks like:
The results I hope would be possible:
Automatically without having to open all the files and have the results of all of it in 1 single Excel file?
----or----
If the above is not possible, open each file and automatically apply the filter into a column in just 1 click and without having to type the same filter for each column.
I found a code to combine all CSV files into 1 Excel file, but half of my files has more than 500k rows and the Excel's limit for rows is around 1million+.
I have two separate worksheets:
I'm trying to find a formula that looks at Column A on both sheets (each client is allocated a unique number) and if they match enter in column D of the referral sheet the month they were seen but only if it is a 1st contact (appt type on column D of contact sheet)
Referral
A
B
C
[Code]....
way to do the calculation using Excel 2003
When I enter number in cells the row that i am on is highlighted, this is standard.
Is it possible to have the same thing happen but highlight a specific column as I go down rather than the row number instead?
I need a Macro that will search all of the cells for specific text, and delete all cells containing the text. I prefer to make a list of the specific texts and run macro to find all of them and delete all cells containing the text. I need to be able to add some more text into the list in future.
View 9 Replies View RelatedI have two Spreadsheets on Excel 2003, one provided by a service supplier (i), and one from my own system (ii).
I need a macro attached to spreadsheet ii that looks for data matches between the F column of my spreadsheet, and the C column of the suppliers. Where data matches (i.e. if cell C42 in spreadsheet ii is the same as cell F23 in spreadsheet i) it transfers the contents of the A column of spreadhseet i and places it in the corresponding cell in the C column of spreadsheet ii.
Example 1 : In spreadsheet i cell C20=BN213YD. In spreadhseet ii cell F55=BN213YD. Therefore, the macro takes the contents of cell A20 in spreadsheet i and places it in cell C55 of spreadheet ii
Example 2 : In spreadsheet i Cell C75 = TN142XS. In spreadsheet ii this data is not found. The macro therefore takes no further action.
Basically, the suppliers spreadsheet has less information per row, but more entries overall. My spreadsheet is lacking a lot of suppliers references, but has more comprehensive information elsewhere. Until now i have been doing a manual search of the postcodes between the spreadsheets and rectifying them manually, but this is taking longer and longer as the spreadsheets grow.
I have 2 workbooks in Excel 2010, each contain just 1 sheet. (see attached) I need to compare on sheet 1, cell D1 and column A:A (this column will be much longer), with the data in columns C:C & A:A on sheet 2, if a corresponding match is found, the data contained in column D on the same row on sheet 2 is written to the cell with the matching data in sheet 1.
My attempt is in cell D2 on sheet1.
I have some daily text files in a folder (so about 30 of them each month), which in the end of month, I need to open them up in excel, format them so that I can use the information for my analysis.
I would like to create a macro, to quickly open them all up at once and save them each individually in .xls or .xlsm format.
I am new to VBA and after some research online, I was able to have the files open with the following code. but now I don't know how to proceed further to save them one by one with the same name but in .xls or .xlsm format.
Sub Opentxtfiles()
Dim MyFolder As String
Dim myfile As String
[Code].....
How we can select specific cells in the filtered data using VBA in Excel 2010.
I need to select 10th column, 5th row data .. or 10th column, 6th row data .. or 13th column, 8th row data...
How to select this data using VBA.. I am struck here .. If I give the below code.. the hidden cells inbetween the filtered data is getting selected.. only the first row is getting selected correctly.. i.e, ..rnVisible(1, 2).Select .. If I change the row like rnVisible(2, 12).Select its selecting the hidden cells ..
Dim rnVisible As Range
Set rnVisible = ActiveSheet.Rows("2:10000").SpecialCells(xlCellTypeVisible)
rnVisible(2, 12).Select
MsgBox ActiveCell.Address
[Code ........
I have a excel file having some data and also I am having PDF file having some data, now I want to match the data in the excel file with the data in the PDF file.
Can we match excel file with pdf files?, this for comparing the data in the excel with pdf file having the data. Is it possible to do it?
We have a Job List, which is a workbook dedicated to all the jobs we have been involved in this company. The Job List has information such as job numbers, job names, client etc etc. For this forum the information of interest is job number an job names. In a seperate workbook is our timesheet. Is it possible that when i type a job number, in say cell A1, that the job number is recognised and then corresponding job name will be fetched from the Job List workbook and placed in cell A2?
View 3 Replies View RelatedI have list of url in a column. I want to fetch data from all the links and store it in a excel sheet.
I have written code, the code fetches only 1 links data at a time. I want to loop that vba macro and fetch all the data at a time.
I have all my data on an Excel2000 spreadsheet and want to save it or export it out as text with just a space between each piece of data.
View 12 Replies View RelatedWhat I'm trying to do is, create a formula that works like vlookup, except I want to return the value of the cell that is a specified number of cells below the looked-up data in a single column array. So, let's say cell A1 reads "Bacon". In B1, I'd like to put a formula which searches the array for the cell that contains "Bacon" and then returns the value of the cell underneath, let's say the value is "Eggs". Then in C1, I'd like to put another formula which also looks up "Bacon" in the array, then returns the value of the cell that is down two cells from the cell that contains "Bacon", let's say the value is "Milk". And then in D1, a formula that returns the value 3 cells below "Bacon", and so on. All the values here will be text, and not sorted in any specific order. I'll be using Excel 2010.
Please take a look at the attached sheet if it clarifies things.
Is there anyway to read a specific cell from many excel files automatically and add them to a new workbook?
I tried to link that specific cell to a new workbook manually but now I have a problem: If I change the name of any workbook that is linked to the new workbook, Excel can't update the new file name