Generate List Of Data Based On Sort Data From Another Worksheet
Feb 25, 2014
I 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.
How to sort a column of data based on a custom list with more than 255 characters.
I have created a named range with 40 entries and then added data validation in the cells of column D using the above named range. However, I want to be able to sort column D in the same order as the named range but the custom data sort lists are restricted to 255 characters.
I have data in Sheet "Schedule" in range A8:C160 (including column headers), but note the length (rows) of the range is dynamic.
A8 = Customer B8 = Load Time C8 = Delivery Time
Columns B & C are formatted as h:mm:ss AM/PM, but Column B also includes text..."PRELOAD"
I would like a macro to sort the data based on column B, but the sort must follow a specific order, which is listed in Sheet "TimeSort", range A1:A50 (including header).
The custom order basically has "PRELOAD" sorting at the top, then sorting everything else chronologically starting at 6:00am.
The desired outcome would sort the range based on column B as follows...PRELOAD, 6:00:00 AM, 8:00:00 AM, 12:00:00 PM, 5:30:00 AM
I have an excel WS1 set up as DB; I want to keep this sheet for data revision. WS2,3, 4, & 5 will be data that is filtered and sorted, using WS1 as source so I want to auto copy the WS1 data. Can I just auto copy WS1 (how do I do that?) then filter and sort in each WS?
I've created drop-down lists in cells B3 and B4 (this is in worksheet 1), based on a column in Worksheet 2. I'd like Worksheet 2 to autofilter the data based on the drop-downs in B3 or B4, or both together.
I have in column A and B. A includes two (or may be more) kind of items, i.e. drink and snack. Column B has for example water next to drink and chips next to snack. The list is huge.
How can I generate a drop down list with data which uses the A column to identify the items which it should include. I want to make two separate drop down lists for drink and snack and each should include only those options which belong to those categories. I.e. drink list should be like water, cola, pepsi, etc. The problem is that the list is huge and it's not possible to sort it. Some kind of if statement structure with named range? The drop down should be dynamic in a sense that if I add line to data, it should be picked to the right drop down menu.
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.
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.
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
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.
I am looking for macro where it can generate multiple workbooks with a single worksheet data source. I have this worksheet with data which is look like this:
[IMG][/IMG]
This data will be places in 1 workbook for each of line with given file name as page number above those workbooks generated will have same information for each line but at different cell position such as: take this example at line 3
and
Microsoft Excel 2007 Window 7Pro 64bit
This line could be up to 50 lines of data. I try google search but found most about consolidate multiple workbooks to single worksheet. I do have VBA reference that I refer to from [URL] ......
How to put the information from the worksheet into specific position in the workbooks.
I was trying to use the VBA code to generate a data table:
Private Sub createDataTable(WS As Worksheet, initialRow As Integer, numCol As Integer, numRows As Integer) Dim initialCell As Range 'specify the upper left cell Dim RefCell As Range 'the reference cell on the caculator sheet
'activate source sheet ActiveWorkbook.Sheets("Calculator").Activate Set RefCell = ActiveSheet.Cells(2, 3) WS.Activate Set initialCell = ActiveSheet.Cells(initialRow, 1) initialCell.offset(numRows, numCol).Select Selection.Table ColumnInput:=RefCell 'the calculation should be automatic, if not, then calculate WS.Calculate End Sub
and it kept giving me an input celll not valid error on this line: Selection.Table ColumnInput:=RefCell the thing is, I have defined RefCell as a range object, so it should be OK rite?
I currently have a large spreadsheet that multiple people fill out. Each person fills out all the information in a row. At the end of the row, I would like a button that says "Generate Form" so that when clicked, a new sheet automatically opens with a template form that I created and is already filled out with the information that was just inputted into the spreadsheet. Also, there are four different template forms that could generate. For example, there are forms A, B, C and D. If the user inputs "B" into the first column of the row, then when he goes to click "Generate Form", a new sheet is created with all the information filled out in Form B.
I am trying to generate a list based on the value of a cell. That list will then be used as the range for a drop-down list. Example: Cell A1 returns a value of 15 A drop-down list displays the values 1 through 15. Cell A1 changes to 20 Drop-down list displays the values 1 through 20. I assume I'll need a two-step macro to accomplish this but I can't figure out the logic to populate the drop-down.
I am having trouble finding info on this. I am trying to create a custom list to sort data.
I only want to sort two of the letters in my first Level or Column. For instance I want to sort Column A Alphabeticaly as A, B, C, D, but once I get to E, have the rest of the list in a random order that is sorted by my next level. Is there a wildcard character that can be used to have my next sorting level take over once I get to E?
How to generate a list base on the differences of another list. let me explain; I have a list of combination A1:F2200 each row of course the numbers have difference, example:
My problem this time is in regards to auto sorting a table as data is changed in another worksheet and is auto copied within the said table.
I.E. I need the Fax page (second last), the table has to auto sort based on column " C ". This is easily done using custom sort but can it be done automatically ? I want to lock the page so nobody modifies it.
I attached a copy of my work so far, using Excel 2007.
All the sheets are locked except " Player DATA " and " Fax ".
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....
The first sheet is the row data given to me, the second one is where I want my data to be analysed automatically by macros.
I managed to create a pivot table manually to give me the info I want but then I still have to copy all the results from the pivot table sheet to my analysis sheet.
How can I create a macro able to do all that by it self? I tried to record a macro using the recorder but then when I try to play it it gives me an error straight at the beginning in the definition of the pivot table I think.
An example of what I want the pivot table to do is: in the first sheet, go in the small table in column K and L, take the value of L1, then in the row data in column A to J, in column C look for the value in L1, once found, look for the value 1 in column J then do the average on the values in column E and put the result in my second sheet in cell F5 then do the same but look for 2 in column J and put it in F8, then 3 in F11, ... until 7 found (the data is in ss.000 and so the results in the second sheet should be formated the same way) then do the same all over again for value in L2 etc etc etc
this should be done for the 6 values in column L, each having values up to 7 in the J column.
Then I need to do the same for other columns and not only averages but minimums as well but I can adapt the code I think.
I havet he following code which sorts data. If there is no data to sort I keep on getting a run time error. Could I add something to my code to prevent the run-time error, as sometime there won't be any data to sort. The code runs when I switch to the worksheet in question.
Sub SortMeetings() Dim iCTR As Integer Dim yCTR As Integer Dim zCTR As Integer
zCTR = 11 For iCTR = 12 To 23 For yCTR = 1 To 10 If Len(Range("D" & iCTR).Offset(0, yCTR)) 0 Then Range("AA" & zCTR).Value = Format(Range("D" & iCTR).Offset(0, yCTR), "HH:MM") & " " & Range("D" & iCTR).Value zCTR = zCTR + 1 End If Next yCTR Next iCTR Range("AA11:AA" & zCTR).Select Selection.Sort Key1:=Range("AA11"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub
I have a spreadsheet that track orders. I want to be able to add a bit code to my current VBA script that sorts the data based on the values in a particular column (smallest to largest).
Current table looks like this:
Order Number Date Quantity Colour Priority
548562 12-09-2012 56 VBA formula 2
[code]....
I want to add be able to sort the data, smallest to largest based on Column E (tittle Priority)
I am attempting to create a macro to generate emails based on data in a sheet. The goal is to run the Macro, and have it generate emails to send to contractors letting them know what they are going to be paid. For instance:
Name in Column J Email in Column L Memo in Column N Balance in Column T Due Date in Column P Week Ending Date in Column H
Now what I would like to happen, is to tie a macro into a button that will create the email as follows:
To Field: Email address from Column L Subject: "Company Payment Remittance Payment Date *Date from Column P*" Body: Hello *Name from Column J*, For *WE Date in Column H* you will be paid *Balance from Column T* for the time worked of *Memo in Column N*
Now the tricky part is that I want the email to contain all line items for each email address. So instead of sending one email per line, have the macro automatically put all of the information that needs to be sent to one email address into the message. I don't know if that is possible, but it sure would make my life easier if it was.
I have attached a sample workbook of the data that will be used
I have a workbook that has 30 tabs in it. Each tab is a report card for students. What I'd like to do is create another tab with a button on it that when I hit the button it will search through each tabs range of D12:D40, D48:D76, D84:D112, D120:D136, J12:J40, J48:J76, J84:J112, and J120:J136. And if any of these cells has an MS in them then this new sheet I have created will list each students name which is in cell E5 and list what they recieved the MS for. This will be in the same row number but in column B. So if cell D12 has an MS in it then this report will list the students name and what's in cell B12.
I am trying to create a drop down list that when selected will display data in the 10 rows below and 4 columns, so a total of 40 cells. I would like the data to be based on the selection in the drop down list.
i have 27 different combinations of the above list with up to 10 different rows. I have a label for each one, but i cannot get the rows below my drop down list to display the proper values base don the selection in the drop down list.
I've attached a spreadsheet showing the effect I'm trying to achieve. A list (which changes weekly) contains employee details and length of service. Based on the length of service I need to build a new list grouping the employees based on the service length.
The table, which works correctly, looks in another worksheet 'Report' for 'Example 1' within the date range of August. This uses COUNTIFS.
Ideally, what I'm wanting to do is have an auto-filter on each of the cells - 'Example 1', 'Example 2', so if a user wants to see what rows on the 'Report' sheet, falls into this criteria, it auto filters the other worksheet, and takes them too it.
The action would be - Click on the cell of 'Example 1', the macro auto-filters based on the cells value in column F of the 'Report' sheet, and takes the user there. The macro would have to take into account the date filter as well, which uses cells A1,B1 for August, and C1,D1, for September.
