Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Sorting columns date wise Results

Hello,

I have a sheet where the schedule of the examination is spead on different dates. I wish to sort this schedule in such a manner that I am able to see the details in ascending order of the dates eg: on what date is the first exam and its details subject and no. of candidates etc.

Thank you,

Raheel

******** ******************** ************************************************************************>Microsoft Excel - CIE candidates dates and numbers.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=
ABCDEFGHIJK1Syllabus CodeSyllabus TitleQualificationNumber EnteredPAPER 1PAPER 2PAPER 3PAPER 4PAPER 5PAPER 6PAPER 72418Information TechnologyIGCSE3415/05/2008      3450Business StudiesIGCSE1912/05/200803/06/2008     4452AccountingIGCSE2711/06/200813/05/200821/05/2008    5455EconomicsIGCSE609/06/200802/06/200809/06/200802/06/2008 20/05/2008 6500First Language EnglishIGCSE1316/05/200816/05/200823/05/2008    7508First Language ArabicIGCSE2012/05/200814/05/2008     8510English as a Second LanguageIGCSE5016/05/200816/05/200823/05/200823/05/2008   9544ArabicIGCSE212/05/200814/05/2008     10580Mathematics (w/out coursework)IGCSE4615/05/200815/05/200819/05/200819/05/2008   11610BiologyIGCSE4103/06/200821/05/2008  13/05/200813/05/2008 12620ChemistryIGCSE5010/06/200812/05/2008  22/05/200822/05/2008 13625PhysicsIGCSE4911/06/200802/06/2008  20/05/200820/05/2008 142059Pakistan StudiesGCE O Level219/05/200804/06/2008     153247First Language UrduGCE O Level114/05/200822/05/2008     163248Second Language UrduGCE O Level514/05/200822/05/2008     178686Urdu LanguageGCE AS & A Level1 12/05/200813/05/2008    188693English LanguageGCE AS & A Level110/06/200811/06/2008     199700BiologyGCE AS & A Level4513/06/200811/06/200827/05/2008    209701ChemistryGCE AS & A Level3212/06/200830/05/200805/06/2008    219702PhysicsGCE AS & A Level3612/06/200830/05/200805/06/2008    229706AccountingGCE AS & A Level404/06/200804/06/200813/06/200810/06/2008   239707Business StudiesGCE AS & A Level1221/05/200805/06/2008

Hello
I am entering date wise data into worksheet for every date of data I have
new worksheet now I want to sort all worksheet together as per column ‚ÄúA‚ÄĚ and
then after all sorted item of column ‚ÄúA‚ÄĚ has to set next column after sorting
order is change.

For E.g. after sorting A wise when B come it should transfer to next column.
I have 5 field (heading) i.e. when sorting order is change it should copy to
next column i.e. column no. 6

Any help.

Shital Shah

Dear sir,

i have exported data from SAP and the date is like 10.02.09 format i want to sort it ascending or descending wise
i did all the effort to do so.... i change the format from 10.02.09 to 10/02/2009 etc.... but partly done and partly not

pls let me guide how to sort

regds
devesh

Hi,

I am maintaining our receivables in an excel files and maintaining a column on the expected dates of payments (mm/dd/yy) receipts.

I use Pivot Table for reporting purposes. I am looking for a way to take the reports on expected payments MONTHLY. I tried many possibilities but I keep getting reports date-wise instead of month-wise. Can someone help.

Hello
I am entering date wise data into worksheet for every date of data I have
new worksheet now I want to sort all worksheet together as per column ‚ÄúA‚ÄĚ and
then after all sorted item of column ‚ÄúA‚ÄĚ has to set next column after sorting
order is change.

For E.g. after sorting A wise when B come it should transfer to next column.
I have 5 field (heading) i.e. when sorting order is change it should copy to
next column i.e. column no. 6

Any help.

Shital Shah

