Data Sorting Query - Get Excel To Calculate?
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
ADVERTISEMENT
Oct 19, 2012
I created a slick little excel sheet with the data coming in automatically via Access query. It has been working fine for months. Now all of the sudden there are a bunch of cells with missing data. The weird part is it's not as if whole columns are missing data, more like 90% missing. When I go to Access and run the query all cells are populated as the should be. There have been no changes to the query at all during this time.
btw...I am running Office 2010
View 2 Replies
View Related
Nov 26, 2012
I have an MS Access query that contains a parameter. The parameter is a date field, and I have configured that in the Access query. If I run the query within the MS Access user interface, it prompts me for the paramater value as expected, and runs just fine. However, I want to connect to this query from within Excel as a data source.
I have created a connection to the Access file using ODBC from within Excel. In the MS Query window, I am merely selecting all of the fields resident in the MS Access query, and returning all values. In other words, there is no selection criteria in the MS Query. I have done this many times with Access queries that DO NOT contain a parameter, and everything works fine. However, in this instance, I need to pass a parameter through to MS Access in order for the query to run. At the moment, I get the "Too Few Paramaters...1 expected" error message. This makes sense, because I haven't figured out how to pass the paramater to MS Access.
Is there a way to structure this that does not involve VB code? If so, I'd love to know how. I have tried creating parameters in MS-Query with the same name, but although I get the prompt it doesn't connect with the Access query as the source for the parameter value.
If the solution requires using code, I'm good with VB Code in Excel...is there VB for Excel code that could make this happen?
Failing that, I guess there must be (I've seen a few in my search thus far) Access VB Code that can make this work. I'm very rusty using VB with Access, so this is my least favored solution. However, if this is the only option, keep in mind that I need to pass the paramater ultimately from a user who will initiate the process using Excel.
View 3 Replies
View Related
Jun 23, 2013
I am trying to get data in excel sheet using web query from the following web link:
But I the data returned is a 'hypen' (i.e. a dash symbol). I am getting the row/column headers but not the figures or the numbers under them.
View 2 Replies
View Related
Jun 23, 2013
I am trying to use queries I have been running in MS SQL Server Management Studio, to return data in Excel where it would display as pivot. Some queries I was able to use through Excel but few others are not returning anything.
I am doubting it has something to do with the query itself - but they are displaying results properly in MS Query, they are just not returning any data to Excel.
View 3 Replies
View Related
Sep 20, 2013
I am trying to set up a spreadsheet in Excel 2003 that pulls data from a lot of different websites and formats it all for me. I have managed to do this with a few websites but there are two which I'm having problems with.
This is the table I am trying to get, but when I do a manual web query and select the table and click OK, it says that no data has been found.
[URL] ...
View 1 Replies
View Related
Aug 12, 2013
I am looking for information on how I can use the Transfer Data from System i Add-In for Excel to only get data that equals the data in one of my columns (both sets of data are strings). Unfortunately, I cannot just get the entire contents of the System i table since it contains more than the maximum allowed in Excel.
View 1 Replies
View Related
Sep 25, 2013
Im using a query to connect to a SQL server and return data into Excel. I can query and return the data in Microsoft Query editor but when I attempt to return the data to Excel in a table, it just says the name of the connection in cell A1. If I attempt to return it into a pivot table a get and "Problems obtaining data" notification.
View 1 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 6, 2014
I am wanting to create a validation rule so when two cells are added the together the answer must be less than or equal to 14:00 - otherwise a message box will appear
So for example if H7+I7 gives an answer of 13:00 in J7, that's OK, but if the answer is 15:00, the error box will appear. I know that data validation doesnt work on a cell that already has a formula so I'm hoping to use K7 for the Validation and then hide the column
View 4 Replies
View Related
Jul 1, 2014
I have an Access table which has following fields:
ScanDate Number DataType
Type Number
Type1 Number
Type2 Number
BatchNo Number
Cases Number
Pages Number
Now I have a useform in Excel so that when the user selects the Date from the combobox then it should check for total batch numbers (Count(BatchNo)), total cases (Sum(Cases)),Total Pages(Sum(Pages)) where ScanDate= Date from the combobox group by Queue Number. The Queue Number consist of Type+Type1+Type2.
See attached the Excelsheet where the data should be populated to. WBCount.xls
View 1 Replies
View Related
Sep 6, 2012
I've about 10 pivot tables in Excel that are populated via an External Query link to Access. In each case I have pre-written views with all the columns I need so that the query can just pick them up. Nine of the ten queries work perfectly, but one doesn't - reporting "Too Few Parameters - Expected 1" when I try and click on the "Return Data to Excel" in the import wizard.
I can see the column headers in Excel to select; so I'm assuming that it can parse the SQL but no results are being returned. Just to confirm though - results are returned (>8000 of them) in Access.
SQL code below:
Code:
SELECT z.pathdurationhours AS PathHours, Count(z.pathdurationhours) AS Paths,
Count(z.pathdurationhours)/(select count(*) from (select [activity date],
[conversion ID], count(*) from tbl_dfa_p2cdata group by [activity date], [conversion ID]) ) AS PCT
FROM (SELECT a.[activity date], a.[conversion ID], val( max(a.[interaction time])-min(a.[interaction time]) )
*24 AS PathDurationHours FROM tbl_dfa_P2CData AS a WHERE a.[interaction time]
"" GROUP BY a.[activity date], a.[conversion ID]) AS z
GROUP BY z.pathdurationhours;
View 2 Replies
View Related
Oct 13, 2013
I'm trying to build a workbook (2007) with multiple worksheets where each worksheet corresponds to a different query/dataset pulling data from the one database.
I would have expected that you could define the one data connection object and then specify the SQL that each worksheet table will invoke. The apparent structure of having the query coupled to the connection object is confounding me. I'm therefore trying to set up a separate connection for each of the worksheets and Excel is getting tied up in knots.
View 2 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
Jan 22, 2014
I use Office 2010. I have an Access database that goes out and runs various queries from our systems of record. It then takes the data and dumps it into an Excel file. I have taken the info and created a file the grabs the raw data, converts it into Pivots and then Charts. All info is automatically updated however the size will change depending on what department is quering the data. It is really all done with a click of a button. I have tried to merely uncheck the "(blank)" but it comes back everytime the report is ran.
How can I keep it from returning??
View 3 Replies
View Related
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
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
Sep 6, 2008
I have a csv file on another drive on the network that i need to query. I believe that ms query would be the best way. I know that a DSN needs to be setup but this macro will be used by various users who wont know how to do that. thus I would like to create one via VBA every time the task needs to be run.
I haven't a clue how to do this and i need it to be explained to me in general terms with words of one syllable!
View 9 Replies
View Related
Mar 25, 2014
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
View 1 Replies
View Related
Nov 12, 2008
I am using a formula to calculate the last day of the month, using any date of the month in a worksheet in cell A13, this cell is also linked to another worksheet to pick up a date, using the ISBLANK function to prevent a dummy date entry appearing if the field in the linked ASHBY RISE worksheet is blank
=IF(ISBLANK('ASHBY RISE'!$C$5),"",'ASHBY RISE'!$C$5)
The last day of the month function is shown below
=DATE(YEAR(A13),MONTH(A13)+1,0)
This works fine if there is a date in A13, but returns a #VALUE! error if cell A13 is blank. I have tried using the ISBLANK function, but I am still getting the #VALUE! error. Of course I may have the sysntax incorrect.
View 4 Replies
View Related
Nov 4, 2005
I've found myself having to use a spreadsheet to calculate some acoustics problems, (problematic frequencies, called modes, in orders ranging from 0,1,2,3....).
Ideally what I want to be able to do is simply to get Excell to calculate all the possible combinations of these modes.
A combination could be:
000
011
101
110
111
012
143
etc.
Obviously this is a nightmare to input by hand, is there any kind of function I can use to help with this process?
Also these frequencies only need to be calculated up to a certain point, so is it possible I could also instruct the cell to only display a value if it is less than a value in another cell?
View 10 Replies
View Related
Aug 19, 2013
Basically all I want to do is paste some odds data in to excel and then get it to just calculate the odds for me example below
9/4 Exotic Isle, 5/2 Greenery, 5/2 Port Alfred, 6/1 Reqaaba, 12/1 Somoud
I know I can use text to columns using the deliminator to split them in cells however I just want to take the odds
So Exotic Isle would be 2.25 (9/4) Greenery would be 2.5 (5/2) and so on
I've tried using the left function basically all sorts.
View 3 Replies
View Related
Apr 21, 2014
I just copied a table from the web which contains numbers in the last column.
I pasted the data into Excel, but when I try to run any type of calculation i receive a #VALUE! error.
The annoying 'workaround' is to re-type the same numerical text into it's own cell, which allows for calculation.
I've tried changing the cell formatting to 'general', and 'text', but I still receive the #VALUE! error, when trying to calculate.
View 1 Replies
View Related
Jun 30, 2014
I was trying to use IF and AND conditions to calculate values for a Col. but when I attempt to sum.
It appears that the value is always "0". Below is my test data.
Col. H Col. J
Qty Result
Row 713 0 <-Excel Formula: =IF(AND(H7>20,H7<=50),"1","0")
Row 825 1 <-Excel Formula: =IF(AND(H8>20,H8<=50),"1","0")
Row 960 0 <-Excel Formula: =IF(AND(H9>20,H9<=50),"1","0")
Row 10Total 0 <-Excel Formula: =SUBTOTAL(9,I7:I9) / =SUM(I7:I9)
View 3 Replies
View Related
Feb 16, 2012
I've inherited an SQL server database which imports data from a multitude of spreadsheets each night. Unfortunately the previous data is not deleted from the database or spreadsheets, so there are duplicated records throughout the database. The method they have been using to get data out is to "select distinct", which gets around the problem (not my preferred solution).
I now need to produce an invoice from the database, so I select distinct records for the previous month (based on date) from, say, the TRUCKING table into a TEMP table, so I can do my calculations from there.
But there are a couple of columns which prevent me from getting only unique records. They are the unique ID column and the TIMESTAMP column they have added.
If I try to select all the individual columns, except for the ID and TIMESTAMP, the string length goes over 256 chars and the query truncates.
So I tried "SELECT DISTINCT * FROM TRUCKING WHERE etc etc" but I get the ID and TIMESTAMP fields, which defeats the purpose, and I get duplicate records.
Is there a way to say, for instance, SELECT ALL EXCEPT the fields I don't want?
View 9 Replies
View Related
Mar 12, 2012
I have the below code that will do a data dump of an access table into excel based on an SQL query, is there a way to do a select statement that can match against the data in column C on the spreadsheet.
like:
"Select * From Bsmart where Serial_Number = ##[EXCEL COLUMN C1:Lastrow]##
Code:
Dim bkNew As Workbook
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stCon As String
Dim SQl1, SQL2, SQL3, SQL4, SQL5, varSQl As String
stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
[Code] .....
View 1 Replies
View Related
Jun 6, 2012
I am using below code to delete the sheet from workbook.
For iTemp1 = Workbooks.Item(funEach.Name).Sheets.Count To 1 Step -1
Sheets(iTemp1).Select
If UCase(Sheets(iTemp1).Name) "TEMPLATE" And UCase(Sheets(iTemp1).Name) "WORKING" Then
Sheets(iTemp1).Delete
End If
Next
How can i avoid the below popup..
-Data may exist in the sheet(s) selected for delettion. To permanently delete the data press delete.
View 2 Replies
View Related
Jan 29, 2008
I have an access database that has many queries and in order to speed it up I have a Macro in access that runs it in no time, but I need to be able to run the query in access via a trigger in Excel and have come up with the below, but it's not working.
View 9 Replies
View Related
Jul 25, 2008
I'm trying to create a report for a coworker to run. I want to filter the parameter in a cell (ssn), so that she can run the report. I have added a button for her to execute..once she puts in teh ssn. The spreadsheet is linked to MS Query in the background. How do I tell the Query to link to the cell in excel for filtering?
View 9 Replies
View Related
