I have an excel sheet with data results of samples analysis from ICP-MS the sheet includes the name of the sample and its results, I want to extract for each sample only the ( average value "x" , standard deviation "S" and " %RSD) and sort these values in columns , see the photo and the excel sheet attached ..
View 13 Replies
I currently have a macro that looks at one value and copies the data to another worksheet.
I'd like to modify it to look at two different values and then copy the data. I've tried modifying the VBA code, but can't seem to get it to work. In the "ETF Report (1)" tab, i want it to look at the "ETF Report" and "PO No" values.
The Final Version tab is what i'd like the results to look like.
I have a workbook with two tabs, Summary & January. The january tab has total expenses for the month of january that are subtotaled by last name. i.e. (baker total, decker total..). I would like to be able to use a formula on the summary tab to pull the values of each of the totals for each person from the january tab to the summary tab.
I tried on the summary sheet doing =jan and selecting the first persons name and then going back to the summary tab and dragging that formula down but it pulls all of the other data within the subtotal on the jan tab and I just want each persons total.
Excel 2007. Is there a way to extract information from cells and rows that constantly move? These rows need to be able to cut & paste, copy & Paste, insert and delete. I have the formula I need to display the data, but with the cutting and pasting etc, my formulas get all messed up.
I have the excel sheet on Google docs. It displays some cells wrong because it does not recognize certain formulas.
[URL]
On the second worksheet, I want the information from columns A, H, M, N, and O to be extracted at a certain time each evening and sent to a worksheet, without sending duplicates. These rows will constantly move which is why my current setup, which is just to display the information, will not work... We decided to try to extract it into a different worksheet or workbook if possible.
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 "cat_no" file and a "master inventory" file.
I need to do a vlookup to match the catalog #'s and mfr name in the cat_no file and the master inventory file. I also need to pull the pricing and packing string from the master inventory file and add it to the cat_no file where item numbers match.
I am trying to extract values from a text string and add them up in Excel 2007. So far i have been successful in extracting the value out of the text string like this - =MID(I6,AD6,3) where AD6 holds the position number in the text string to start from. So it's working OK for one row but i need to do the same thing on multiple rows where the text string can be in different columns and I'd like it to automatically pick up the non-blank cell.
each row only has one column with text in it and the value i need to extract is always after "$". this is a working spreadsheet so the text string could move from column to column over time and I'd like my formula to be able to detect which column to read from. I then need to add up all the values from each row.
I have a table of data from which I would like to extract the information which is listed horizontally.
Column A Column B Column C Column D
Code Name Address Locality
SMITJOHN John Smith 123 Bell Rd Perth
On the 2nd worksheet I have a form which I have designed to which I would like to extract the information from the 1st worksheet.
I am hoping that it is possible to start entering a code based on some reference on the Name. Eg SMITJOHN
Once the code is true or correct it will populate certain cells on the 2nd worksheet automatically.
I work in transport and am looking to streamline some of the paperwork and am trying to do it within the means I have available to me.
The format of the 2nd worksheet is;
CODE: SMITJOHN
NAME: John Smith
ADDRESS: 123 Bell Rd
LOCALITY: PERTH
I have some data, in a xlVeryHidden sheet, with multiple headers. An example is given below.
I would like to auto-generate a table on a different sheet with only revelent headers and filter by source and month/year. Basically, I want to write a macro that would make this table for, say, January and source1 only, without headers 1, 2 or 3.
(Very similar to the Pivot table, only without combining the data)
--------------------------------------------------------------------------------------------------------
Source
Type
Ref
[Code]....
I have this text in excel in every cell, but I want to extract only certain data from within the cell eg:
Name: John Doe
Price min: $2000
Price max: $3000
Contact Number: 1800-600-400
E-Mail: john doe@somewhere.com
*** Please note that we are trying our best ***
I need to extract only the name, contact number and e-mail from withing these cells, or delete the info I don't need from all the cells. The text does appear exactly like this in the cell. see attached screenshot
This info was extracted from Outlook into a CSV format and this is the body of the mail only....
how to extract data in the following case: I have the following columns:
Employment Name
code1
code2
cost1
[Code]....
how can I do this using formula ? For one sheet and for extracting this data from multiple sheets into a new one in the same workbook?
I work with Excel 2010 and have a very large spreadsheet with data that I need to manipulate in several different ways. I have been filtering and then cutting and pasting but this is very time consuming . Is there a way to extract specific data from the spreadsheet and transfer it to different worksheets? I don't really know how to use macros.
[URL] .....
I have a workbook with 2 sheets.
Sheet 1 will act as a summary for sheet 2, showing only the last 5 entries from sheet 2.
Sheet 2 is based on football results of a season which will have a total of 34 league games plus cup games.
Sheet 2 has 4 columns:
Game Number Home Team Result Away Team
1 United 2-0 City
2 Sportive 4-2 Uniao
3 Real 1-0 Ateltico
4 Villa 1-3 City
5 United 2-1 Real
6 Atletico 0-0 Villa
So sheet 1 will show the last 5 results as the season progress's and new data is added.
I have been able to extract column 'GAME NUMBER' using the following formula:
=INDEX('Back end 1'!A:A,MATCH(9.99999999999999E+307,'Back end 1'!A:A)-ROWS('Back end 1'!A2:A$6)+1)
I am struggling to find a way to pull the last 5 data that is text based.
I am using Excel Mac 2011.
I have sheet containing a schedule of data. It is a record of changes on a project, each change is sequentially numbered and contains a row of data (date of change, whether approved, cost of change etc). Each change is given a cost centre reference (1 of 10 cost centres are being used) depending on the type of change being made.
I need to keep that record of the overall schedule of changes but I also want to extract each of the cost centre categories into a sub table on the sheet to give a schedule of changes against each category.
I have almost achieved what I want but one method leaves blanks in the sub table which I want to avoid and the other method I used to remove the blank lines but needs the first method sheet calculated before it then goes to work and seems rather cumbersome and I suspect there is a much easier method. (I also want it to be compatible with excel 2003 so it can be used across several platforms.)
The link to my file is: [URL] ........
I am using excel 2003.
My query goes like this:
I need to find out "total lifted quantity"(i.e. the sum of 'first state lifted quantity'+'second state lifted quantity'+'third state lifted quantity') for each "state" for each "size" and each "grade".
However when I am applying a pivot table (as seen in Sheet1) it is not giving data in lucid form. I don't want to split the data for three states in three different tables (as seen in Sheet 4).
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.
So in my excel document I have it such that on the first sheet (Labeled 'Sorted') the data is set up as follows: LOCATION, EXTENSION, NAMEWith the appropriate data under each header. On the second sheet (Labeled 'Locations_Ext') I have a named range called Locations; it contains extensions from a separate document, names of people associated with the extensions from the document, and their locations on the map. I am trying to have it such that the excel matches the extension number given on the first sheet with the ones that I extracted from the document on the second and output the location into the first column on the first sheet. The code I have set up for that process is:
=VLOOKUP(B(//SomeValue),Locations,1,FALSE)
I then want the excel to cross check the information that I took from the Visio to see if the visio is up to date with the latest info we have received (Which is the data on the first page under Extension and Name). The code:
=IF(C(//SomeValue)=VLOOKUP(B(//SomeValue),Locations,3,False),"Good","Error")
Both codes seem to be giving me this: #N/A as the results for every cell.. I'm using MS 2010
I want to write down the code that will populate values in "Sheet1" from the Access table. The column headers shows "Envelope types", "Envelope Size" fields from the Access table and each cell should store sum(volume) for each month in the table.
As I can't upload access table in the attachment so I have exported data into Workbook named "tblmain" as attached. But in actual tblmain is Access table. consider it an access table.
wrting code that will fetch data from access table and store in all the cells of the table in "Sheet1" of Elevate workbook.
I have created a spreadsheet that is a roster to track mutliple crews and shifts.
I have a userform which is used to select a person on shift and assign them as being on either, Annual Leave, Personal Leave, Training.... on particular days.
I would like to have the shift combobox auto populate what shift the person is on based on the person and dates entered. All the information is there I just can't get the code to do this.
I was also thinking about blocks where the shift goes from DS to NS and if people are on leave over this time. I would need the combobox to know this also.
I have attached my workbook : New Annual Leave Spreadsheet.xlsm
how to extract the current/active worksheet's file's name using VBA code. Let's say I have a an excel file with the name "Test.xls" Which function do I use to return Test (with or without the extension)?
View 4 Replies View RelatedI know that there are programs out there that will convert a PDF into excel, but what I need to know is, is there a way for excel to access values (numbers) in a PDF and then use those values in a worksheet?
So, if I have a table in a PDF file - containing part numbers, descriptions and quantities ordered, I want to be able to extract the part numbers and QTY, and have them populate an excel table?
As above, how do i retrieve excel file names in a specific folder and place the names into a specific column?
Say there are 2 files named UAT1.xls and UAT2.xls and i have a master file to put all this data in.
I have certain values relating to a single person spread out in various worksheets and I need to get that in one sheet.
eg: For "NAME xyz" I need the values corresponding to "apples" in as many 10 sheets. Similarly for about 20 different things for about 300 names.
Sample Data:
Col A: Col B:
CV01 01
CV01 01B
CV01 02
CV01 08
CV01 03
CV01 10
CV02 02
CV02 02B
CV02 03
CV02 10
CV02 01
CV02 09
CV03 06
CV03 06B
CV03 04B
CV03 05B
CV03 08
CV03 09
I need to generate/extract a "list" of all the values in Col B. that "belong" to each value in Col A. So, if my criteria is "CV02," I need the list to be 02,02B,03,10,01,09 (with each value in it's own cell)
I've tried the "VLOOKUP" function, but it only returns the last value for each criteria (example =VLOOKUP(CV01,A1:B18,2) returns "10")
I've searched these forums, but I'm not really sure what search criteria to use...
This is my qns
cell A1 having <txt>NEWM</txt>
cell A2 having <txt>RSQ</txt>
I need to display NEWM in B1 and RSQ in B2....
Does anyone know of a formula that will take an expression such as
"C1-5" in a cell, and produce five separate values in 5 cells:
c1
c2
c3
c4
c5
Even a starter would help, like how to extract values from "1-5"
Ultimately I'd like to use this for values that can have up to 3 digits following the letter (ie, C99-112.)
extract the highest top 5 values from one column in a filtered table with hidden cells. How can I do this without using advance filter or a pivot table in excel 2003 that will show only the visible cell values and not the hidden cell values
I have fixed amount of columns (6 in example: can be seen at sheet called "llist1", more in real example:"real example of list1") and rows (8 in example, 62 in “real example").
These 6 "analyzed" columns consist 3 type of info, basically there were 3 columns, which were "cut" to 6. In my example those types of info are called "name1"(columns A and F), "name2"(B and G) and "a or b"(C and H). Also there are rows, which should not be “analyzed”.
First, according to example sheet called "list1", i need to extract unique values from some "fixed" rows of columns A and F, more detailed: a2:a4 a6:a8 f2:f4 f6:f8. Probably, a named list should be created. Also it would be great, if these names will be extracted in alphabetical order. This is the first problem.
Then there goes second, the big one, problem: I need to extract unique values from !"rows of fixed rows"! of columns B and G "for" extracted unique values from columns A and F.
It is really easier to understand if you look at the analyzed information structure at the example sheet and what I want have in the end at "ideal" or "wanted" sheets.
By saying "rows of rows" I mean something like this: cell B2 contains this:
1st "row": abc-301
2nd "row": abc-302
3rd "row: abc-303
All this rows, thanks god, always contain 7 signs. I suppose a second named list is needed here, i e: b2:b4 b6:b8 g2:g4 g6:g8
After that I need to count A's or B's, according to rows, already done that myself using asterisks.
how can i attach an excel file? checkick the faq info, the spreadsheet will appear in a couple of minutes...
I am working on a project that has 5 worksheets. I have been able to figure out everything else I need to do but this has me stumped. I have data in Sheet1 A6, that i want to place in Sheet2 A6, Sheet3 A6, Sheet4 A6 and Sheet5 A6 and keep data and formatting(BOLD AND UNDERLINE). So I change Sheet1 A6 and the other 4 sheets change also. I'm using Microsoft Excel 2007.
View 1 Replies View RelatedI want to be able to run a macro that will export some of the data from a worksheet called Generated Report into another worksheet called Matrix. The data will be taken Generated REport and grouped into sections of the worksheet. These sections will be defined by the BRID value.
The Data that i want to export will be from the first 5 columns within the Generated Report(note there are other columns which data is not required from).
The following columns will be exported from Generated Report -
BRID,Requirement, Bug Description, Bug ID and Alt Bug ID.
BRIDRequirement Bug DescriptionBUG ID Alt Bug ID
PQ115 PQ115-Login Bug Description text xihllloloj43712 123
PQ116PQ116-Landing Bug Description text ghghghgoot 43713 126
For Matrix
The data will be imported into Matrix worksheet into the following columns as part of the row headings
- Requirement, Description, Bug ID and VF Bug ID.There will also be other columns (outlined below) which i will use to manually enter data
The data will be grouped and imported into the relevant section by its BRID eg PQ115, PQ116 etc.
I want each section (marked by BRID number)to be separated by row headings.
These row headings are to be generated after populating all data within each BRID number.
When the data has been populated in each row there is a column called 'Status' which contains drop down list boxes which can be assigned.
The default status will be set to 'To Do'. The complete row headings are displayed with Output from Generated Report underneath
BRID Priority Requirment Description BugID Alt Bug ID PC UpdateNotes Actual Result Type of DEfect Test Status Tested by
PQ115 PQ115-Login Bug Description text xihllloloj43712 123 To Do
BRID Priority Requirment Description BugID Alt Bug ID QC UpdateNotes Actual Result Type of DEfect Test Status Tested by
PQ116 PQ116-Landing Bug Description text ghghghgoot43713 126 To Do
So far the macro that i have been using allows me to locate the data for each specific BRID in Generated Report and copy into the corresponding BRID section within the Matrix. I have to repeat this step for each new BRID and i want to be able to whole automate this process.
