Pulling Out Info Based On Text
Jul 23, 2007
I have a regular table of data and dependent on the info within a column of cells I would like the info to appear within one of 3 new sheets. BUT.... I can't figure it out because the info forms an irregular part of text within the cells in a specific column - Obviously if it was dependent on the whole of the text I could use Vlookup but it's not.
If it needs further clarification, a column of cells may have, say, "blahblah234/PLblah" -> If this cell contains within the text "PL" then it should have the whole of the row in the new sheet, if not then blank.
View 9 Replies
ADVERTISEMENT
Jun 5, 2014
I have a spreadsheet with info about students in a summer program. I need to pull out the students who have allergies along with what their allergy is and their emergency contact phone number. I would like this new info in a new spreadsheet.
Here is an example of the spreadsheet:
allergiesnamephoneallergy type
nobob410.555.1234
nojim410.555.5843
yesmeg410.555.7458nuts
nolex410.555.1159
yeswill410.555.5005fish
Is there a formula that I can use?
View 1 Replies
View Related
Jul 8, 2014
I'm copying and pasting data from a PDF and need to eliminate some unnecessary data. The original .pdf has 4 columns: Account, Dollar Amount, Name, Notes
When I copy this to an excel document, it copies the row from all 4 columns into column A. The two columns I need are Account and Dollar Amount. All account numbers are 8 digits so I was able to create a formula to weed that out with this: =left(A1,8)
The problem I'm running into is obtaining the dollar amount within the cell. For example:
A1 contains: 11112222 $1234.56 Sample, Name Sample Note
I pull the 11112222 with =left(A1,8) in column B but not sure how to pull dollar amount to column C.
Further, the dollar amount varies from $1.01 to $10,000+
View 9 Replies
View Related
Dec 10, 2012
I have a list of cities, counties, and schools with dates for each account. I want to put these into a workbook with tabs along the bottom for each one, then I want to be able to select all of the accounts that have a Jan 1st effective date and show them on a list in the first worksheet.
View 9 Replies
View Related
May 13, 2009
I am trying to pull info from Access into Excel using Data, Import External Data, New Database Query. I follow the menu and when I get to the end for the info to be pulled to Excel I get an error.
Microsoft Query:
Too Few Parameters. Expected 1.
View 9 Replies
View Related
Jun 16, 2014
I have monthly reports of sales by client number. I am now trying to pull the annual sales info by month for one client by name. Eg. each month, XYZ ltd has sales figures for various products. Is there a way of me grabbing all his sales info for the year with out having to open each spreadsheet
View 3 Replies
View Related
Jul 12, 2014
I am accustomed to using filters to find a lot of my information in large datasets.
However, now I am trying to use formulas to return specific values. For simplicity's sake, I have included a sample below with a couple types of scenarios I am looking to solve through the use of formulas. Would this involve sub-arrays perhaps?
sabinfire1.xlsx
Excel questions.docx
View 5 Replies
View Related
Dec 26, 2013
I have found a code to pull outlook calendar information from outlook to excel. I am trying to determine how to pull from Shared Calendars. This code looks as if they calendar has a number (9), but I can't figure out what the other calendar's numbers would be. How to pull from Shared Calendars and how to pull the month for which the calendar is on. My shared calendars are under Calendars>Shared Calendars in outlook. So for example, I would need to pull John Doe's calendar information for January if the calendar was on January. My code is listed below.
Sub ListAppointments()
Dim olApp As Object
Dim olNS As Object
Dim olFolder As Object
Dim olApt As Object
Dim NextRow As Long
Set olApp = CreateObject("Outlook.Application")
[Code] ......
View 2 Replies
View Related
Nov 27, 2005
Here is what I am trying to do with no luck so far.
If I type RS23U1R109000 in a cell A1, I want B1 to read the 5th letter or
number and fill B1 with E86.
Example
A1= RS23U1R109000 B1=E86
A1= RS23V1R109000 B1=E87
A1= RS23R1R109000 B1=E84
As you can see in my example, the 5th letter could be U,V,R or whatever, but
I need cell B1 to read that letter and populate B1 with E86, E87, E84 or
whatever.
View 14 Replies
View Related
Jun 18, 2013
Here is an example of my spreadsheet: Excel example.xlsx
I would like to get all the bid/ask quotes for the different currency pairs in sheet 2 into sheet 1 sorted by the right date and time.
I need a formula which recognize the time + the currency from sheet 1 and search for it in sheet 2 then brings the right bid/ask quote back into sheet 1.
View 2 Replies
View Related
Aug 2, 2014
I am trying to pull titles out of a text string but its quite difficult because of how the data was originally inputted. I need titles that appear in the following different formats
" title"
title/
title"
These titles were not inputted consistently and it could show up in the middle or at the beginning. I have attached a sample of the data that i am working with and a sample formula that i was trying to use to obtain my information. The formula that i have is a nested formula of find and mid formula that searches for the text within " ".
View 5 Replies
View Related
Mar 25, 2008
Is there a way to pull out numbers from a cell that has letters mixed in with it. I tryed using Right() and Left() with the LEN() function but sometimes I might have (examples: "TP3470" or "9310-AV") and all I want to get is ("3470" and "9310") I also may have (4500SSML). I cannot find anything anywhere on pulling out just numbers or text. Maybe a custom format?
View 8 Replies
View Related
Mar 28, 2007
First I copy and paste the text into Excel. All of the data is in column A because of varying lengths, which seems to work fine. I've already written VBA code (shown at the end of this posting) that reads the contents of each cell in column A, pulls out the key information (like the actual error code or invoice #), stores the fields in variables, switches to a separate tab that has a more user-friendly layout, locates the next empty cell in the appropriate column for the field, and puts the variable contents into the cell. I realize that the code currently has some variables that aren't being used and other basic issues, but that's not really what I'm after at this point.
The report itself includes billing errors, with each billing error separated by a series of asterisks. My current code works just fine for sections of the report like the first section in the sample below, where the billing error only contains a single invoice, error code, description, and action item. If the entire report was formatted in that manner, my task would be finished because it does those sections perfectly well.
But...if you notice in the sample report below, the third billing error contains more than one invoice, error code, description, and action item. Obviously, using my current code, when the macro encounters that section, it continues to function, but the rows do not align properly in the separate worksheet because there are extra invoices, error codes, descriptions, and action items. I just cannot figure out a way to have the macro keep the information aligned. In other words, in the third section of the sample report below, the billing error contains more than one invoice and item.
One approach that I've thought of is to try to count the number of rows between the cell containing the previous set of asterisks and the cell containing the next set of asterisks, because it's the information between each set of asterisks that I need to work with. But I haven't found a way to do that and don't even know if that's the direction I need to go or not. I'm pretty much lost at this point.
Here's a sample of the text report as it appears in column A (the fields are actually longer than this - I tried to simplify as much as I could for this posting): ....
View 9 Replies
View Related
Dec 31, 2009
I'm building a personal budget that looks a lot like a set of business financials (really just an exercise to learn excel techniques).
So, I have one sheet that acts as a register -- in column A I have the date of the transaction, column B is the amount, C is from which account the money came, D is the use of the money, and E is a code (1 for paycheck, 2 for investment income, 3 for food expense...etc).
On another sheet I have an income statement. I want the income statement to pull code 1s from the register sheet for the month indicated by column A in that sheet into the proper month column (B is january, C is Feb....). I then have a cash flow sheet which uses V-lookups to pull the information from the income statement, and that seems to work fine.
View 3 Replies
View Related
Jun 12, 2014
I've done some searching and cannot find the right answer for my question. I need to create a formula that will pull column header text data based on cells within a table that contain a numeric value not equal to zero. The catch is that I cannot use any one specific numeric value as the basis for a Lookup.
I've attached an example spreadsheet for reference. Basically, I need two formulas:
1. Formula to pull column header (Receiver name) based on Sender. This is a necessity.
2. Formula to pull individual lines for those senders that have multiple receivers (Division D in my example).
My actual data file has 80+ senders and 100+ receivers.
View 6 Replies
View Related
Dec 17, 2013
I've attached a workbook with a description of what I'm trying to do. I'm not sure if it can be done with formulas or if it will require VBA. I can move it to the VBA section if the latter terms out to be the case. it's not that I'm opposed to copying code, I just like to know how it works with enough depth that I can fix/modify it, and I can't do that with VBA).
View 6 Replies
View Related
Jan 25, 2014
I have a workbook with multiple tabs, each tab is representative of a person and their ongoing project list. What I am trying to do is to be able to pull rows from each tab identifying the persons active projects based on an assigned due date.
Details: Tabs for Tom/Susan/Phil/Jerry each person will be updating their own sheet with project completion details. Each tab has similar headers with a corresponding due date or completion date. On the master sheet I would like to pull those rows that correspond to the specific dates for all persons.
Example - give me all rows that reflect projects due on 1/21/2014.
View 6 Replies
View Related
Oct 4, 2013
How to pull data based on column B to individual tabs.
I have the following data:
A B C D
Jane Doe | L1 | 20% | High
John Doe | L2 | 15% | Medium
Mike Smith | L1 | 60% | Low
Marie Smith | L4 | 10% | Low
I want to have the first tab/spreadsheet only pull records that have L1 values for column B
In the next tab/spreadsheet I want to pull records that have L2 values in column B etc.
What formula can I write that will pull all L1 records (A.B.C.D) in to Tab 1, then all L2 records (A,B,C,D) into the next tab?
View 10 Replies
View Related
Jun 3, 2014
I have a list of active accounts with its relevant data on one sheet, then I have a list of closed/inactive accounts that will be updated periodically by different users. I would like to run a query for the accounts on the closed list to pull in relevant data from the active list,if any, or maybe just highlight the closed accounts on the active list to ensure that the closed accounts aren't active again. Is that possible with a macro? Should it just be a vlookup? The accounts list is going to be a very big file and I am trying to find a solution that won't take up too much time to run.
View 4 Replies
View Related
Nov 23, 2009
I wanted to replace Array(Array(1,1),Array… in TextToColumns.
http://www.ozgrid.com/forum/showthread.php?t=140770
http://www.excelforum.com/excel-prog...o-columns.html
View 9 Replies
View Related
May 12, 2014
I would like to fill in a a form on page /sheet one and have the same info on every sheet that follows is it possible?
View 3 Replies
View Related
Aug 22, 2014
I have attached a spreadsheet and I am trying to capture the info in lines 2,7,12,17 and return the info into column d,e,f,g
The info in these columns at present has been manually entered but I am sure it could be automated.
OOL Roster Final 18-31Aug14.xlsx
View 1 Replies
View Related
Jan 29, 2014
I need a macro thaw sends a mail to a specific address (and CC) based on a critiria in a cell. This is the code I have. It does not send the mail.
[Code] .....
View 4 Replies
View Related
Feb 22, 2009
I am trying to get a list which counts info based on multiple conditions but I can find no way round this one point
I have a sheet which has all the games played in a particular division and the time that each goal was scored and I am trying to break down the following
What I want is a list that has a column that has the result of how many times when team A were playing at home was a goal scored between the 45th and 65th minute
column A = Home Team
B=Away Team
C= Time of First goal
D=Time of Second goal
E=Time of Third etc
I have a countifs in place for counting the home team and counting if the first goal was scored in under 10 minutes (this is simple as obviously all the first goals are in column C) but what I want to do is be able to have a count of how many times a goal was scored between x number of minutes eg between 40-50.
My problem comes when trying to count the goal times as some matches will have no goals and the cells will be blank and others will have multiple cells filled and the figure I want could be in any column and I also only want it to count only the amount of games a goal was scored between those times so if a game had 3 goals scored in between that time I only want it to count 1.
The sheet has a lot more columns but this is the main sticking point.
View 10 Replies
View Related
Nov 29, 2009
I have a column that has different SIC codes in it (00045, 00046, etc). I would like to set up a macro or filter that would take cells in this column and delete each row that contains a certain code that I don't want.
Example, I want to delete all the rows with SIC codes that have 00345, 00873, 00145, etc in a particular cell. I would like a macro that would look at each cell in the column and delete the rows that I don't want.
View 9 Replies
View Related
Mar 14, 2012
I have a sheet (see Sheet 1) from a report we run which lists the following information: Personnel Number, Amount, Wage Type. This is generated for 1000's of employees, with each personnel number being repeated several times in column A.
I am trying to pull specific data to another sheet (see Sheet 2), which would ideally generate the sum of "Amount' for a specific wage type for each personnel number. The issue is is that there may be dplicates of the wage type for each ID number (which is also repeated).
For example, the total salary amount on sheet 2 for ID#12345678 would be 0, while for #9876543 it would be 1250. Is there a formula I could use on sheet 2 column B that would generate this?
Excel 2010 ABC1Personnel NumberAmountWage Type212345678550Payment312345678400Overtime412345678300Overtime512345678250
Payment612345678750Vacation798765432800Salary898765432250
Payment998765432100Overtime1098765432450Salary1198765432300Overtime
Sheet1
Excel 2010 ABC1Personnel NumberTotal SalaryTotal Overtime212345678398765432
Sheet2
View 3 Replies
View Related
Jun 2, 2013
I've been using a nice colorful spreadsheet to use to schedule rooms for an organization. Visually it has worked out well but now I need to automatically generate a 'report' using that data. So...
I want to scan each row of a table and create another table with that data transformed.
That is, scan each row and whenever I find any text in a cell I want to return that text (the room being used) as well as the row and column header information (who is starting to use that room and on what date). I've attached a jpeg to show the example.
Ideally, when I scan the row and find text to return the headers I'd like the row scan to also find when the fill color changes and return that column header, too as that that would be the date that the room stops being used.
View 5 Replies
View Related
Dec 22, 2009
I have a combo box named Combobox5 and a text box named Textbox7.
Should this code go in the Userform Initliaze or where? I have tried placing it in both the ComboBox5 Change evnent and no luck so far.
Range("G22:J28").Value = TextBox7.Text
Again this code is dependant upon the user making a choice from ComboBox 5.
I have also tried:
Range("G22:J28").Value = TextBox7.Value
I am using Excel 2003. Any ideas??
View 9 Replies
View Related
Aug 13, 2012
I want to delete the rows if they do not equal "TL" based on certain criteria, but save the info
then delete the rows if they do not equal "Fedex" based on certain criteria, but save the info
then delete the rows if they do not equal "LTL" based on certain criteria
After the information has been filtered it is supposed to load to a template giving monthly information for each of the above, but this is not working
VB:
If Load = "LTL" Then
Range("A2").Select
Selection.Sort key1:=Range("F1"), order1:=xlDescending, Header:=xlYes
For x = 2 To TotalRows
If Cells(x, 6).Value > 10000 And Cells(x, 1).Value = "FEDX" Then
[Code] ....
I am pretty sure that my problem lies within the parenthesis where it says Rows("2:" & x-1).Delete. Am I supposed to put something else there since I am deleting rows based on three different loads?
View 6 Replies
View Related
Jun 17, 2008
My company has an excel file that has our new customer's information (colum A & B are first & last name;Column C is customer email; Colum D is cutomer phone, ect...)
once that information has been entered we have another excel file that will load all of a customer's information, and then be pasted into a welcome letter into MS Word for their confirmation & emailed to them
The problem I'm running into, Is that I do not know how to populate my nex excel sheet with the info I want only from a specific line.
I would like to enter a line / row number (for example '6') and than have excel go to that file & copy/paste the name, phone, email, company name, ect. into my new excel sheet
I have one that works already, but the previous owner PW protected it & hid his formulas/macros, so I'm in a dead end now...
View 9 Replies
View Related
