So I have started working for a medical supply company and sorting through the prices and item numbers of thousands of line items is difficult to do manually. We have a computer guy who can take two files and match certain columns up.
For example, I have a file with the Item Code and Our Costs, and a different file with Item Code and a different companies prices (so I know what prices to beat while maintaining a higher margin). Every time I need to do this sorting I need to go to my computer guy and give him the two files to merge them together so he can match the thousands of items of our competitors prices to line them up with our costs for the proper item.
Example is attached as an excel file, although it is a hundredth of the size of many files I work with.
I am using Excel 2007 and I have a worksheet with multiple columns, some of these columns are protected and some are not.
I then lock the worksheet with a password so the user can't edit certain cells that are protected but can modify the unprotected cells, the problem is the user cannot use the filter or sort the workbook.
Is there a way to allow filtering or sorting but still lock down the worksheet.
I have a continuously growing list of data that I need to sort onto another sheet automatically. The sorting data is always in column A but sometimes is not always in the same format; however the sorting data is always the first 6 digits of the data in the cell. When i add the new data to the first sheet I would like the data to be automatically sorted onto the second sheet with 1 row in between each group of data.
I'm using Excel 2010 and I applied a Data Filter to a simple table. I then messed around with the drop downs in each column, sorting the data by different criteria. After doing this, is there a simple way to get the table to revert back to its original order/form?
I am a teacher and have a recurring problem when trying to sort data in excel. The problem involves national curriculum sub levels. For those not familiar with these levels, there is a main level then 3 sub levels. The problem is that they are counter intuative to the way excel sorts data. The sub levels work like this:
4a - highest 4b 4c 3a 3b 3c 2a 2b 2c - lowest
There are lower and higher levels, but you get the idea. If I have a set of pupil data that I want to sort into decending order, I find it difficult in Excel. E.g.
At the moment I manually complete this. E.g. for Mary the Sub levels progress required would be 4, as there are 4 sub levels between 3b and 4a. For Joe it would be 3 as there are 3 sub levels between 3a and 4a, etc. Is there a quick way to get excel to calculate this.
I have some data in a Excel 2007 workbook in the range a1:d100 and I want to sort the data according to "A" column, which is the first column of the range and when I am giving data sort it is prompting to select the column, where as in 2003 it will prompt to select the column but by default it will take the first column of the range, so if we want the first column the we can hit enter key otherwise need to change the column name which will save time.
My question is that whether there is any setting I can make at the time of sorting data and by default it will take the first column of the range.
I've got large set of data(Column E) for dates from 2007 to 2013(Column B). Here dates are in chronological order.(Attachement) Now I want to match the data in to dates in Column H. Here dates are in random order. I used =INDEX(E3:E40582; MATCH(H3;B3:B40582; 0)). But it doesn't work.
I have a problem on auto-sorting my data after clicking the "x" or "Close" button on DataForm. Im creating a command button that trigger a macro to open an autofill DataForm. I want a code that everytime i ADD or EDIT using the DataForm, it will automatically sort the Column A after clicking the "x" or "Close" button. Below is the code of my macro:
Code: Sub OpenForm() Sheets("DATA").Select ActiveSheet.ShowDataForm End Sub
So I have a guest list workbook. There are two sheets. On the first one is a list of names on Column A. On Column B is a classification: 'C' if confirmed; "D" for declined; "I" for pending. I want to have all the names with "C" on sheet 1 appear on sheet 2 automatically.
I work for one half of a joint venture & am responsible for planning & expediting. The other half does purchasing. The bi-weekly PO download reports I receive are less than useful. I have already written the code to delete undesired sheets & add, format, and enter headers for a "Summary" sheet.
I need code to move to the next row, and run formulas to pull data from the next sheets, and repeat until there are no more sheets.
The number of sheets will vary from one download to the next, and the sheet names will vary from one download to the next.
Following are example formulas that need to be run on successive rows while pulling from successive sheets.
I have 2 workbooks in Excel 2010, each contain just 1 sheet. (see attached) I need to compare on sheet 1, cell D1 and column A:A (this column will be much longer), with the data in columns C:C & A:A on sheet 2, if a corresponding match is found, the data contained in column D on the same row on sheet 2 is written to the cell with the matching data in sheet 1.
In this attached example, this is a portion of a actual work procedure. The yellow highlighted cells are the same work element within the master procedure but one is for one type of machine while the other is for same model but added options. This is a pattern throughout the entire worksheet. Basically 1 model with several different types of options. I don't want to have unnecessary elements in a work procedure.
So what I am trying to accomplish, I want to create a userform or dashboard which will allow selection of model #, model type #, then based off those selections have excel populate a worksheet with all correct procedures based off model type choice.
In this example there is 6 model variations, all share same basic procedure, but all have their own work procedure based on options, so my above approach will allow me to make changes to the master procedure only instead of changing 6 procedures.
I have created a userform with multipage, has two page that add new record in a excel sheet. Data has a unique reference no.(TxtRef.Value) for each record. I am trying to add a button to load the added data for a specific record using reference no back to userform so that it can be updated and overwrite back to the sheet in the same row, So far it is adding new record correctly. I do not know how to populate all the fields of the of an existing record and overwrite it back to the same row instead of adding a new record. Below is my codes
Sheet 2, Row 1 column B-E contain restraurant names (only on the top row, like field names).. i.e. I manually put the date in because typically the projected date is different from the actual review date.
-A----------- B ----------------C ------D-------- E----- Date |Ruby Tuesdays|Olive Garden|TGIF|Ruths Chris|
I need the data from Sheet 1 Column B moved to sheet 2 in the next open row (i currently have data in row 1..the field names and down to row 35). This will be continuous so each time i need it to add the score as a new row in the correct field (restaurant name), IF the restaurant isnt listed, I want a new field named with the restaurant name and then place the score in the correct row and column. So, in the example I'd need Outback added.
I have a workbook with data on sheet1 and a summary on sheet 2. I want to use two drop down boxes on sheet 2 (B3 and B6) to look up matching data from sheet 2. What formula is best to enable these two look_up values to be used? I saw an example where the two values were separated by a comma within {} i.e. {B2,B6}, but only got an error when I entered the formula into my Excel 2010 sheet.
I have previously used VLOOKUP, but this comes back saying I have to many arguments. I can attach the workbook if that makes it easier to explain.
I have a database being exported to a spreadsheet with over 40 fields (columns) and 1000+ clients (rows). One of the fields (column C) incidates that referral source. I would like to copy & paste the list into sheet 1 and have 5 seperate sheets for each of the 5 different referral sources. So that when I go to sheet 2 the only thing I will see are the clients and their 40 associated fields that are referred (column C) by say "John Smith"
Is there a function or script that will search column C for "John Smith" and then fill in all the 40 fields on sheet 2.
A B 1 List 2 things that went well during the meeting 2 Election of Chair Election of Vice Chair 3 Powerpoint Presentation 4 Presentations were informative Everyone at the meeting took part in Q&A 5 6 Group discussed Gardens Upkeep We were mostly in agreement 7 For each meeting attendee (row) they will give their answer to the question (A1). Some will write no answer, some 1 answer, some 2 answers.
I need to list these answers in another sheet on seperate rows, thus seperate cells, in one column. Without having a gap between each of the answers on that sheet.
I have been wracking my brains and came upon this site searching via that best loved search engine we all know. I would be very greatful if anyone could help me here. I'll be watching this closely, nothing else to do. so I'll be giving quick replies.
We are needing to do a two tier sort in a protected spreadsheet. Is this possible?
The two columns that need to be sorted have protected cells between them.
If there a way to ad a button that will automatically do this sort for the user... as in can I add a button some how in one of the empty cells that will do it?
Here is the sheet. [URL] ..........
Looks like I need to sort column A thru Z, except W.
I have a dorm roster on one sheet and the other sheet is an Alpha Roster. I want it to automatically sort alphabetically (column B) . Any time I change the roster, the Alpha Roster sheet doesn't automatically sort, I have to hilight and re-sort it again.
I updated the file, there are 4 tabs at the bottom. 1st Floor, 2nd Floor, and 3rd Floor. I want to have all the names, room numbers, and phases (ph) in alphabetical order on the Alpha roster tab. And when ever I make changes to one of the Floors, it will automatically update it the Alpha Roster.
I have a list of movie titles A3-A9999(Infinity), their disc numbers in B3-B9999(Infinity). The titles being written in text, of course.
All I want to do, is have another list, in the same sheet in D3-D9999 that will be the same list as the original list that I input movies onto, except alphabetical and automatic from when I type in the movie title in the original list. When i just use "=A1, =A2, =A3 ~~~ =A999" in the D cells, then try to sort, it only sorts by the cell numbers, not by the actual text that I want it to.
Is there a way to do this? I know excel relatively well, but I'm definitely a noob compared to experts.
I have a sheet with over a thousand part numbers (lines of data) whereby they are uniquely colored.
I am looking for code to allow the Macro to Sort the lines by the color of the line, identical to the Excel provided function. However, in that one- it is tedious to add the levels for the multiple Colors going on and I would like the Macro to perform it all at once.
So, I have five unique colors (Maybe doesn't matter)that remain constant: Red-Color Index:3, Yellow-color Index:6, Lime-Color Index:43, Gold-Color Index:44, and Gray-50%- Color Index:16? (I think).
So- when I run the Macro I would like it to Sort the data and group all the Colored lines (Parts) in series (doesn't matter the color order). In other words if there are 50 part numbers red and 100 part numbers Lime then Rows 1-50 are all the Filtered Red parts and Rows 51-150 are all the Lime and so on for all the other Colors.
I have tried to use absolute references (perhaps I didn't do it right) but it didn't work. Below is the code that does work but I need to first make the sheet visible then activate it. Sub Row_Sort(tmpsheet As String) ' Sort the first 4 columns of the tmpsheet sheet
Dim tmprng As Range Dim OldActiveSheet As Worksheet
' Activate the desired workbook (in this case Linewkbk) Workbooks(LineWkbk.Name).Activate
' Save old active sheet Set OldActiveSheet = ActiveSheet.............
Need to sort part of an Excel worksheet across multiple columns.
For example data is:
ABCDEF1 Machine 4394264394294394434399834399842 Model TD860TD400TD860TD620TD6203 Pgm OPTABCOPTEP2EP24 Start 5/31/119/1/115/1/096/15/092/1/085 End 2/28/158/31/125/31/114/30/108/31/09
I need to sort it first by Pgm, within Pgm by Model, and within Model by Machine. I do not need to sort on Start or End
The results should look like: ABCDEF1 Machine 4394294399834399844394264394432 Model TD400TD620TD700TD860TD8603 Pgm ABCEP2EP2OPTOPT4 Start 9/1/116/15/092/1/085/31/115/1/095 End 8/31/124/30/108/31/092/28/155/31/11
After selecting the data to be sorted (columns B,C,D,E,F), I tried using "Sort Left to Right" under DATA-SORT-OPTIONS but it doesn't seem to work.
I am trying to auto-populate a table with data looked up from another sheet. The functions I have used are: Data Validation, vlookup, ISerror and if functions.
Cell B4 in sheet2 uses Data Validation to pick data from sheet1 (in this case "tax") and auto populate the table with the information- Job type, Name, employee type and Job title. If i change from Tax to HR, the table should get auto-populated with the correct information.
I have setup vlookups but I am unable to get it working. I am a novice at using vlookups (I learn it just 2 days ago) and am struggling to get it work. I am not even sure if vlookup is the right tool to get this job done.
