I have a selection of data that lists sellout information for 45 different Cable networks. What I'm hoping to accomplish is to have (on a separate sheet) a function that will automatically pull out the top 10 networks based highest sellout percentages and list them in order from highest to lowest. I don't even know where to begin or if it is even possible to set up as an automatic function.
View 3 RepliesAttached is a sample of a excel file I am using for tracking overtime.
On the sheet labeled tracking I want to gather data of the other sheets based on a date range.
All sheets other than the tracking sheet will be labeled in the date format mm-dd-yy.
On the tracking sheet in cells B2 & B3 will be the date range that I want to gather the data from.
There is 3 columns of data that I want to gather, "Balancing" "Approved" "Unknown".
I would like to return just a sum of these from the dated sheets M3:O42 based on the employee name A3:A42
I need to add data from b2:b12,d2:d12,f2:12,h2:h12 and to show the totals in L2:L7
View 5 Replies View RelatedI have a column of dollar amounts (variances) and next to each is the shift they belong to.
I am looking to get a break down of the variances for each shift.
Attached is an example of what it looks like.
I have a series of employee variances and dates for the variances in two columns.
I have another section on the same sheet where I want to track the amount of variances & occurances for certain months.
attached is an example of what I am looking to do.
I have a column of dollar amounts (variances) and next to each is the shift they belong to.
I am looking to get a break down of the variances for each shift.
Attached is an example of what it looks like.
ok lets say you have thousands of rows of raw data and you are running vlookups to pull specific data from the raw data to update your spreadsheet. Your raw data consist of some new ideas so when you vlook up, you get an error. In my case i get about 50 errors all spaced out amoung the thousands of rows or raw data. I would like to create a formula that would go and find the error and output a code/value that already exist in the raw data and output that in Cell D1 for example. The it will go and find the second error and output that into cell D2 and then the third error and output that into D3 and so on.....
The idea is that when its done a list of all the errors will have been created so then i can use other formulas to simple add the new info to the workbook. Instead of search through thousands of rows for the errow.
I have a what I believe to be a consistant formula on lines S12-S18 for gathering the sumproducts for dates on column O. Only problem is that the sumproduct formula is not working on line S18 correctly. I keep looking it over and I can't figure out why it is not returning the correct amount for that cell.
Here is the formula I am using:
=SUMPRODUCT((TEXT($O$12:$O$500,"dddd")=R18)*1)
Please see attached line S18 for the error I am getting.
In the attached WB I managed to write some code (behind Sheet1) to change the Source table, itself, into the requested layout. My question is - could this be done by ONLY Worksheets functions - preferable without any helper column - if possible. note that all 4 columns data should be transported to the target table. The upper left cell of the target/new layout table can be put in cell G1 or in cell A15.
View 4 Replies View RelatedI'm attempting to list my data... I have a dynamic table that has the following columns:
number of countries
country 1
country 2
country 3
1
Egypt
3
USA
Egypt
Scotland
2
Scotland
USA
As you may have guessed, I want trends on these countries. That would be easier to do if I had a single "Country" columns but I have to work that way.
I would have wanted to work with a pivottable (because they're so "user-friendly" (not always though^^)) but I didn't find a way to do it.
The reason why I want to work with a pivottable is to be able to link my countries results to the rest of the table.. If that's not possible, I'd still want to be able to reference them and say "USA, egypt and scotland pop up 2 times"
I found the following formula (in E2):
Code:
=INDEX(Table1[country 1]:Table1[country 3];MOD(ROWS(E$2:E2)-1;ROWS(Table1[country 1]:Table1[country 3]))+1;INT((ROWS(E$2:E2)-1)/ROWS(Table1[country 1]:Table1[country 3]]))+1)
Which works but I have to manually click-drag that cell down to complete my list. I'd rather have it grow automatically if I have to work that way.
Trying to learn the basics of how to pull data, from the same formatted excel spreadsheets, combined into one consolidated spreadsheet. I created a few examples below of what I am trying to do. Eventually, I want to be gathering data from over 200 spreadsheets at one time. I believe that a MACRO is needed to do such a thing. I am not sure of the complexity of the MACRO, so hopefully I will be able to mimic whatever needs to be done in my actual file. I have almost ZERO experience with MACROS, so if this is way above entry level MACRO.
Below I have 3 spreadsheets, and I want to collect the data from those 3 spreadsheets into the consolidated spreadsheet.
I've been given the task of automating a spreadsheet to assess whether work has been done by each employee. Every employee has their own spreadsheet, where column A is a job code, and column H contains either y or n dentoting whether they have completed the task (y) or not (n).
The main spreadsheet is designed to show any outstadning tasks across everybody so it has a list of everybodies names in column A and then any incomplete tasks will be listed from columns b onwards next to the appropriate person. Here is what I have done at the moment.
I am trying to program a macro that get the sheets in four different workbooks and put them into another workbook where my macro is located. I have managed to open the first workbook and copy the first sheet but when going for sheet two in the same workbook it gives me an error 91 stating the variable or with block variable not set on the following line:
[Code] ..........
Here is the rest of my code and attached file.
Attached File: Training Hours Macro.xlsm
I have an employee summary sheet, and another sheet where data will be entered by date.
On the summary sheet I want to gather only the last 3 dates that were entered.
Attached is an example.
I have a file that is a master report. Every month i have to pull information to it from 40-50 different files (all of these are exactly the same and the data is in the same sheets columns and rows) Is there an easier way of just pointing excel to one folders so it would automatically pull the date into mu master sheet?
View 4 Replies View RelatedI have a column of variances, these contain both negative numbers and positive numbers. I want to gather a sum of all the negative numbers and positive numbers separtely. Basically saying all the positive overeages = this amount And all the negative shortages = this amount. you can see the attached sample.
View 3 Replies View RelatedI have a spreadsheet that is updated weekly -- but every week new info is added that needs a user to input corresponding info. I use a vlookup function to link to another spreadsheet that populates the info from previous weeks and the info that is missing shows up as #N/A...
First I was using a msgbox function to get the info:
HTML Code:
For Each b In myrange
If Application.IsNA(b.Value) Then
Employee = b.Offset(0, -2).Value
SSID = InputBox("Please enter ID# for " & Employee & " :", "New Employee Found")
b.Value = SSID
End If
Next b
But it can be up to 30 different new employees... and that is time consuming.
I would like to make it more user friendly by creating ONE userform that displays all of the employees as labels -- has a text box in which to put the ID # -- and then has a drop down box to choose the type of employee (2 options). I want all of that info to go back to the reference spreadsheet so it will be saved for following weeks, and then redo the vlookup to get the info into the new weekly spreadsheet (I can do that part)....
HTML Code:
Private Sub CloseButton_Click()
Unload UserForm1
End Sub
Private Sub ComboBox1_Change()
[Code] ......
Looking for a formula to accomplish the following:
I'm trying to populate cell A31 on a worksheet titled "VolumeTotals" with the data in Cell E23 from a worksheet titled "CurrentCustomers" if the merged cells F3-F22 on worksheet "CurrentCustomers" are equal to the word "Contract".
I am looking for VBA code to extract data from each tab into master tab based on data.
I have 3 tabs (inputs)
Tab1 (Dept A-NAME) (RANGE B4:I7)
SL NO
ID
Date
Customer
Start Time
End Time
Trucks
Supervisor
1
A
2/25/2014
Customer 1
10
1
3
ABC
[Code] .....
Tab 2 (Dept B-NAME) (RANGE B4:I11)
SL NO
ID
Date
Customer
Start Time
End Time
Trucks
Supervisor
1
B
2/25/2014
Customer 3
10
1
3
RTY
[Code] .......
Tab 3 (Dept C-NAME) (RANGE B4:I7)
Is it possible to run vba code to get below result in new tab
SL NO
ID
Date
Customer
Start Time
End Time
Trucks
Supervisor
Result
1
A
2/25/2014
Customer 1
10
1
3
ABC
Dept A
[Code] ......
Condition here is date 2/25/2014, is possible when running code message box pops up to ask date, when we give conditional date it extracts those dates.
Excel -2007 & Windows 7
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 am using Excel 2010. I am a novice user.
I have a lot of data to filter / sort. I want to initially to create a filter for a column of data - which has the format similar to hierarchical paths to files. The data is a mix of text/numbers. e.g.
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_254__5_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_253__5_0/d
pathA/path_123/path_456/data_out_reg_17_0/d
pathA/path_123/path_456/data_out_reg_0_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_255__5_0/d
[Code] .........
Doing an alphabetical sort of this date would return the following order. As you can see while each strings in unique - there are many instances where they are simialr - if you ignore the unique numeric values at the end of the string.
pathA/path_123/path_456/data_out_reg_0_0/d
pathA/path_123/path_456/data_out_reg_17_0/d
pathA/path_123/path_456/data_out_reg_4_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_230__6_0/d
[Code] ......
So what I want to do is to create a filter for the strings - but ignoring the numeric bits at the end i.e.
reg_[0-9]+_+[0-9]+/d
The strings are obviiously of varying length and the number of hierarchical paths is different, so I can't split string on "/".
Similarly folder paths names can contain "_" so can't split string on this either.
As I don't know how many "/" or "-" instances there will be in the string I don't believe I can use the find function. Also as the amount of number will be different i don't think I can use =right(a1,X) either.
I may be able to search for the pattern above - as this is probabay unique - so maybe it's something like the following pseudo code:
Function GetString(txt As String) As String
With CreateObject("VBScript.RegExp")
.Pattern = "reg_d+(_)+d+//d"
GetString = .execute(txt)(0)
End With
End Function
If I do require VBA code - how do I then use this for creating a column filter? Or will I have to extract the filtered data first from the column (and its associated row data) into another worksheet to use?
Once I have the filter in place I want to create tables using the filtered data - so for example each column value above has a lot of associated data values in each row e.g
26 pathA/path_123/path_456/data_out_reg_0_0/d
32 pathA/path_123/path_456/data_out_reg_17_0/d
8 pathA/path_123/path_456/data_out_reg_4_0/d
So my table would show the name "data_out_reg" and the range of values 8-32
I have a multiple column spreadsheet (Call it- "Money") whereby I need the data identified based on a list of account numbers and return this data to a new sheet.
In "Money" I have:
IE; columns B, C respectively have cust #A100 & 20.00
columns E, F respectively have cust #B100 & 40.00
columns G,H respectively have cust #C100 & 60.00
Etc.
(above for illustration-there are 100 lines of data in these columns with varied account numbers and respective dollars)
So what I have now is a new sheet I have named "Control". I have listed all my account numbers like A100,B100 etc. in column A. These are the account numbers for ident purposes.
I need the data entered in "Money" identified by those columns B,E,G with respective amounts from C,F,H and based on the list I have in "Control" whereby in "Control" if A100 is listed in column A then the figure to be returned in column B is all the data bits found in all columns C,F,H from the entire sheet "Money". Tough to explain but ie below....
In the attached spreadsheet I track the performance of my team. I enter the AHT for my team in the work sheet named "AHT Summary".I keep updating this data every month. automatically sort the updated data and rank the agents based on their average AHT. The person with the lowest AHT should be ranked 1. Column is highlighed in green for your reference. Based on this ranking the work sheet named "Ranking" should be updated automatically. Since I am taking a weighted average for all agents, the one who gets the lowest AHT should be ranked 1st . In the ranking work sheet the agent with ranking 1 should be given 100, the second highest ranked person should get 98,third 96,fourth 94 etc.
View 6 Replies View RelatedI have Sheet1 "MASTER" and Sheet2 "Area1" and Sheet3 "Area2" etc...
My MASTER sheet has a list of employee names and the areas they work in. I have employees working in different areas, and I want to pull a list of employee names from the MASTER working in Area1 (sorted on the MASTER sheet) to column A on Sheet2, then pull a list of employee names working in Area2 to column A on Sheet3, etc...
I want it to do this in such a way that if I add an employee to Area1 on the MASTER data, it will populate that employee in the Area1 Sheet.
So basically, I'm looking at one column on the MASTER sheet to see if the area matches. Then looking at another column on the MASTER sheet to get the name. Then taking that name and transposing it to a new sheet corresponding to the area they work in.
I've attached a sample sheet. I want Column A in the Area1 sheet to reflect all names that show up on the Area1 LIST on the MASTER sheet, and nothing else. I'm using VLOOKUP to pull the rest of the data from the MASTER table.
There is a new sample workbook up now. Couldn't update it sooner due to site outage. I've removed irrelevant data to improve readability and focus on what I'm trying to achieve. Again, the main issue is scraping column E from the Master, and populating a list of all employees who match certain values in Column E on the Master in Column A of the other tabs.
I have a workbook (excel07) that I use to import data onto our purpose built database. I have some macros to sort the data and send it to the correct columns however the one stage I still have to do manually is assign each row its own unique sorting Code (in a bespoke column "B2:B999").
These are objectives and the code should contain three seperate parts (part 1a and b do not need to be seperated);
1a. The characters "PO" should be the first in the code (for sorting purposes)
1b. The persons initials (first letter of the first name and last name) found in column J2:J999. [They are sorted by name so It would also be good to add a number after the initials seperated by a space incase of multiple objectives however I could defintly live with typing these in manually]
e.g. "Joe Blogs" second objective = JB02
2. The numerical part of the stategic objective they have selected found in column H2:H999. [incase you need this these range from 1.1-1.4, 2.1-2.5,3.1-3.6 and 4.1-4.5 all with a short text strings after which shouldnt be included in the code]
e.g. "1.1 We are Committed to being nice" = 1.1
3. The year they are applicable for which will all be "12/13" however I will need to change this next March.
Therefore the final code should appear as "POJB02 3.1 12/13"
There is a 16 Character limit on these codes but all of that information is needed for sorting, filtering and report generation, the spaces could be replaced by "_" if that makes things easier.
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
51 86 REP 1 33.3% 14
42 74 REP 3 19.3% 11
57 86 REP 5 19.6% 9
46 66 REP 4 33.3% 9 ...........................
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
I'm creating a macro that will involve some form of if-else/case-switch, as well as a loop (probably), but I'm not too sure how to go about it.
Basically there are records in columns. Lets say Column A has numbers in it, for example
Cell A1 has "Three"
A2 has "Five"
A3 has "Two" all without quotes
I basically want a macro that will loop through the whole column, so if A1 contains "Three" (not case sensitive), then set B1 to "3" without quotes. If A2 contains "Two", then the macro will set B2 to "2" without quotes.
Of course this is sample data but you get the jist. It needs to loop through one column, so as to fill in the other column. You can assume that column A will always be filled, so the macro is being created to automatically fill in column B based on what is in column A.
Creating a working userform that takes data input from the user and puts it onto a spread sheet.
That data is then used to calculate a result (via the spreadsheet), and I want that result to show on the userform when the data is submitted (if that makes sense?)
Rylo thanks for your help on the other project. Here is step two for the same project.
Basically I'm going to list a step by step list of what needs to be done then attach some sample sets so you can see what I'm talking about.
Step 1 ........