I am doing a lot of manual work and I tried finding relevant macros, but unfortunately could not find any.
Basically Let us think my excel sheet has 4 columns ( A,B,C,D)
I arrive at A,B columns in my excel after doing a lot of screening on the current MONTH's companies filings (I remove certain marcaps above and below my range, I remove not my sectors data, etc etc)
Column A has Company Name( upper case, lower case and sometimes combination)
Column B has the date (I am doing month wise now)
Once these 2 columns are ready
Then, I run a web query from website, that downloads entire month's filings with SEC with HYPERLINKS
Column C has Company Name with HYPERLINKS (Not necessarily same formatting Case as Col A)
Column D has Date ( I am downloading month wise, so that will be the same month)
Column C has data that is much more than Col A, it has all the unwanted companies hyperlinks too and there is no way that the search on that website can be more customized than to the extent what I did.
Col D is much longer than Col B, because of more filings
Eg: Col A----Col B-----Col C(Hyperlinks)-----Col D
Abc------3/1/2008----- AAA ----- 3/1/2008
BCD------3/1/2008----- AAB ----- 3/1/2008
BCD------3/2/2008-----AAC ------ 3/1/2008
cDE------3/2/2008-----ABC ------ 3/1/2008
DeF------3/3/2008-----ABE ------- 3/1/2008
----------------------- BCD-------- 3/1/2008
---------------------- ABC-------- 3/2/2008
-----------------------BCD------- 3/2/2008
-------------------------CDE------- 3/2/2008
-------------------------AAA---------- 3/3/2008
-----------------------AAF----------- 3/3/2008
-----------------------DEF----------- 3/3/2008
I need the company in Col C to replace with its Hyperlink in Col A, provided they are on the same date (Col B=Col D), irrespective of Case. (here it is Alphabets, but in reality the company's name is unique)
The order of the companies in Col A and C are not same. Even if I sort 'A-Z' for columns, still because of unwanted companies data in Col C, C is a very long column than A.
Each month has 1200 to 1500 filings and I am chkg manually and replacing manually datewise. I have to do for 3 years, I am still in the same month for past 10 days...plz help!! There is still more I have open each filing and read thru and update the remarks column...looks like never ending

I posted this in another forum and one of the members served me with an answer, but I see no further communication on the questions I posted

Plz Help
http://stackoverflow.com/questions/1...-replacing-col

Code:
P.O.	Prelim Dwgs	Dwgs Deliv Quote
8/31/2005	N/A	#VALUE!
10/24/2005	N/A	#VALUE!
7/25/2006	9/5/2006	42
6/29/2006	8/24/2006	56
8/11/2006	9/22/2006	42
I'm trying to create a VLOOKUP for my columns H, I, and J. I'm trying to find the "Most Recent" date in column H that has a value listed in column J. If it doesn't have a value, I'd like it to list the 2nd, 3rd, or 4th most recent date instead until it lists a value. The point being I need the most recent (date wise) value from column J. Not every date has a value, so that is why I need to make sure J is a real number of some sort.

Hellow friends,
I have using Excel 2007.

I creating two sheets. (Sheet1 & DATA)

In DATA sheet : Total 20,000 row record. (sorted : column "C" by order A to Z & column "B" by order newest to oldest) column "C" is code & "B" is date.

In Sheet1 : I have calculate customer wise recorder. i.e. number of quantity sold & last date of transaction.

for example : In F6 position I enter formula =SUMIF(DATA!$AI:$AI,":"&$A6&"::"&$F$2&":",DATA!$N:$N)
In Sheet1 : A6 is code F2, G2, H2, I2, J2 is series.
In DATA sheet AI column created a key with two columns (=":"&C2&"::"&Q2&":") code & series & column "N" quantity.

Same formula in column : G6,H6,I6,J6
=SUMIF(DATA!$AI:$AI,":"&$A6&"::"&$G$2&":",DATA!$N:$N)
=SUMIF(DATA!$AI:$AI,":"&$A6&"::"&$H$2&":",DATA!$N:$N)
=SUMIF(DATA!$AI:$AI,":"&$A6&"::"&$I$2&":",DATA!$N:$N)
=SUMIF(DATA!$AI:$AI,":"&$A6&"::"&$J$2&":",DATA!$N:$N)

Now
in column K6 i using formula: =INDEX(DATA!$B:$B,MATCH(1,INDEX(($A6=DATA!$C:$C)*(Sheet1!$K$2=DATA!$Q:$Q),0),0))
in column L6 i using formula: =INDEX(DATA!$B:$B,MATCH(1,INDEX(($A6=DATA!$C:$C)*(Sheet1!$L$2=DATA!$Q:$Q),0),0))
in column M6 i using formula: =INDEX(DATA!$B:$B,MATCH(1,INDEX(($A6=DATA!$C:$C)*(Sheet1!$M$2=DATA!$Q:$Q),0),0))
in column N6 i using formula: =INDEX(DATA!$B:$B,MATCH(1,INDEX(($A6=DATA!$C:$C)*(Sheet1!$N$2=DATA!$Q:$Q),0),0))
in column O6 i using formula: =INDEX(DATA!$B:$B,MATCH(1,INDEX(($A6=DATA!$C:$C)*(Sheet1!$O$2=DATA!$Q:$Q),0),0))

In data sheet column B is date, column C is code, column Q is series.
In sheet1 : column A6 is code, column K2,L2,M2,N2,O2 is series.
In sheet1 total data row is approx 3000.

My problem is : when i selecting filter & selecting any type of data i.e. > or < or any data record. it will take calculating / processors time more than 15 to 20 minutes its work very slow.

Is there a way to faster calculation.

I have attach sample file with some record.

Hey guys, sorry for the double post. I posted this in the wrong forum, gonna go see if I can delete the other one now.

I am working on a project for a pseudo-accounting book. The goal is simple, to have the user input a single entry of expenses. Basically, that expense can be sourced from a bunch of different accounts - with each having its own tab.

So there are tabs for each account + one tab for the user to enter their data on. I want to have a drop down list (I've done this through data validation lists) that the user selects the name of the tab that the expense entry should be sorted onto.

Then the user describes the expense in each column with attributes like date, description, etc etc. When the user finishes entering this information, I want to have a SUBMIT button that then takes that row of data (which is a single entry expense-wise) and move it over to the proper tab (which should be able to be found out by looking at what is selected in the drop down menu).

Can anyone help me set up this submit button? I've spent a couple of days trying to figure out the visual basic code with no luck.

Thanks a lot!

Hi Everyone!

I'm learning VB by applying some automation to an Excel Workbook. So far, I've been able to:

1) Program a Combobox control so that every time a Userform is activated, the contents of the column of a sheet are loaded in this control.
2) Every time an item of the list contained in the combobox is selected, a listbox control is loaded with data of several other sheets corresponding to the selected item of the combobox. This data is ordered/sorted by date in a descending order. In other words, the most recent date is the first item on the list and the last item corresponds to the oldest set of information.
3) When an item of the listbox control is clicked, a number of textbox controls are loaded with the corresponding data.
4) Together with the last step, a chart that graphiclly displays some of the data on the listbox control is generated. It displays the last 24 months of information prior to the selected date (the date is contained in the first column of the listbox control).

