Excel 2007 :: Data Sorting According To Column
Jul 26, 2012
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.
View 1 Replies
ADVERTISEMENT
Jun 30, 2014
I have a pivot table with multiple row fields and multiple column fields. One of the column fields is a Date and I need some VBA that will auto-sort the columns into ascending order by the Date column field.
E.g., if the first four column labels are "2-Jun-2010, 13-May-2009, 16-May-2013, 17-May-2012" then i want the sort to arrange them as "13-May-2009, 2-Jun-2010, 17-May-2012, 16-May-2013".
Note: This is the left to right order of the columns i'm talking about, not the top to bottom order of the rows, or the data in the rows but specifically the column labels.
I've tried googling a solution and I can find a variety of code that deals with sorting the data in the rows in all sorts of ways, but nothing on how to order the columns.
i'm using excel 07, and the source data for the pivot table has the Date field formatted as custom "dd-mmm-yyyy". This can be changed if necessary.
View 5 Replies
View Related
Oct 3, 2011
Excel 2007 - Sorting across columns
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.
View 3 Replies
View Related
Nov 26, 2011
when you sort by VBA, you can only do 3 level of sorting. is there a workaround solution for 4 or more level to sort?
I am using excel 2007 but the data excel is xls.
View 4 Replies
View Related
May 11, 2014
I'm selecting only some rows in a Table trying to sort only these rows (e.g. rows 11 thru 75 in a 200 row table), but when I hit Sort Excel always overrides my selection by selecting all the rows in the table instead. The same thing happens in VBA. I'm using Excel 2007.
View 12 Replies
View Related
May 6, 2013
I have a spreadsheet with nested subtotals. i need to sort it based on one of the nested subtotals, but maintain the rows that comprise the subtotals, together with the subtotal.
is there a way to do this? I don't want a macro because then the whole project will be done as a macro. this is just a small part of what i am doing.
sample included. my goal is to sort by column F (ABS value) high to low so that rows 8-13 are together and maintain the subtotal and on top, then rows 34-36 are together and maintain the subtotal and are next, etc.
I am in Excel 2007.
View 1 Replies
View Related
Jan 3, 2012
I have created a pivot table using a family name in row labels. The names appear in alphabetical order unless the person's name is the same as a month or day of the week. The result is that I have Mr Sun and Mr May at the top of my list, rather than listed alphabetically. Sorting the list does not solve the problem. It switches Mr Sun and Mr May, but does not included them in the main list. Is there a setting I can use that will stop Excel 2007 thinking that these words are something that they are not?
View 4 Replies
View Related
Oct 29, 2013
Using Excel 2007, I have a very simple spreadsheet made up of only two worksheets that I am using to track the songs played by a band during a current tour.
One worksheet is called "Summary" and the other is called "Setlists". In the Setlists worksheet I list every show played (10 shows so far), and every song played from each show. In the Summary worksheet I have very song listed that has been played during the tour in one column (A), then the number of times that song has been played in another column (B). Column B, the number of times each song has been played, is populated by a COUNTIF function that looks at the Setlists worksheet and counts each instance of each song.
What I want to do, very simply, is to sort on column B in order to display the list of songs from the most played to least played. That is where I run into a problem. When I sort my list of numbers, I end up with a seemingly random list that certainly isn't from most to least and I can't figure out why.
Is it possible that the COUNTIF function which populates that column of numbers is somehow throwing off the sort?
View 6 Replies
View Related
Feb 7, 2011
I have excel 2007, I have a spread sheet with approx 80 worksheets. The document grew over the year of 2010 and is not in alphabetical order. I am starting this new year wanting to have the tabs in alphabetical order. I looked in the DATA sort option...but looks like its is worksheet specific. How to make the tabs sort in alphabetical order...
View 5 Replies
View Related
Jan 30, 2014
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.
View 3 Replies
View Related
Sep 25, 2012
I'm working on a workbook that will combine a cash flow report by category and also by date. The categories can change (which I think is the easiest part) but so also can the frequency of data. For instance, you choose how you want to see the data, monthly, quarterly, annually and this will change the Row that the data should be summarized based off of.
Examples:
1) Annual: Sheet 1: Row = Rental Revenue and Column = 2001. I want to pull from sheet 2 all of the Rows that are mapped to Rental Revenue and all of the Columns that are mapped to 2001.
2) Quarterly: same idea but by quarter.
3) Monthly: No issue, just a SUMIF.
In Sheet 2 I have added a mapping at the top of each column for the Quarter, Year or Month that should be included in the Sheet 1 heading Row.
I tried using a sumproduct on both row and column using the whole data set underlying, but this did not work.
"=SUMPRODUCT(1*('Sheet 2'!$A$9:$A$140='Sheet 1'!$C28)*('Sheet 2'!$E$9:$GA$9='Sheet 1'!E$27)*('Sheet 2'!$E$10:$GA$140))",
It gives me a #N/A result.
I have tried to attach a table of what I am trying to do.
Sheet 1
Choose Frequency
1
Annual
[Code]....
View 4 Replies
View Related
Feb 26, 2014
I need to transpose data from Column A to Row 2 and down.
The data in column A is in sections of 19 rows and then a blank cell and another 19 rows of data contimuously, It is a dynamic range and can contain many thousands of Rows.
The data needs to be transposed from Column A to row 2 (row 1 has the head line for each column) so the 19 lines of data is now spread accross 19 columns in row 2 and the next section from column A is spread accross the 19 columns in row 3 and so on.
My data looks similar to the below. (Test Number 0001 starts in A1)
Test Number 0001
21-Feb-2014
Kettel
Office
Demo
[code]....
I use Excel 2007
View 6 Replies
View Related
Sep 24, 2011
I am stuck trying to sort data that looks like the following, into a format that I can use as source data for pivot tables/charts.
Excel2007ABCDEFGHIJK2MalayMalayMalayMalayMalayMalay
ChineseChinese3MaleFemaleFemaleMaleFemaleFemaleMaleMale
436-4536-4536-4525-3525-3525-3536-4536-455
DateArticleContentLapseCurrentCurrentCurrentLapseLapseCurrentCurrent612-SepRelieving 40 years - Oct 15, 1972
Thick Haze continues to blanket MalaysiaHistory42712-SepRelieving 40 years - May 5, 1972
All Sharifah wants is a pair of legsHistory1222812-SepA man and his agonyHistory3223912-SepA lesson on Sept 16History22Sheet2
I will need to group Data multiple ways. However, I cant work out a good way to sort it. Can I have a suggestion on layout that I will be able to arrange data appropriately.
View 2 Replies
View Related
Jun 6, 2014
I am using excel 2007. I have data which, instead of being posted on multiple columns, is all within 1 column. The data most simply has the characteristics of:
AAA
AAA - description
BBB
BBB - description
CCC
CCC - description
and so on..
I would like to move the descriptions from column A to column B:
AAA AAA - description
BBB BBB - description
CCC CCC - description
and so on...
What would be the best way to accomplish this?
View 4 Replies
View Related
Apr 30, 2010
I am trying to create a formula which will look at the data in columns A - I (50 rows) and copy this data to columns K - S but ignoring any blank cells. Thereby consolidating the data in the upper rows with no spaces.
View 10 Replies
View Related
Feb 23, 2012
Excel 2007. I have an Excel file that contains a data dump from an external database file with numerous analytical sheets that perform calculations. Some of these calculations utilize the SUMIFs function that was introduced in Excel 2007. This function does an outstanding job of summing a column on the data sheet based on multiple criteria.
However, someone high up in management in my organization would like to "drill down" into the data behind the
SUMIFs formulas to get a quick snapshot of the lines in the database that roll into the
SUMIFs formula. =SUMIFS(DataBase!E:E,Data!A:A,C7,DataBase!B:B,D7,DataBase!C:C,E7,DataBase!D:D,F7)
If I double click on a cell with the formula above, Excel takes me to the Database tab and selects Column E which is close, but not exactly what I need. What I really need is for Excel to only show the rows on the database sheet that make up the total in the SUMIFs formula and not the entire data dump from the database.
At present, we have to manually apply the autofilter on multiple columns to show the rows in column E that make up the total in the SUMIFs formula which is a tedious and time consuming task. Is there a way to force Excel to do this? Suggested custom database application or pivot tables, but we do not want to reinvent the wheel.
View 2 Replies
View Related
Oct 5, 2011
I have a workbook that has a sheet called CustInvData, this sheet contains 4,421 rows of invoice transaction data for 178 customers starting on row 2 (headers on row 1). I need to split the transaction data for each customer out into a workbook template based on the customer name in column A. I need each workbook named by the customer name along with a month and year (example: Bellsouth-0911.xls), this should create 178 unique workbooks. And since we sometimes have to go back and rerun invoices for previous months, I'll need to control the month and year manually in the code.
The parsed data needs to be copied to a pre-formatted invoice template. This template has 2 sheets, Sheet1 is called 'Product Summary', this is a table that uses VLOOKUP functions to read the data in Sheet2 called 'Product Details', this is the sheet the parsed data needs to be copied to for each customer invoice. The 'Product Details' sheet has formatted rows 1 thru 11, row 11 being the header row for the data from CustInvData to be copied. So the parsed data needs to start at row 12.
Last, once the data has been copied into the 'Product Details' sheet, I need the data to be SubTotaled at each change in column J (Product) and use the 'Sum' function to add a Subtotal in column L (Retail Price) for each unique product category.
Example data below, I've simplified it (the actual data array spans from columns A to Y)
Customer NameProduct Retail Price
ABC CompanyAVMPCR10
ABC CompanyAVMPCA15
[Code]....
I'm a bit of a novice with macros, but I know Excel pretty well.
Using Excel 2007 running on Windows Vista
View 2 Replies
View Related
Jul 23, 2013
I have excel sheet which is having following format
sr no | bank | branch | chk no | amount | party | bill no
1 | combobox1 | combobox1 | 123456 | 12000 | combobox1 | 123456
2 | combobox2 | combobox2 | 12346 | 2000 | combobox2 | 123456
3 | combobox3 | combobox3 | 12345 | 900 | combobox3 | 123456
4 | combobox4 | combobox4 | 13456 | 7000 | combobox4 | 123456
5 | combobox5 | combobox5 | 3456 | 18000 | combobox5 | 123456
When i try to sort amount column it need to sort and change with corresponding row but it is not happening so what should i need to do for that ?
View 2 Replies
View Related
Dec 2, 2013
I am having a small problem with microsoft excel 2007.the problem is that: my excel 2007 cannot display tooltip of the column width when I keep and drag left mouse
Show tooltip of the column width as attached file.
I also tried many ways to find settings of advance menu. But cannot.
Pic.jpg
View 2 Replies
View Related
Nov 27, 2011
Autofill column B with sequential values based on whether value in column A changes its value.
I would like to autofill column 'B' with sequential values (i.e. GenoMap1, Genomap2, Genomap3,... GenoMap10, GenoMap11, GenoMap12,... GenoMap104, GenoMap105, etc...), but changing to the sequential GenoMap# only when the value in column A changes.
This is what I imagine.
A1 "Alfiero", B1 "GenoMap1"
A2 "Alfiero", B2 "GenoMap1"
A3 "Alfiero", B3 "GenoMap1"
A4 "Allocati", B4 "GenoMap2"
A5 "Amaranto", B5 "GenoMap3"
A6 "Amaranto", B6 "GenoMap3"
A7 "Amaranto", B7 "GenoMap3"
A8 "Ambrosiano", B8 "GenoMap4"
A9 "Ambrosiano", B9 "GenoMap4"
A10 "Ambrosiano", B10 "GenoMap4"
A11 "Ambrosiano", B11 "GenoMap4"
I listed examples above of GenoMaps higher than 10 and 100 to show how I need them numbered.
I'm using MS Excel 2007 in Windows 7.
View 2 Replies
View Related
Dec 20, 2012
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?
View 3 Replies
View Related
Jan 8, 2014
I need to remove the duplicates under column B for each item under column A and I can't seem to figure out how to do it.
I'm using Excel 2007; I would prefer a VBA solution, as I have to do this on a monthly basis. However, if a formula is the best way to go, then that's just fine. Either way, I'm preparing the data for a pivot table. I am unable to post the actual document in the interest of data protection.
Essentially, I'd like to turn this-
_ A B
1 Paris Bill
2 Paris Bill
3 Paris Mike
4 Paris Derek
5 Paris Derek
6 London Mike
7 London Bill
8 London Mike
9 London Bill
10 London Derek
11 Tokyo Derek
12 Tokyo Derek
13 Tokyo Derek
into this-
_ A B
1 Paris Bill
2 Paris Mike
3 Paris Derek
4 London Mike
5 London Bill
6 London Derek
7 Tokyo Derek
In other words, each city should have only one instance of any name that accompanies it; not all names will accompany each city.
View 8 Replies
View Related
Jul 12, 2013
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.
merging and sorting files.xlsx
View 3 Replies
View Related
Feb 18, 2014
I have a large excel file with the following columns:
unique ID | Date | Time | D or N | Open | High | Low | Close | Volume
I would like to figure out how to create a table showing:
Date | Highest High of the Day | Time the High occurred | Lowest Low of Day | Time Low Occurred
I think this can be done in a macro where I sort first by the Date column then By the High column but I can't figure out how to get the rest.
View 2 Replies
View Related
Oct 16, 2013
On the attached worksheet, our guys are regularly inspected for performance. Column 'H' shows the average of their grading from each inspection (shown in cols L,P,T etc) What I would like to develop is either a macro button that when pressed sorts them all into order according to their average score in Col H, with the highest score at the top, or to have the sheet do this automatically as the data is entered. Obviously all of the data in the engineers row (all of his inspection history) will need to move up and down accordingly, but not get left behind, or mixed up with someone else's records.
View 4 Replies
View Related
Feb 10, 2013
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.
Jacob2c
Mary3b
John2a
Sally2b
Sarah2b
Joe3a
Bill3a
If I manually sort the above data into descending order of national curriculum level it will look like this:
Joe3a
Bill3a
Mary3b
John2a
Sally2b
Sarah2b
Jacob2c
but if I try to get excel to do it, obviously excel just treat the levels as alpha numeric and sorts them like this:
Mary3b
Joe3a
Bill3a
Jacob2c
Sally2b
Sarah2b
John2a
I know there must be an easy way involving creating a custom list, but I don't know how to do it.
Problem 2
A related query involves finding a way to get excel to complete the data in the Sub levels progress required column below:
NameLevelTargetSub levels progress required
Mary3b4a
Joe3a4a
Bill3a4a
Jacob2c2a
Sally2b3c
Sarah2b3c
John2a3a
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.
View 6 Replies
View Related
Sep 15, 2014
I'm trying to sort a column of numeric values largest to smallest while retaining their unique designators and color illustrations.
See attached. 3ColumnSort.xls
View 3 Replies
View Related
Jan 15, 2014
in excel where you can select a column and sort it from high to low, how can you get this to be done automaticly as the data changes?
View 2 Replies
View Related
Feb 24, 2011
I have a question regarding filtering of columns. I have set up Excel 2007 so that I can click on the drop-down menu in each column to filter them.
However, in my data set there is one blank row separating two set of rows. Now, when I apply the filter on a column, all the rows above the blank row filter correctly, but all the rows below stay un-filtered.
I guess Excel only looks at the consecutive rows, then stops when it hits a blank row. Is there any way of applying the filter beyond a blank row (i.e. the whole column)?
View 4 Replies
View Related
Mar 18, 2014
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.
View 5 Replies
View Related
