I have a table of data on 20 employee performance stats for my department. It is in the format of col A names and corresponding values for multiple skills and performance data from Col B to M in rows. I have a worksheet for each employee within the workbook. What I would like to do is to be able to extract the individual row of data to each employee sheet. I have played with OFFSET but can't get it to work other than in a column..
View 9 Repliesone of my suppliers can only provide me with a basic .xls product list as shown in the attached test sheet.xlsx.
Is it possible to create and run a macro from test sheet.xlsm that will extract data from test sheet.xlsx, specifically from below the heading on row 64 down to and above the heading on row 123 and paste into the (new) worksheet in test sheet.xlsm
This sheet is updated daily and all the heading rows are constantly fluctuating as products are sold and added.
I can do this manually easy enough but if there's any way that this can be automated
I have a directory folder with an active workbook and another workbook id like to copy data from. The Following macro opens up a file in the same directory, copies some data and pastes it in the active workbook. However with this code I have to specify the filename, 'Data.xlsm' in the example code. I would like it to copy data from the only other workbook in the current directory WITHOUT having to specify the name in the code, so just opening it up no matter what filename it has.
In addition I would like to extract the filename from the workbook im copying data from and paste it into the activewoorkbook in sheet 1 Cell A1. I had a look at getopenfilename function but cant seem to make it work for my purpose.
The format of the text in which I need to extract numbers is as follows:
23411268 - 23411270
Need to extract the following:
23411268
23411269
23411270
These numbers have to be listed in three seperate rows.
There is no where else for me to go. My problem is: I have a table with over 30,000 rows and columns A - W. The first column A contains UNIQUE ID. Column G contains CATEGORY CODE.
Example.png
Since Unique ID (column A) can be listed more than ones, I need to select only those records (rows) that correspond to the following: Category Code (column G) is either 14, 15, 16, 17, 18 and not any other. I hope you can see the attachment, UNIQUE ID = a;does not satisfy my criteria as it does contain Category Code 14, it also contain other codes. The final result from the sample provided would be extracting data for UNIQUE ID = e (since it is the only record that does not contain any values other than 14, 15, 16, 17, 18).
I have attached two documents. One is called "Zone Destination" which is a template I designed. The other file is called "Schedule5_4" which gets downloaded from a work server once a week that contains all employees and their shifts for the entire week. What I am able to do so far is extracting the first row using index and match but I don't know how to extract any of the rows that follow. an employee might have several rows for one day based on a lunch or if they are working in multiple zones during their shift.
Zone Destination File -- start tab has the template in place that gets copied over when you create a new tab.
-- employeeroster tab contains the employee roster that i use to match with the schedule5_4 file. i changed the names and also reduced the amount to make it easier to read. i have over 80 employees but for this example, i only made up a handful.
schedule5_4 File -- this file contains all the data that I need to pull from. The criteria that I am using is by employee and date. I'm matching from the employee roster tab and also the date in cell a1 located in the zone destination file.
[URL]
I have a sheet of data, and i need to extract rows that contains dates containing 5 in them. Eg, 5 Jan, 15 Jan, 25 Jan... 5 May, 15 May, 25 May and copy them into a new sheet. Is there a faster way to do it via VBA rather than manually extracting them?
View 5 Replies View RelatedI need to filter a large database using multiple OR criterias.
Database is CUSTOMER NAMES in each row with CONSUMED PRODUCTS following in each cell.
I want to filter two or more products and list customer names in sheet2. Like listing customers using salt or pepper Filter criterias will be entered into cells in sheet2 with results shown below and I want search to be active and alive just like vlookup function.
Sheet1
CUSTOMER NAMES---CONSUMED PRODUCT---CONSUMED PRODUCT---CONSUMED PRODUCT
GEORGE--------------SUGAR-----------------SALT
MICHAEL-------------PEPPER-----------------CHILI
JACK-----------------BREAD------------------SALT-----------------COFFEE
10/05/0808:30:00 AMValid Card EntryLamDoor 1
10/05/0808:31:00 AMDoor Leave Open Door 1
10/05/0808:32:00 AMDoor Closed Door 1
10/05/0808:41:00 AMValid Card Exit Yap Door 1
10/05/0808:46:00 AMValid Card Exit Lam Door 2
10/05/0809:14:00 AMValid Card EntryLam Door 2
10/05/0810:18:00 AMValid Card Exit Lam Door 2
10/05/0810:19:00 AMValid Card Entry Yap Door 1
10/05/0810:40:00 AMValid Card Exit Yap Door 1
10/05/0810:42:00 AMValid Card EntryLamDoor 2
11/05/0808:30:00 AMValid Card EntryLamDoor 2
11/05/0808:20:00 AMValid Card EntryYapDoor 1
11/05/0810:20:00 AMValid Card Exit Lam Door 1
11/05/0811:40:00 AMValid Card Exit Yap Door 1
the above is the data i need to work with (dates are in dd/mm/yy). i need to find a way (a macro, preferably) to extract the date and time corresponding to one person, and paste it into a new sheet, e.g. at sheet 2 (renamed to "lam") should be
10/05/0808:30:00 AM 11/05/0808:30:00 AM
10/05/0808:46:00 AM 11/05/0810:20:00 AM
10/05/0809:14:00 AM
10/05/0810:18:00 AM
10/05/0810:42:00 AM
I have a spreadsheet full of data and I need to extract only those lines of data which can be identified by the last three letters in a cell. I am adding an example spreadsheet which highlights the rows I am trying to extract based on the contents in column B but only where column B ends in KY.
Example data extract requirement.xlsx
I have two exported lists of data to compare from my newsletter program. One is the list of email addresses that I sent the newsletter to and the other is a list of the bounced email addresses.
Question: How can I extract the bounced emails from the list of sent email addresses?
In other words I want a list of clean, sendable email addresses.
My query is , I require the UCF column for the ID having maximum number (last five digits) of UCF column for the respective year column values
IDUCF Yr
559589876543212610007971212
559589876543212610000333712
559589876543213610004703013
563829876543212630000323912
My answer should be like below (or) having an additional column saying Active/Inactive.
IDUCF Yr
559589876543212610007971212
559589876543213610004703013
563829876543213630004703113
563829876543212630002782312
I am trying to extract data from a website:
[URL] .....
I looked at the source code of the website and realized that if you notice (above) that the variables listed in the link (i.e year, month, day) are exactly what i need to change in order to get the data for a specific date. how can I accomplish this using VBA. so say I have in on an excel sheet year in column A, month in column B, and days in column C (time interval is constant so we don't have to worry about stime and etime). and i run the macro and it loops through each row taking year,month,day for all rows and saving the data as .csv or xls files?
how I can extract data between two strings?
Example: My string is: -_creativename-_spongecell-_creativesize-_300x250-_creativetype-_spongecell-_
How can I create a column that contains everything AFTER 'creativename-_' and before '-_creativesize-_'?
I have created a table of data with Serial No, Name, Age & Common Friends field. Now i need to extract all the data (complete Row item) where the condition matches a certain criteria. Basically a formula or function which will say what are the names of people along with all other corresponding fields (Age & Common friends) who have common friends as "Jill" or "Jack" (For example). Note : I cannot use filter option and copy/paste the filtered data in new sheet as i am working with thousands of data items. Due to the nature of report software/data extraction report, multiple data items appear in single cell only (Multiple friends name appear in single cell).
View 3 Replies View RelatedI have a problem with VLOOKUP not extracting the data I need from 1 workbook.
I want to populate 4 different sheets in the "108" workbook with data from the "1st" workbook. I've uploaded the workbooks to show the formulas I have already. The data in "1st" workbook changes daily and can have upwards of 2000 rows. I've simplified the list for the example.
In the "108" workbook I want to fill in the first three columns with the formula down to about row 500.
I've tried different formulas to do this using EXACT, MATCH, and VLOOKUP but to no avail.
I have table that has a list of agents (Colum B) and then corresponding appraisal dates; 3 Month (Colum C), 6 month (Colum D) and annual (Colum e).
What I would like to be able to do it to have something that looks at the table and tells me all the 'appraisals' that are due the same month we are in, and the date that it is due, as well as the 'agent' that is due the apprisal.
I have played with the INDEX MATCH formulas and also pivot tables but have not found a solution, is it something that is going to require VBA or is there a simpler solution?
Let's say 1 Feb starts on week 5, 1 Mar starts on week 9 and 1 Apr starts on week 14. As such, feb has 4 weeks and march has 5 weeks. I have a spreadsheet of data on a weekly basis, and i want the data from week 5 to week 8 to be under feb and data from week 9 to week 13 to be under march. The data is to make a graph. Is is possible for excel to be linked to the calander so as to detect which month has 4 weeks and which month has 5, depending on the 1st of each month?
I have many company-specific Excel files containing given data fields such as company name, company address, commodities supplied, etc. An example is attached here under the tab "Company Profile". I would like to extract all the information from the different "Company Profile" Excel files and put it into a more usable format, as shown in the attached tab "Results," where the company data runs horizontally across.
I tried to record a macro to do this ("CompanyProfile" -- included in the attached file), but I am not able to make it do what I want it to do. I also do not know how I may be able to capture the information in the "check boxes."
In one cell i have a dropdown. Depending on what is in that dropdown, i want a table of data to be written. I'm not too hot with pivot tables but my understanding is that is predominantly to do with counts. This is more of a multiple vlookup i guess. I want to pull from a list of data (3), all the cases matching the dropdown (1) and deposit into a list (2). What is the best way of doing this? I don't want to use filters. See attached.
View 2 Replies View RelatedI am using INDEX functions to extract up to 2 items of data which I now need to extend to upto 6 items.
The spreadsheet shows a list of staff initials and a day by day work rota of (M)ornings, (A)fternoons, (N)ights and (O)ffs. What I am needing to do is to identify only those staff who are rosterred Off and to place those intials in the two sets of colored cells in rows 5 and 7.
The attached spreadsheet, Tester1.xls shows the 2 existing INDEX functions in use. These can only detect the first occurrance of "O" and the next. I now need to be able to extend this functionality to cover all "O"'s in both section 1 and section 2. So, eg if 12 Aug is input to B15, I can only select IB and GG, but I now want to be able to see MG in the next blue cell.
[URL]...
This entire link is in one cell i would like the data Iowa_Health_System
What formula can i use
how I can extract data from a non-linear line graph to be used in a calculation. The graph will be made based on user input data and the values to be obtained will not be entered, but read off the graph. Again, it is non-linear, and while it could be log-normal data, it may not be any particular function. Basically, I want the program to read off an x-value for a particular set of defined y-values.
View 1 Replies View RelatedI have approx 20 spreadsheets which within these spreadsheets is a named range, say "data3"..... the 20 spreadsheets is in the folder Z:Sales.
What i wish to do, is extract each of the data3 data sets and append each of them one below each other in the master spreadsheet.
By the way, data3 can vary in range, eg could be a2:v60 in 1 workbook, and a2:v50 in another book.
I have a bunch of contributions events and I am trying to extract the date ... for example. each line has "contribution 11/02/2011" or "contribution 12/17/2010" is there a formula that will pull out the date. this way I don't have to do it manually.
View 1 Replies View Relatedhow to get the function in excel 2007 for the following problem please . Col R i am cutting and pasteing from a web page info like this 1/1 or 13/14 or 9/10 . when i paste to my worksheet it comes out as 3-Mar or 10-Sep or 12-Dec , but everything 13 and above is coming out as per the web page eg , 13/14 or 15/15 . I am actually only wanting the first number or numbers to the left of the slash / mark , so col R2 might be 1/1 i want only the 1 to appear , 13/14 i want just the 13 and so on , they are only ever 1 or 2 digits ,
View 9 Replies View RelatedI've been working on this issue today and have decided to ask the same question differently; sorry if this is duplicitous....
View 9 Replies View RelatedI am using the "CreateObject("InternetExplorer.Application")" method to extract data from a webpage. This webpage is a sub page that I cannot find the data for, even after filtering through all the ".document.all(x)" items? Any ideas on how to obtain this "sub page" data, since there is other data that I need which is on the "main" page? I don't want to have to call up this sub page separately since I'm already seeing it (physically able to view it on the main page but not able to extract it) from the main page?
View 9 Replies View RelatedI 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 file A3R-(345).xls, B1Z-(235).xls etc files inside folder called MyFolder.
another master file Master.xls which will extract data from files inside folder MyFolder.
what i need is to put values from file names inside Master.xls. It will loop through all files inside that folder and extract the numbers between ().
So column A in Master file will have:
345
235
..
How to do this using VBA? (only Master File should have the VBA code)