All of this works fine, now, what I want to do is for the user to be able to DOUBLE CLICK the item so that the chart reflects the last 24 months prior to the date selected. In other words, I want to separate the Click Event (to ONLY load the textbox controls with data) and the DBCLICK event to change the chart accordingly. The problem is that the DBClICK event is not activaded with a double click no matter how fast I click the mouse button. Can anyone please give me a short example of how to use the Click and DbCLICK events together. I have the idea that it's got to do with the Mouse Up/Down events but I it's not very clear to me exactly how.

I Decided to upload the file I'm working on so it's easier to see what I'm trying to do here. To activate the Userform, simply select the sheet named "AUTOMATION".

For any information Thanks a lot!

Hi Everyone!

I'm learning VB by applying some automation to an Excel Workbook. So far, I've been able to:

1) Program a Combobox control so that every time a Userform is activated, the contents of the column of a sheet are loaded in this control.
2) Every time an item of the list contained in the combobox is selected, a listbox control is loaded with data of several other sheets corresponding to the selected item of the combobox. This data is ordered/sorted by date in a descending order. In other words, the most recent date is the first item on the list and the last item corresponds to the oldest set of information.
3) When an item of the listbox control is clicked, a number of textbox controls are loaded with the corresponding data.
4) Together with the last step, a chart that graphiclly displays some of the data on the listbox control is generated. It displays the last 24 months of information prior to the selected date (the date is contained in the first column of the listbox control).

All of this works fine, now, what I want to do is for the user to be able to DOUBLE CLICK the item so that the chart reflects the last 24 months prior to the date selected. In other words, I want to separate the Click Event (to ONLY load the textbox controls with data) and the DBCLICK event to change the chart accordingly. The problem is that the DBClICK event is not activaded with a double click no matter how fast I click the mouse button. Can anyone please give me a short example of how to use the Click and DbCLICK events together. I have the idea that it's got to do with the Mouse Up/Down events but I it's not very clear to me exactly how.

Previoulsly I Uploaded a file with some minor adjustments File-Size wise so it could be Uploaded but it had a little problem. Here is the file I'm working on.

For any information Thanks a lot!

Hi, I am new to the board and first of all would like to say thanks for anyone that takes any time to try and help.

Recently I've been handed some spreadsheets to streamline and minimise the amount of time spent updating one from data in the other, one spreadsheet gives staff placement (project wise) on a day by day basis and the other shows staff on the project they spend the majority of the time working on. To accommodate the problem of only needing one value to be copied across I've added a column to the daily spreadsheet which is to be the value copied to the week by week planner, so far so easy, the problem arises that the daily planner doesn't have a standard layout and names change position every time it updates.

I need a way of the macro I write being able to differentiate names (I'm guessing through the way it's referenced) for moving the value to the other worksheet or another way (sorting both sheets alphabetically perhaps?) in order to get the right information in the right place. I've been looking everywhere I've used in the past for VBA help and even some places I didn't but I haven't come across anything easily modified for purpose.

