Lookup Or Data Change Based Upon The Data Reported In Column
Mar 7, 2008
I need to pull info to additional tabs. The problem I am having is that the data is sorted daily based upon performance numbers, so the REP names in column A can change based upon the data reported in column C.
What I want to accomplish:
Pull daily data for each unique name in column A to a team tab/worksheet that will be used to report that team's daily data.
Below is a representation of the data I need to break down by Rep Name and Team.
Rep DATA DATA DATA DATA DATA DATA DATA
1 2 3 4 5 6 7 REP 2 27.5% 14
I have data for various firms - such as revenues, employees, etc. - for multiple time periods. There is no uniformity, so for some firms I'll have data for 8 time periods, for others for 1, for others for 30, etc. I have hundreds of columns, but the three relevant ones are: in one column is firm ID, in another column in reporting date, and in another column is number of employees.
The attached sample data will make it easier to understand.
I'd like a summary table that tells me the most recent reported number of employees for each firm, and the date of that reported figure. If that firm never reported any employee figures, then the summary table should still have a row for that firm ID, but blanks in the 'reporting date' and 'employees' columns. So, the summary table should be 3 columns - firm ID, reporting date and employees.
The challenging aspect is that the most recent reported employee figure is not always the most recent date on which we got data from the firm (so I can't just do a sort followed by a 'remove duplicates'). It'll need either a multi-step formula solution or a VBA code (that's my guess).
The attached sample data will make it easier to understand. It has the form of data I have and the desired output form.
I have a spreadsheet in which column "c" represents a department: 01, 02, 03, etc. every time this value changes I want to do a subtotal. The code I've come up with thus far is:
Sub Subtotal()
Dim lrow As Long For lrow = Cells(Cells.Rows.Count, "c").End(xlUp).Row To 2 Step -1 if cells(lrow, "c") cells(lrow-1, "c" then range Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(4, 5, 6), _ Replace:=True, PageBreaks:=False, SummaryBelowData:=True ActiveSheet.Outline.ShowLevels RowLevels:=2 End Sub
But, I can't figure out how to get set my range to include all the data that pertains to a department to do the subtotal.
I am trying to find a way to populate a column based on the changes in the data of another column...
For example, I want to fill in the code column....so when the customers cost changes, I want a numeric code to populate & change. So for the first 5 lines, the code could be 21, and then when the cost changes to $37.51, want the code to change to 22.
I have a spreadsheet with data in a1:p100 and i'm trying to find a macro that anytime the cell contents change in either column B or Column F it copies the entire row to another tab in the spreadsheet.
I have a excel spreadsheet that has employee names, email address and DOB. The DOB format is 23/03. I need a program that will lookup the DOB row and when it matches the value I enter it extracts the employee name and email address. Once it has this information I need it to copy into an already created email template and send its off. On some days there are more than 1 person with a birthday so it will need to send multiple emails.
I have a tab that has 2 columns of data and I want to be able to return a value in column A if my data matches column B. If column B has the text TRUETRUE, I want to bring back the corresponding data in Column A. How do I return all the data in Column A for all the TRUETRUEs in column B? I can only get the first instance of TRUETRUE.
I have a excel worksheet with the following columns: First name, Last Name, Email address, domain of email, product type, date registered. The list consists of about 50,000 entries. I want to sort the list by the domain of email(which I am able to do already) Once this is done, I want to find all instances of where a domain appears at least 10 times on the list(such as webmessenger.com appears 40 times, so I want to get that data).
For those instances where the domain appears at least 10 times, I want to pull those rows out of the intial list and put them in a new list(the new list will be sorted by domain and will only have people who have a domain which appears at least 10 times). To make this a bit more clear, The initial list I have is a list of people who registered to use the software my employer makes. We are trying to locate companies which may have many people using our consumer version of the software. When there is a large amount of people in the same organization using our software, it would benefit them to upgrade to the enterprise version due to enhanced managment features. By running this filter, I can see which companies have at least 10 users registered to use our software. Of course I will remove any Gmail/yahoo mail/msn/hotmail... pretty much any public email domains and just leave the ones that are obviously corporate emails.
So far, I think it probably has to be done with a pivot table... I was able to get a table that tells me how many instances occur from each domain, but I cannot get it to display the actual data(it just says IE. company.com 200, yahoo 120, etc... I need it to show me the 200 rows of company.com emails and extract them to a new sheet so that I can then follow up with company.com and see if they are interested in the corporate version.)
I have data in one column which is text based (A, B, C, D, E) and in the accompanying column is a response (1, 2, 3, 4, 5).
I want to be able to find those who answered 1 and then group to letters - so if 'A', 'D' and 'E' replied '1' then I want the result of the formula to be ADE.
I have been doing this manually and just typing the results in from a print-out but am hoping I can automate and get Excel to do the work for me to eliminate inputting errors!
show/display data based on month criteria, with selected month in cell H2 (as start date) and I2 (as end date) my expected result start from cell H7, i called "blue area"..
I have a bit of a challenge. You'll have to take a look at the attached files to understand what I am trying to do. I am asking for your advice. I am creating multiple spreadsheets (QPPcompany.xls) that will refer back to a master data(QPPdata.xls) file.
I am trying to figure out how to best set up the data file so that I can do lookups on the data from the referencing files. Here's the challenge. I need to be able to find in the data page the company name in Column A and then how many L's (or C's or P's) they had in a specified month. So for instance I need to be able to find the number 3 in cell E6 and place it in B15 of the referencing file. The required criteria for lookup are Company Name, Month and then L, C or P.
So my question is, is this possible with how my data file is currently set up? I think it may be a problem to have the month/year in row 4 refer to 3 different colums for each month (L, C and P). I don't mind making changes to the data file to make these lookups possible and I think it may be necessary.
I have an array in excel, 4 columns: Month, Day, Stock, Price. I dont know the most efficient way to handle arrays. I know i can loop through an entire array to find a specific value, but if i have a large array, this doesnt seem efficient.
For example, I would like to find the price in an Array (or Range), when i know the value of two columns. If this were in a database, I would write sql like this: " select Price from TABLE where month = 1 and Day = 3". The result would be 40.21. How can i do this with an array? I have attached a sample spreadsheet with the example mentioned above..
I have a work book with several work sheets, what I'm trying to do or find a a method of coping data from one sheet to another provided the data in any given cell matches a specific criteria. Example below:
Work sheet 1 is the primary sheet in the work book on this sheet I have a cell with a text value (Bob). Ok on the next work sheet 2 I need to search for a cell that contains (Bob). When or if it is found I need information from other cells in that row (where Bob was found) copied to work sheet 1 in a specified row and colum. If it is not found a negative value can be placed in the specified cells.
And I have set up a seperate worksheet (2) as an invoice. What I have been trying to do is when I enter a qty in worksheet (1), worksheet (2) will reconise an entry in the qty colum an copy selected fields/ cells. I can do a "=" or use an "if" statement looking at anything greater than "0". but is there a way I can get the invoice worksheet to ignore any lines with "0". So it will only copy over the information with only qtys for the invoice ?.
I have a book with multiple tabs in it. The first is a master customer file, including customer numbers. The next two tabs include multiple references to the same customers because they list multiple shipping addresses and contacts for each customer on a separate line. I want to be able to have the additional tabs refer to the master list and find the customer name, then return the corresponding customer # in the previous column for that customer. This is what I came up with: =IF(B:B=Sheet2!B:B,Sheet1!A:A,0). But the results simply returns numbers that increase by 1. It should repeat the same customer # for the number of times that customer name is listed based on how many addresses or contacts there are. What am I not doing right?
I have this nice formula (listed below) that I'm trying to use to get the employee names that belong to each manager. How ever using the first formula I only get the first name in each cell and using the second formula (associates is a define name for the range A1:A70) gives me name that do not belong the that manager....
I would like this formula to only pull names from the manager that is listed in the C column....
Got a bit of a stinker here. (Well, it is for me anyway. I haven't used Excel in months)!
I want to use a VLOOKUP function (I think) to retrieve data from a list of Exchange Rates on a separate sheet.
The user enters a 'Period' number, selects an 'exchange' variable (the blue text cells). The hope is to lookup the data on the 'FX RATES 06-07' sheet and return the result in the green cells.
I've tried using VLOOKUP but one of the arguements specifies that you nominate a column to return the information - that's the problem, we don't know that information when asking Excel.
Table 1 has Column A, with 500 unique names/codes listed. Table 2 has Column A with the 500 names, plus 500 more mixed in (all unique). Table 2 also has columns B-G, with values corresponding to each of names in Column A.
Basically, what I'd like to do is filter out from Table 2 the 500 names (and their corresponding info in columns B-G) not listed in Table 1.
I've tried to look around, but most answers seem to be in reference to situations in which Column A has numerical values rather names.
from data lsts i select unit type , then hinge type then code.... the problem is that as soon as i select code all the values (H W D ) in the next three cells should automatically change.
the values against each cabinet code are also provided in the same sheet.
The sheet I have attached has three tabs one is a master sheet another is the production input and the last is an engineering input section, I have coded the production input section to input into cells and email the engineers when they have a new job, the issue i have is with the engineering input.
The engineers can currently go into this sheet and input the job number onto the engineering input section and it will show them the current issue from the master sheet (I have already entered 1002 in it), what I want them to be able to do is input into the white sections below the description and when they click on update entry a macro takes the job number entered into B3 and saves the information onto the mastersheet in the correct column/row which corresponds to the job they are updating.
However the engineers may need to go into the same job several times and update different information therefore the fields need to not paste into the cells if the input sections are blank.
Code: Private Sub GetColData() Dim SrchRng As Range Dim SrchVal As Range
[Code]...
And it is also possible by using Column Number to retrieve the data SrchTxt, which is in a cell in the Row, in which "Roy" is found. This Row in this example is Row 5. Column Number may be anything within the search Range. Column Number is 3 in this example.
In Sheet1 "Poet" is in C1. (SrchTxt)
How to retrieve data of the cell in Row 1 in the column where "Roy" is found?
I have a simple lists containing two columns. One column contains a five digit number and the other a vendor name. The vendor name in most cases is two to four words. I am wanting to type in a partial string of the vendor name and it return to me the 5 digit 'vendor' number.
Col A Col B 20567 3M Electrical, Inc.
I want to type in '3m' or '3M' or 'electrical' or 'ELECTRI' and it return the 20567. The other part of this is that there may be two rows with the same info in which I would need to see both...
Col A Col B 20567 3M Electrical, Inc. 21789 3M Tape Division
Is this possible with standard lookup features in Excel or does someone have a VbScript or macro that will accomplish this?
I need to bring in values into one worksheet from another worksheet using row 1 and column A as criteria. I have previously done this using Sumproduct. However the complicated issue with this new worksheet is the setout. The source worksheet (see attached) has the actual data spread across columns. How can I bring in the values to the 'Summary' sheet from the '0607' sheet using the Employee number and the seg code as 2 criterias to lookup and bring in the resulting data (which is spread out in the purple area in the '0607' worksheet).
I have data I use in a Pareto chart. This data is found somewhere in cell range A18:AF20. I want the chart to adjust itself to only the cell range with data. It may be A18:M18 or perhaps A18:AB18.
Is there any way to set the cell to look at only the cells with data in them?
