See the attached file.
I have a table, which contains a dates in Column A. and Contract type in Column C, and the value in Column G.
I need a VLOOKUP function which takes the value "06.03.2009" and "Dec-2009". That can return the value "10,97".
I have a 4 columned table with 10000 rows
Entry # Correlation Index1 Index2
1
2
.
.
10000
I want to be able to create tables out of this where I can pull all line items with correlation > 0.8 or <0.1 or <0.3 etc.
I think a macro will have to be written for this.
I have a table populated with values for given dates. Some days there is no data. I would like a separate table over to the right to automatically populate with only the date and data.... (no spaces). I will attach an example problem to this Thread.
View 2 Replies View RelatedI have a table I wish to pull information from. Here is an example table:
"Start at".."Less than"..0..1..2..3
80............85...............5..4..3..2
85............90...............6..5..4..3
90............95...............7..6..5..4
This example is A1 to F4. There are two inputs; A10 and A11, and one output (formula) on A12. A10 will be the column I choose, and A11 is the row I choose...but it's chosen if the number is >= column A and < column B.
Example: I input A10 as 2, and A11 as 87, A12 will output 4. If I input A10 as 0, A11 as 85, A12 will output 6.
I kind of understand how to use VLOOKUP for this, but I am unable to check column A and B to choose the row.
I have a huge number of excels that are formatted poorly. They basically look like a checkerboard of data. Looks similar to the illistration below. The X's represent Data and the periods blank cells.
X X X X X . . . . . . . X X X X X . . . . . . . X X X X X
X X X X X . . . . . . . X X X X X . . . . . . . X X X X X
X X X X X . . . . . . . X X X X X . . . . . . . X X X X X
X X X X X . . . . . . . X X X X X . . . . . . . X X X X X
. . . . . . . X X X X X . . . . . . . X X X X X . . . . . . . X X X X X
. . . . . . . X X X X X . . . . . . . X X X X X . . . . . . . X X X X X
. . . . . . . X X X X X . . . . . . . X X X X X . . . . . . . X X X X X
. . . . . . . X X X X X . . . . . . . X X X X X . . . . . . . X X X X X
Except that this goes on for over 100 columns and 30,000 rows. Each block of data is about 4 to 6 sheets printed. My boss does not want to get any blank sheets, only sheets with data on them.
I can see the pages outlined after I Print Preview. But I really don't wat to Ctrl+Click and set the print area thousands of times. Is there a macro that can do this?
I was thinking something along the line of check the range of cells for each page, and if the page has data in it, then set that page's cell range in Print Area.
I have a worksheet that is full of names (column A) and dollar amounts (column B). Many of the names are repeats.
How do I pull the largest dollar amount for each unique name in the worksheet?
I work with large spreadsheets. I was hoping that someone would know how to create a macro that will take selected cells in column C and search all of column C for exact duplicate entries. The macro envisioned would let you select cells to test. If a dulicate entry is found I would need the macro to test the cells in column E against one another. If both C & E match exact (if possible maybe display the findings in a pop up box) I would like the macro to ask me if I want to delete the duplicate entry, if yes, delete the entire row. If no, skip it and move to the next one. If C but not E match I would need the macro to ask me if I want to remove thetest cell. if yes, cut out the entire row of the TEST cell and paste it in a new sheet. If no skip it and move on.
Am I just dreaming or can this be done. If it can be done, but more info is needed let me know.
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.
I've been trying to get a graph that looks like the image below for a while now and can't seem to get it to appear how I want. I am looking to duplicate it exactly, but the issue I'm having is getting the individual bars to be separate and appropriately spaced.
[URL] .....
i want it to change the Header in E1 to the name of that price level and have the prices change according to that price level. The price level prices are currently being pulled from another tab through vlookups which lookups up the part numbers. My method only works with 1 price level right now and have no clue how to approach it with more then one price level. Also these part numbers will change positions and locations and are not permanent hence why i thought to use a vlookup.
Is there a way macro wise ( preferably through a formula ) that i can have Column E prices change according to the Price Level Entered.
In the little chart on the left, I have the customer name, when their story is due, and when it was completed. On the right, I want to show how many words per hour my journalist is averaging for each week. What I'd like to do in cells J2 through J5 is to have a formula that pulls out the total number of words written for all projects that occurred during that time frame. I can't seem to figure out the formula, though. I was thinking that I needed an array formula starting with ifferror and calling from the row functions, but I can't seem to get it to work out right.
Here's my sample sheet: sample data journalists.xlsx
I am trying to create a spreadsheet which will help us analysis the sale of each of the inventory items.
Col A: Item #
Col B: Description of Item
Col C: Standard COGS
Col D: Price
Col E: Profit %
On Sheet 1(Standard), I listed all 205 items with columns b-e also being filled in.
On Sheet #2(Actual Sold), I want to just enter the Item # and have all the other info on Col B - Col E fillied in automatically.
What I am trying to do is pull data from one spread sheet into another. The Data spread sheet has 2 columns. Date and Price. What I need to do is enter a date in to row b2 (example 2/13/2013) Then Cells c2-v2 fill in with the data from the 20 days prior to 2/13/2013. Also note that I use only dates from weekdays.
View 7 Replies View RelatedI have searched extensively for an answer to this but can't find anything.
I am pulling data off the web and it puts data down the column in the following manner:
Data
Number
Number
Data2
Number
Number
I need to extract each set of Data to another worksheet. The problem is that it will be anywhere from 1 row each to 10 rows each. In each instance, there is a blank between the two sets of Data. I've looked at ISBLANK, LOOKUP, SEARCH, etc. but nothing I've tried has worked out.
I have written a macro to pull in data from a website each day. However the macro falls over because the end of the url changes slightly each day as the website provider updates the information. Unfortunately there does not appear to be a logical pattern to the number change in the url and so I don’t think I can pre-empt what the change will be. The path through the website remains the same, but the url changes as a new csv document replaces the old one. Is there any way of writing the macro so that it opens/picks up the current url rather than a fixed url?
View 9 Replies View RelatedI went through the thread http://www.mrexcel.com/forum/showthread.php?t=302438. I have similar problem of pulling the data from http://www.nseindia.com/ site. Could you please help me?
The navigation to the page required is as below.
http://www.nseindia.com/ > Equity > Historical Data > Security-wise data >
eg. NSE Symbol: RPL, Series: ALL, From date: 01-01-2008, To date: 01-05-2008.
After clicking on the 'Get Results' button it navigates to the page with tables. At the end it says "Download file in csv format". upon clicking this a csv file opens. (http://www.nseindia.com/content/equi...-2008RPLXN.csv)
I need this page to be opened in Excel sheet. How should I go ahead?
(Basically I need historical stock data csv files of various stocks)
Even though a simple web query opens the page, it do not work if the parameters (stock name, date) are changed.
I found that the last resulting page in csv is using a "REFERRAL URL". Could this be a problem? How to get around?
I know little bit of VBA and can further work to pass variables.
I get excel files containing information listed by store for the entire company. How can I sort and pull out only the information pertaining to the stores that are in my area?
View 9 Replies View Relatedinformation from a website that does not allow web queries (or at least from Excel 2003).
I have to pull the latest data every Wednesday from this webpage: [url]
To make things even more complicated the name of the page changes every week as well (corresponding to the date).
I am having trouble pulling data from a website. I need to get data from this website:
{url}
I need the highs and the low temperatures for the next week. (just the numbers preferably). But when I go to Data>> Import External Data >> New Web Query i'm not able to get any of the numbers into Excel. This way worked fine with other websites.
I have a spreadsheet with multiple data tabs feeding a few summary tabs.
For this question I will deal with one summary tab and two data tabs, one data tab for sales and one data tab for service. On the summary tab, I want to look at vehicles sold during a certain time period-(looking to the sales tab). For each of the records in the sales tab that match the date range, I need to count the repair order activity on the service tab. The key between the summary and sales tab is the sales date, and the key between the sales and service tabs is the vehicle serial number.
I currently have multiple columns on the sales tab to perform the counts from the service tab, thus allowing me to pull the information to the summary tab. Problem is that the spreadsheet has over 2 million calculating cells and tends to take excessive amounts of time to recalc.
I have a generated list that pulls data from a different sheet. I need a formula to pull only unique values from that generated list since some values are list multiple times.
[URL] ......
In VBA I'm trying to grab numbers from a cell range (ie: A1:A10), and save them to a matrix or variable. I will later need to grab additional numbers and add it to that matrix. I have experimented several different ways, but belows demonstrates what I'm trying to do:
VB:
my_data = Range("A1:A10").Value
my_data = my_data&Range("B1:B10").Value
I have over 7000 items with a unique 6 digit item number each. They are separated buy category numbers. So category 5 may have 30 item numbers in it. column A has 555555555666666666777777777. Column B has the item numbers. I have about 200 categories.
In the workbook I have separate sheets for each category.
I need to pull all the item numbers from category 5 to sheet tab 5, all cat 6 numbers to sheet tab 6 and so on.
I do know excel but never used code.
5
562462
5
845294
5
349124
[Code]....
I am tracking 3 different payment types, and the employee, customer and dollar amount for the transactions that can not be verified. All of this data is then transferred over the the "Summary" sheet. I found the Countif formula that will tell me how many times an employee names appears on the sheet, but I was wondering if it were possible to also calculate the dollar amount associated with those transactions.
I have attached the spreadsheet to better explain what I am talking about, but below is a very small example. I do not want to calculate the info on this page, but rather add another 2 cells next to where the number of unclaimed and the % of unclaimed are calculated on the "Summary" sheet. Sample spreadsheet2.xlsx
A B C D
1 Date EmployeeCustomer $
2 1-Jan MonicaJohn Doe $65.00
3 2-Jan AshleyJane Doe $85.00
4 3-Jan MonicaJustin Doe $15.00
5 4-Jan MonicaJessica Doe $135.00
6 5-Jan AshleyJeremy Doe $100.00
7 6-Jan CheriJustine Doe $50.00
8 7-Jan CheriJace Doe $450.00
9 8-Jan JudyJackie Doe $50.00
10 9-Jan MonicaJake Doe $65.00
11 10-Jan JudyJennifer Doe$85.00
i am currently working on a file that counts things during the day and then sums them up. i have a different sheets for each day witch are named 28.07,27.07,26.07 etc'
i want the current sheet to pull data from the last sheet, lets say from cell I10. so if i'm working on the 28.07 - i use the formula " ='27.07'!I10' " and it works just fine. but i want to automate it. lets say make a cell in every sheet with the name of the previous one (i figured out how to automatically generate it) and make the formula pull from there. so lets say if i use the cell B2 for the value of the previous sheet (27.07) - it should look like " =B2!I10 "
Start Date: 1/1/08
End Date: 1/1/08
Users: 100
Description: ...whatever
What I want to do is pull just the end dates into the next column. Is there an easy way to do this or do I need to build a macro?
I don't think this may even be possible, but what I am trying to do is pull only certain information from one worksheet to another based on whether there are entries on certain dates for employees.
A sample is attached.
On the first worksheet I have a drop down menu for all employees on the second sheet. (the drop down menu pulls the names from the second sheet)
The second worksheet has all employees in Column A and to the right has all their variances by date. Most dates will be blank as they didn't have a variance.
What I would like is on the first worksheet, is to select their name from the drop down menu and have all the dates that they had variances and the variances show up.
If you take a look at the attached excel file it might explain what I am trying to do better.
I have a huge listed of assets - the column i where my info is has vasrious serial numbers.... some are a simple string of letters/numbers and the others look like this for example AG-1234567
I need to somehow pull out the ones that look like that. (AG-1234567). I only need those. Now normally i would just do a filter and pull em out however, this sheet contains 73k worth of items.
what would be the formula to perform such a task?
I would like to be able to copy a single row of data from a huge sheet containing about 7000 rows on a daily basis. This would be a non issue except that it is not in the same row from day to day. It may be in row 1624 today and 1620 tomorrow basically. In the second column is a unique ID number that says constant though. I need a macro that can find this number and copy the entire row of data to another sheet.
The second issue I am having is that this data is from a .csv file that is posted on the next business day. The name of this file changes day to day because the name of the file is the date it was from.
i need to pull data from one spread sheet and place it in a new spread sheet. and i am not that familiar with macros i am learning but i need to get this done.
View 13 Replies View Related