Does anyone know of anywhere to look or perhaps have a suggestion as to how to approach the problem?

For a little more detail, the value I wish to transfer would be in Column K, the names of the people involved would be Column B, the data would be put into the second "week by week" sheet where the name would again be in Column K but the information itself would be put into a column designated by date which is in Row 1.

I thank you again in advance if you do have any idea as I'm currently lost for how to approach this.

Hi Experts,

I have collective data based on different dates. The format of the date is "Month,date,year"
There are 4 columns such as John, Peter, Alan & Thomas for which i need the data to be filtered month wise.First column has dates.
For eg: For John I require the total count for the month say "June" .similarly for Peter, Alan and Thomas I need the total count.

I tried with Pivot table but not able to sort monthwise.

I have attached a sample workbook.And hope my requirement is clear.
Please provide me a solution.

HI

I want to search from a option page called "index" using a dialog box or a userform for a date contained in the "Calculations engine" in column A and then copy that entire row and the 80 rows below to the "main line sorting page" cell A100.

Any help would be most appreciated.

the forum at the moment is not allowing me to place an attachment other wise i would post an example

Hello,

Being a close-to-novice Excel user, I have come across a problem (or a challenge rather) involving huge sets of data which need to be transposed from rows into columns.

please see attached workbook. In the "Raw Data" worksheet, you will notice the first two columns being the date and the time that the data was logged. Data was logged every 10 seconds (roughly) for about 491 parameters (see Column C). After every 491 parameters the time changes and continues on for another 491 rows. What I require is:

1. Only the "tag indices" from 346 to 408 in Column C and the corresponding "Value" in Column D for every time interval.
2. The above parameters which are currently being tabulated row-wise to be transposed column-wise. See worksheet "Required" where I have shown a sample of how I want the data to be organized.
3. NOTE: As this data was logged by a data logger, the data is not consistent. Some time intervals (not in this workbook but in others) just stop at "200 tag indices" and jump to the next time interval. Hence the data sorting and transposing is probably best achieved by looking at the "time" rather than the "tag index". In such cases, the values would be ZERO for the tag indices that I am after.

If there is a quick and easy (being rather hopeful!!) way to do this by a macro or some such thing, it would be great as I have about a 100 such workbooks that I need to sort!!! Help highly appreciated with loads of thanks in advance.

If something is unclear, please let me know.

Sincerely,
Ananth

I have a table that was formated poorly for data analysis. I need the data to be in columns. Does any one know how to re-arrange the data with out doing it manually?

Current State.xlsWant.PNG

There is currently a column for defect mode and item (defect discription two) and then their is a row of dates and across from each column and under the appropriate date there is a qty. I need there to be a date, corresponding defect mode and item, and the qty for that date to be in a row.

Any ideas? Other wise I am sorting through it all manually.

Thanks

I need to generate a report that compares the support efficiency for the following months:

Oct - Dec - 2010
Jan - Nov - 2011 to be sorted month-wise

This attachment has 2 important fields i.e.
Date Created - The date when the ticket was opened
Last Modified Ė The date when the ticket was closed with solution

i.e create a column for age showing the no. of days for each ticket to be closed (Last Modified minus Date Created)

The report may be in the following format:

For Example: For the month of November 2011, the report shall be:

Month: Nov 2011
Total Ticket Closed: 2
Total Resolution Time: (2+1) = 3
Average Resolution time: (Total Resolution Time)/( Total Ticket) = 1.5

cheers
Sravanthi

Hi!

I am working with a large amount of columns, but the important columns are these; A = date, B = value. For every single date, I have several values.

Example:

A             B

2001-01-01     4.5
2001-01-01     5.2
2001-01-01     3.2
2001-01-02     1.2
2001-01-02     4.2
2001-01-02     5.0
In order to find the biggest value in B, for every date, I have made a macro that sorts the database, first on column A (oldest to newest date), then on column B (biggest to smallest value), but the more data I work with, the slower Excel gets, and sometimes my laptop is unable to handle this kind of sorting, causing Excel to crash.

Is there a more efficient way to do this? The database is already sorted on column A, but the values in column B are random, so I need some kind of Max()-function that is able to find not only the biggest value a certain date, but also the second biggest, and so on. This is dynamic, sometimes I need to find the second, third, fourth...biggest value if the first value is 'wrong', sometimes not.

Also, I read somewhere that selecting cells was a bad idea, performance wise. Is this true? Is it better to use the Offset-function in order to manipulate and get values, instead of first selecting a cell, and then go with the ActiveCell.Value?

Thank you!


No luck finding an answer? You could always try Google.