Extracting Rows Of Data Based On A Cells Text?
Jul 9, 2014
one 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
View 4 Replies
ADVERTISEMENT
Jul 26, 2014
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.
View 3 Replies
View Related
Apr 30, 2007
Please check the attached workbook. Sheet "Data" has some bank account data, and whether the dues are paid or pending.
What I need is, I need to extract only the data of Column A and B into a new worksheet, only if G is in the Pending status.
I don't want to use a macro, nor a pivot, nor autofilter. I need to do it through formulas. I have seen this been done before, but can't figure it out. The max number of pending accounts wont be more than 10, so need to just pull the formula down 10 rows. I have already entered the data in the second sheet that would be the result.
This second sheet is sort of a supervisor sheet, so that whenever they want they can open it to see the number of Pending accounts.
View 3 Replies
View Related
Feb 23, 2010
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.
View 14 Replies
View Related
Oct 29, 2013
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).
View 6 Replies
View Related
Jun 15, 2013
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]
View 5 Replies
View Related
May 7, 2008
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 Related
Feb 20, 2014
I regularly run a report that comes out in a slightly different format each time depending on whether or not there is data for specific criteria. for instance (sample attached): criteria a, b, and c are in rows, 1, 2, and 3 in columns. a, b, and c are expected to repeat themselves several times. if there is no data in one instance of b then you may see something like abcacabc.
I'd like to loop through each row in column a, check to make sure that it is the correct value (either a, b, or c) and if not, insert a blank row so I can then copy the data to my final project without having to worry about formatting.
the example above would then become abca cabc
View 3 Replies
View Related
Nov 5, 2009
I need a formula that will extract #s, some of which appear as a range, from many cells. The #s/range of #s will not be in the same text location for every cell & “text to columns” converts some of the ranges to date format which is bad. Below is an example of some of the data that may appear. The only constant is that the first name is only the initial w/a period, but there may be a Jr. or Sr. thrown in occasionally.
T. Jones 1
M. Chavez 2-25
B. Bailey Jr. 26-125
View 7 Replies
View Related
Oct 28, 2009
the text entered in B2, to split it, so each letter/figure gets his own cell.
like showen in example below.
using excel 2003 + 2007
and if possible no macro's
ABCDEFGHIJKLMNOPQ1datecont.seal
2
ABCD123456-7
ABCD123456-73
View 11 Replies
View Related
Jan 25, 2013
I am wondering if I could have a formula be used against a certain row/cell containing a certain "phrase" or "number" for instance
I want E1 to read something like this = (row containing item "FF32105") (the given row from the previous statement) ( the input column ex. B) (the input value ex. *6)
Essential I want to be able to copy and past a sales forecast and have the formulas automatically (listed below the pasted forecast) calculate purchase needs. The issue is that if our forecast one month has a certain item and then the next does not then they will end up on different lines after the copy/paste. We have over 500 items so a manual adjustment would be time consuming..
Is there a possible way for the formulas to "find" the correct line to start calculating data?
View 6 Replies
View Related
Apr 23, 2007
What I have is a column of data(text) which contains amongst all the text three strings of text in ever cell in the column which I require copying into three adjoining cells
The data I require is :-
(a) The persons name which is always after the word ‘Requester’ e.g. Requester Steve Robinson
(b) Their office location which is directly after the persons name and is in brackets e.g. (Newcastle User)
(c) The Approving persons name which is preceded by ‘Approved by’ e.g. Approved by Christine Hunting
See examples 1 & 2 below
Example 1
CR0/CRZ3651 Requestor Steve Robinson (Newcastle User) Tel: 01234 798157 Approved by Christine Hunting
Please install and configure 2 Ultra 2s (typhoon and lancaster) for use as ARTE workstations. These workstations require Solaris 2.5.1 plus the same patches as before
Example 2
CR0/CRZ3118 Requestor Doug Cunningham (Newport User) Tel: 0114 9881480 Approved by John Smithers
Please provide support to set up Cisco 2691 Router and PIX-506E Firewall to enable external connection of a remote terminal for project work.
As you will appreciate the text in the cells is of non standard lenght and the three pieces of information can be located virtually any where in the text
View 9 Replies
View Related
Jan 15, 2009
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 Replies
View Related
Sep 16, 2013
This could be a bit complicated, but hopefully also useful for others. We have the following sample of 10 cells from a large file with similar data:
N: 1; B: 162; M: 278, 278; T: 24; A: 103, 105; I: 393; Ḥ: 7240, 7390, 7465, 7546, 7617, 7756, 27399, 8380, 8741, 8894, 8985, 9559, 9671, 9741, 10119, 10211; W: 40; D: 766.
N: 2; B: 246, 889, 1136; M: 255, 255; A: 55; I: 286; Ḥ: 22731, 22802, 22857, 22906, 22948; D: 685.
[Code]...
We want to extract the same letters with its numbers and put the same letters with numbers in one column and do this for all letters, but - this is important - while keeping them in their original row. The purpose of this is addition of data and easy rearrangement of the letter plus numbers order, e.g., to: B M N A T I W D Ḥ. As you can see not all the rows have all letters, nor are the seizes the same. There are thousands of such cells. Some parts are partly in bold, we want to maintain that layout after sorting. Such a thing should be easy for computers, but how could this extraction and manipulation be done (I have Excel 2010)?
P.S. There are two spaces after every ; that were eaten up here.
View 9 Replies
View Related
Aug 15, 2008
I have a text file with a long list of data that simply needs to be extracted under separate columns in excel. Below is one sample out of a long list. Here is sample data and the format of the text ...
-----------------------------------------------------------
ABC MORTGAGE LLC
232 23RD ST STE A
BAKERSFIELD, CA 93301
KERN County
Title II
Approval Date: Dec 08, 2007 [11 Areas Approved for Business]
HECM: No Originates 203K: No
Telephone: (343) 633-1234 FAX Number: (343) 632-1330
E-Mail Address: chadlow3@aol.com
------------------------------------------------------------
In excel I need to have these columns, Company Name, Address, Phone, Fax, and Email. I need to pull the data out of the text file automatically and insert it into these columns. The other information in the sample such as approval date is not needed. How do I do this?
View 14 Replies
View Related
Sep 8, 2009
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 Related
Aug 5, 2009
I have a table with 'Product Rereference', 'Date', and 'Estimated Assets' Columns. I need to run a formula that will extract the number in my 'Estimated Assets' column, based on speicific list of 'Product Reference' numbers that I have filetered, and a specific date - 1/31/ of each year.
I've been trying to create a formula, however I can't seem to work it out.
I have attached a file, which should make things a lot clearer :P I am trying to make the data extract into the table to the right. I have filled in the first few columns manually, to show what I would like to formula to achieve.
View 4 Replies
View Related
Dec 14, 2013
I 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
View 5 Replies
View Related
Jul 14, 2012
I have the following data in column b and I would like to extract into individual cells.
I would like to extract upto the first number, number text after first number etc
I have several rows and the data lengths are variable
Liverpool FC 2 Sheffield United 1
Manchester United 0 Everton FC 3
Middlesbrough 2 Manchester City 0
Norwich City 2 Chelsea FC 1
Oldham Athletic 1 Crystal Palace 1
Queens Park Rangers 3 Southampton 1
View 6 Replies
View Related
Mar 13, 2014
I have a spreadsheet where the data held in column A is an export from another system and the exported data looks like this:
??????? Stn ??????? ??????? ??
What i would like to be able to do is to split out the data in column A so that the text before the 'Stn' (but including Stn) are shown in column C with text after 'Stn' is shown in column D. i have looked at using =LEFT, =MID or =RIGHT with =FIND but the problem i have is that the text prior to the letters Stn could be one, two, three or four words and the text after could be similar.
View 2 Replies
View Related
Jan 23, 2013
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‎
View 5 Replies
View Related
Jul 11, 2014
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.
View 4 Replies
View Related
Jul 3, 2013
I have a column of data that contains text such as 'as per A3', 'B4 requested' and 'as per F6 Mark' (these aren't cell references).
I'm trying to find a formula that will just strip out the 'A3', 'B4' and 'F6' element of the text. I've tried SEARCH with wildcards but it's not working, and can;t use the LEFT RIGHT or MID functions due to the inconsistent data.
View 4 Replies
View Related
Nov 29, 2007
I work in my Client's office and assist in settling construction disputes. Part of this work is to browse/search their server for documents that may assist in strengthening their case.
During this review I have found an excel document which is a text-only version of a pivot table, ie someone has done a copy, paste special, values into this sheet. I need to extract the original source data from this table back into the list format, as the original source of the data cannot be located
The row titles on the left are activity descriptions, the column headers are dates and the data in the body of the table is hours. As an idea of size the data is spread over 213 columns and 45 rows. Their are more blank cells in the table than entries.
What I would like to do is create the data in it's original form ie
Column A; Date
Column B; Activity Description
Column C; Hours
and have a separate row for each instance of an entry of hours from the pivot table.
View 9 Replies
View Related
Oct 4, 2006
I'm trying to do is build a form that will allow me to select from a list of options, that links back to a catologue of data so that when i click on the generate button it will pull the data associated to the item selected from the list into a text box in excel. I have attached the form that I have created.
View 4 Replies
View Related
Jan 17, 2013
What type of formula would resize a text box based on the input of two cells? Here's what I'm trying to accomplish:
A1 - width (in inches)
A2 - height (in inches)
Converts the text box to that height & width.
This is for a sign request form I am creating that would give our requestors the ability to see the size layout of their sign and then input their text to match what they're looking for.
View 3 Replies
View Related
Jan 26, 2010
I've got a list of donor names, names listed multipled times in Column A, each row with different donation data. For instance:
Sheet 1
A1 - Mary Jones, B1- Internet Donation
A2 - Mary Jones, B2 - Silent Auction Purchase
A3 - Mary Jones, B3 - Event Ticket Purchase
A4 - Gary Jones, B4 - Foundation Grant
A5 - Gary Jones, B5 - Internet Donation
A6 - Sara Parker, B6 - Event Ticket Purchase
I want Excel to compare rows A1 and A2, and if the name has not changed, enter the donation data from Column B into another sheet that will just list that person's name once:
Sheet 2
A1 - Mary Jones, B1- Donation1, C1 -Donation2, D1 - Donation3
A2 - Gary Jones, B2 - Donation1, C2 - Donation2, D1 - Donation3 (which would return no value because there the name changes
Does this involve IF, MATCH or VLOOKUP formulas?
View 9 Replies
View Related
Nov 18, 2013
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
View 1 Replies
View Related
Sep 21, 2009
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
View 9 Replies
View Related
Mar 9, 2009
I have written basic macros (probably not the most efficient solution, but its what I know how to do). Up to the present point things are going well. (I tried to attach file, but without success - So I will email the file directly it is pretty easy to figure out) I have option buttons tied to macros that populate a data sheet based upon the input.
This works out quite well and I am able to generate the statistics I desire quite easily. Now comes the second phase, (this is where I am stuck) - when I click the option button "no", I would like this to generate a line item in a separate existing worksheet which lists the item number, tells the discrepancy (as written in the column on the inspection criteria sheet), has a text field for the inspector to briefly explain the exact nature of the discrepancy, and also keeps discrepancies in proper order. Conversly, if "yes" is selected, I would like to be able to check the associated comment page, search the page for that discrepancy number, and if found, delete the line item, then re-oder the page.
View 8 Replies
View Related
