How do I count Excel cells in a column containing a specific date, when such cells contain the date and time?
Since COUNTIF cannot accept array arguments, you can create an array formula that does by using a SUM():=SUM(--(INT(A1:A8)=(TODAY()-2)))and entering it using CTRL+SHIFT+ENTER.INT(A1:A8) creates an internal array of just the integer portions in the list A1:A8.ex: INT(A1:A8) becomes:By comparing it to the value of TODAY()-2 which is also 42907, we get:SUM cannot add TRUE and FALSE directly so we use the trick of putting a double negative (any math operation will convert a TRUE or FALSE to 1 or 0 so we quickly change the sign to negative and back to positive) and we get:Which SUM can easily add to give us:Just remember to enter an array formula using CTRL+SHIFT+ENTER instead of just ENTER. When you use CTRL+SHIFT+ENTER, Excel acknowledges that you entered an array formula by putting the curly brackets at the beginning and end of the formula for you. You don’t add them yourself.To see what different segments of a formula evaluate to like I did in my examples, use your mouse to highlight that segment and the press the F9 key, To return to the original formula, either press the ESC key or use the combo of CTRL+Z.
How can I pull data from one Excel sheet to another automatically? I want to run a comparison on products I've sold one quarter versus another. Is there a way I can pull in the sales quantity from the other sheet and add it to the row to compare?
There are two ways to do this. Use VLookup or Use Combination of Index-Match.Lets say you have your data in two sheets, X and YPlease note that the same products might not be selling in all the quartersNow, when I want to consolidate in a separate sheet, I would first of all put all product categories in that sheet.Post that, I can use Vlookup to find the value. The only problem is that if the value does not exist, I would get a #N/A error. To avoid that you can use iferror to make the value 0, if it is not found. Please note that the array where you are looking for the key, has to be protected with $sSimilarly I can use a combination of Index and Match, protect the key column with $ and ensure that I can avoid #N/A errors with iferror
Best way to import and process Excel data in VB.NET 2005?
Imports System.Data Imports System.Data.OleDb Dim conn As OleDb.OleDbConnection = New OleDb.OleDbConnection Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand Try With conn .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\nameofexcelfile.xls;" & _ "Extended Properties=""Excel 8.0;HDR=Yes""" .Open() End With Dim strSQl As String = "SELECT * FROM [Sheet1$]" With cmd .CommandText = strSQl .CommandType = CommandType.Text .Connection = conn End With Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter Dim ds As New DataSet cmd.ExecuteNonQuery() da.SelectCommand = cmd da.Fill(ds, "Excel") Dim sw As StreamWriter = New StreamWriter("c:\aaa.txt") Dim row As DataRow For Each row In ds.Tables(0).Rows sw.Write(row("nameofheaderinexcelsheet") ) Next sw.Close() Catch ex As Exception MsgBox(ex.Message) Finally conn.Close() End Try Once it's in the dataset you should be able to do whatever you want with the data.
How can you pull data from one sheet to another in Excel based on a particular value?
1- Vlookup function2- Index & Match function “=INDEX (column to return a value from, (MATCH (lookup value, column to lookup against, 0))”I prefer IndexMatch function for many reasons:1- IndexMatch works in two directions (right to left and left to right) while vlookup cannot look to its left.2- If you want to insert or delete columns from the table or sheet you are getting data from the Vlookup function will get broken or gives wrong results, and this doesn’t happen in IndexMatch.3- If you are working with big data like thousands of rows that means thousands of Vlookup formulas, Excel will work much faster if you use IndexMatch rather than Vlookup.
In Python 3, how do you extract, for example, only column 2 from an excel sheet?
Assuming you are using pandas and reading from a .xlsx i.e. not a csv, you can use the ‘parse_cols’ parameter when using read_excel to determine the columns being read into a dataframe. From the pandas documentation:parse_cols : int or list, default NoneIf None then parse all columns,If int then indicates last column to be parsedIf list of ints then indicates list of column numbers to be parsedIf string then indicates comma separated list of Excel column letters and column ranges (e.g. “A:E” or “A,C,E:F”). Ranges are inclusive of both sides.So, if you want to read in only the second column from a .xslx:import pandas as pdpath = 'data.xlsx'# read in dataframe with parse_cols# use a list to read in specific columns, even if only one column# use 1 instead of 2 since Python is zero indexeddataframe = pd.read_excel(path, parse_cols = [1])If your data is already read into a dataframe, and you want to extract the second column from that, you can just used .iloc with brackets:second_column = dataframe.iloc[: , 1]and the second column will be stored in second_column as a pandas series.
In excel, how come I can't sort times in order? ?
Sometimes Excel can seem to be extremely dumb and has to be "hammered" into submission. Here is one way to do just that. Assuming the dates and times are in column A, insert two columns as helper columns. In column B, format the column as a number and enter =A1 and copy down the length of your data. This will give the serial number of the dates and times in column A. Format column C as h:mm AM/PM and enter this formula: =MID(B1,SEARCH(".",A1,1),6)*1 This will extract the decimal part of the date serial number and convert it to time in hours and minutes AM and PM. Now you can select all your data including the helper columns and sort on column C. If you want to break the dependence of column C values on the values in columns A and B, copy column C then Paste Values back into the same selection as the copy. This will eliminate the formulae and return only the values. This will allow you to sort your data completely independently of the date value that was originally part of the time values.
How can I record a current cell value (that is constantly changing) on another Excel sheet at a specific point in time?
You can do this by activating Iterative calculations in excel. Go to File>Excel options>Formula and enable iterative calculationnow you have to add reset and counter cells in one cell. Counter will update when ever your target cell change and value is recorded. If Reset Cell (named as reset) will have value of 0 or 1. Counter(named as count) will have formula of =IF(reset=1,,count+1)As you can see there is circular reference in formula for count as count=IF(reset=1,,count+1). But you will not get error as you have activated Iterative calculationAdd serial number in Row A and your target values can be recorded in row B. I have given below screen shot of workbook recording time stampin Cell B52 add formula as =IF(reset=1,"",IF(count=$A52,time,B52))This is again circular referencing. Drag this formula and your sheet will start recording with every refresh. your workbook should be open for this to function. I had developed this sheet to capture and analyse stock quotes that changes frequently. I had taken data from web query with 15 sec refresh rate.