Free Microsoft Excel 2013 Quick Reference

HELP!!! Need to find data where the date matches certain criteria

I need to find the Min, Max, Avg, and Mean values in a column where the dates (in a separate column) are between two dates in another column.

So, for example:

Column A holds the dates
Column B holds the data
Column C Holds a list of Week Ending Dates (52 for each year) for a few years (2006 - 2008)
Columns D, E, F, G will hold the Avg, Mean, Min, Max values found in column B where the dates in column A fall between each of the two adjacent week ending dates in column C

As in the below example:

A B C D E F G
1 | Date | Act to SLA | W/E | Avg | Mean | High | Low |
2 |1/1/06| -7 |1/6/06 | ? | ? | ? | ? |
3 |1/1/06| 4 |1/13/06| ? | ? | ? | ? |
4 |1/1/07| 0 |1/20/06| ? | ? | ? | ? |

Any Help would be much appreciated!


Post your answer or comment

comments powered by Disqus
Hi,
I have a excel file in which I have data which is sorted date and language wise.
I have created 3 new sheets with names "English", "Hindi" and "English & Hindi".
I want to split data in Raw Data sheet to all the three sheets date wise.
I will add data date wise to raw data.
If the date in english is may 1 to may 15 then it should copy the all the rows related to may 1 to may 15 in English sheet from Raw Data.
same like Hindi and English & Hindi.

Attaching the file for reference...
Please help

Hi,

I want to find and replace the Date format in word, if there is any macro coding for this. I try to using the following codes

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = " 7/"
.Replacement.Text = " 07/"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
End Sub

But "7" is relates to month, so if there is any code for change the numeric character to specific date format as "mm".

For ex :

BUARDC 04294008295 010321 6000 7/17/2010 0000354652 000224

The above is the example data , in that month needs to added a leading zero.

Thanks in advance

I have a pivot table giving the sum and deviation of a column of data. How can I get the pivot to exclude data where the count of a batch is less than 2? Sample attached

How to set a macro to copy data in the date range in sheet2, and so that each time before copying erase the previous data in Sheet2

Hello All, I'm sure that there are many wonderful Profit and Loss spreadsheets out there but in this case I have created my own. It is rather basic, and it follows a format similar to a checkbook register. In column A, I have my dates. It is in the format 3/12/11. In column B (for example), I have an amount that was spent and in column C (for example), I have the reference for that expense, such as, 'Rent'. Over to the right (in column G for example), I have all of my expense references and the running total amounts for each item (such as Rent, Cable TV, Phone, etc). The problem is that I set this P&L for the 'whole year'. What I want to do is break these numbers down for each quarter of the year. I can create either duplicate expense references for each quarter (to display the quarterly data), or I can isolate a cell and assign it to show the P&L for each of the four quarters. Now here is where I need your expert advice ... How do I grab only the data in the rows corresponding to 'Dates' within the first quarter? I was thinking that I could use a 'IF' command but I don't know how to grab dates in the format I am using. Thanks so much for any help.

I have a form on sheet 1 (New User) that when you press the 'Add' button it
needs to find the first empty row in sheet 2 (Data) and paste the data from
the form there. I have gotten it to where the data gets copied, but only on a
row that I select when I'm recording a macro. Here is what my code looks like:

-------------------------------------------------
Sub NewEntry()
'
' NewEntry Macro
' Macro recorded 4/6/2005 by Grant Bivens.
'
' Keyboard Shortcut: Ctrl+a
'
Sheets("Data").Select
ActiveSheet.Unprotect
Sheets("New").Select
Range("A4:G4").Select
Selection.Copy
Sheets("Data").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A4,C4,E4,G4").Select
Range("G4").Activate
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
Range("B3").Select
Selection.AutoFill Destination:=Range("B3:B4"), Type:=xlFillDefault
Range("B3:B4").Select
ActiveSheet.Protect
End Sub
-------------------------------------------------
As you can see the data is just copied to row 4, over writing it each time.
Also, I need it to color every other cell for easy of viewing.

If anybody would help me with this project I would be willing to slip some
funds across the table if you know what I mean. This is really important for
me to get done. Its for a list of under 13 users at a forum I moderate. Its a
large list and our current system is virtually impossible to wade through.

You can reach me many different ways:

MSN- robthesgman@hotmail.com
AIM - rgbivens
Yahoo - kjkangb
Email - grant@grantbivens.com

Thanks,
-Grant

I am using Excel 2000.

I need a formula to find particular data within an array of data. I have a
worksheet displaying inventory activity. Cell A1 is a cutoff date. Cells
A4 thru A1000 are the dates of the activity. Cells B4 thru B1000 are
descriptions. Cells C4 thru C1000 are additions. Cells D4 thru D1000 are
subtractions. Each row will have a date, description and either an addition
or subtraction amount (but not both on the same row).

I need a formula to return the date, description and adddition amount of the
last row with an amount in the additions field where the date is on or
before the cutoff date.

Any help you can give me will be greatly appreciated.

Thanks,
Monte Sliger

Hi,

Wonder if anyone could help with this. I have a worksheet where in Column A I have numeric values that could be positive or negative. In column B I have a date in the format m/yyyy. So for example, I might have data like:

-23 7/2007
0 7/2007
12 6/2007
-3 7/2007

what I am trying to do is to find the average of all the negative numbers where the date string is the same in one cell, and in another do the same for 0 and positive values.

I've tried using various formulas to do this, but I keep getting DIV/0 or zero values, I assume because of the negative values in the array. Can anyone suggest a formula that will do what I need?

Regards,

Mark

I have the data below

******** ******************** ************************************************************************>Microsoft Excel - CUPS Stats.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)boutA2=
ABCDEFGHIJKL2BOOKNOP/MBOOK DATEDESTTOTAL COMMAIR OFFER COMMLAND OFFER COMMSTAFF NOBRANCHOFFER SUPP  3            4EA4001M04/08/2008DME249234090001QF  5EAAA76P04/08/2008LAX921.77260661.125101QF477D 6EA4002M04/08/2008DME249234090001QF  7EAAA77P04/08/2008LAX921.77260661.125101QF  8EA4003M05/08/2008DME249234090001QF 477D9EAAA78P06/08/2008LAX921.77260661.125101QF  10EA4004M07/08/2008DME249234090001QF  11EAAA79P07/08/2008LAX921.77260661.125101QF  12EA4005M07/08/2008DME249234090001QF  13EAAA80P08/08/2008LAX921.77260661.125101QF477D Superfacts Data  
[HtmlMaker 2.41] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

What I need to do, is, if the user for example selects code 477D (columns K to L) and select the date 05/08/08 I want to be able to bring in the 2 booking numbers booked after that date, in this example EA4003 and EAAA80

Any ideas ?

I have this formula:
=VLOOKUP(A6,DATA_Size!A32:K500,6,0)

This part is where I need the Concatenate done: DATA_Size!A32:K500

I want it to contatenate A & B together when trying to find it in the DATA_Size sheet.

For example A6 = C01BLK

On the DATA_Size sheet Column A has C01 and Column B has BLK so with the current formula =VLOOKUP(A6,DATA_Size!A32:K500,6,0)
it will never find it because on the DATA_Size sheet C01 and BLK are in seperate columns.

Obviously I could create a seperate column with the concatenate already done but I am really trying to avoid doing that.

Please help!

Hey guys

looked around quite a bit but didnt find an appropriate solution. I have an excel file which has 80,000 rows of data, but since excel cant handle that i have split the data and put it in 2 worksheets. Now i need to use this data to create a pivot table and would like to combine this entire data in acces....and here lies the problem...i am a complete newbie with access and have no idea how to use it...i used the inport funtion and was able to import one worksheet which goes upto 50,000 rows. now i need to add the 2nd worksheet which has the remaining 30,000 rows...i am guessing this is a simple solution but it has eluded me so far...so please help....

btw the columns in both the worksheets are identical...

thanks in advance...

I have been given a .csv file of several thousand rows, and I need to
change column A of each. The brief cutout below shows column A of the
first two rows. (I need to skip the first row, which is the headers.)
Kinda clumsy to have such a wide column, but the application this file
is to be fed after I change the contents requires all the data
separated by double-quotes to be in the one column.

Cell A2:
09/12/0509/25/05"1417385"121"307"111-11-1111"09/30/05"LASTNAME1
Cell A3:
09/12/0509/25/05"1417386"101"900000005"222-22-2222"09/30/05"LASTNAME2
..
..
..
etc

I need to do two things:

1. Add a constant to the 7-digit number which always appears after the
first double-quote; e.g. 1417385 becomes 1427385 and 1417386 becomes
1427386 (by adding constant of my choice in this case 10000). I need
to apply this constant to column A of all rows.

2. Change the date which appears after the fifth double-quote to a new,
constant date. 09/30/05 would become 10/14/05 for all rows. I don't
care what date is in that position, I just want to write over it with
the new one. Note that the number after the third quote can be
variable in length (e.g., 307 vs 900000005). I suppose part of the
solution could be to count (parse?) over five double-quotes to get to
this date.

All other data in the column remains the same.

Here's the after-image of the above example:

Cell A2:
09/12/0509/25/05"1427385"121"307"111-11-1111"10/14/05"LASTNAME1
Cell A3:
09/12/0509/25/05"1427386"101"900000005"222-22-2222"10/14/05"LASTNAME2

TIA for any help you can provide.

Ed

I have data that I have exported to excel that I need to reformat to be able
to create a pivot table to check for duplicate entries.
On importing the data which is in the form of journal entries the memo line
is stting above the journal numbers in column D. I need to find all of the
comments and cut and paste them to column C. The comments are not all the
same but do contain the same word "To" in the comment. The journals are not
all the same size and so the comment line does not appear at regular
intervals. Any help appreciated as I am very new to VBA

I need to find out the YEAR, MONTH, DAY between two dates. for example:
A1:19/05/05 and A2: 30/06/06. How can I have to difference in year, month,
day.
If 0 year, automatically the result must be run out in month, day and so on.

Please send me the formula.

Thanks

One sheet contains data that includes dates, vendor, price, and other items
in each row. I need to find the row offset that will match a date with a
vendor. I have tried using match, but since some dates and vendor names are
the same, I can't seem to isolate a matched pair everytime. Also, there are a
few times when there are duplicates of both and I need to get the next
matched set in the array.

DATE VENDOR CHARGE
date 1 vendor 1 $$$
date 1 vendor 2 $$$
date 2 vendor 1 $$$

I have a program that takes a student's ID, finds their Soc. Security Number (SSN) using the ID, and populates a cell with that SSN. Actually the program is a little more robust than that, but that is what it is doing in a nutshell. The problem I am having is it takes a very long time for this mission to be accomplished because I may be looking at anywhere from 15,000 - 30,0000 numbers. I have been programming in VBA for years, but I don't consider myself to be an expert by any means so I feel sure there is a better way of doing the searches than what I am doing.

Since I cannot attach the actual files I am using because they contain sensitive data, I have create two files that memic the process. I have simplied things greatly, but it will give you an idea of what I am doing. Target File.xls contains the list of ID's I am needing to find an SSN for. Data File.xls contains the list that has both the ID and the SSN. The code I am using is below.

I would certain appreciate any tweeks to the current code or any new coding ideas that would speed things up. Thanks in advance for your help!

Sub FindID()

Dim wsTargetFile As Worksheet, wsIDData As Worksheet
Dim rIDData As Range
Dim lTargetRow As Long, lStartRow As Long
Dim sID As String

Set wsTargetFile = Workbooks("Target File.xls").Sheets("Info")
Set wsIDData = Workbooks("Data file.xls").Sheets("Numbers")

For lTargetRow = 1 To wsTargetFile.Cells(wsTargetFile.Rows.Count, 1).End(xlUp).Row Step 1
    
    sID = wsTargetFile.Cells(lTargetRow, 1).Value
    
    Set rIDData = wsIDData.Cells.Find(What:=sID, After:=wsIDData.Cells(1, 1), LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
           
    If Not rIDData Is Nothing Then
        wsTargetFile.Cells(lTargetRow, 2).Value = wsIDData.Cells(rIDData.Row, 2).Value
    End If

Next lTargetRow

Set wsTargetFile = Nothing
Set wsIDData = Nothing
    
End Sub


I have been given a .csv file of several thousand rows, and I need to
change column A of each. The brief cutout below shows column A of the
first two rows. (I need to skip the first row, which is the headers.)
Kinda clumsy to have such a wide column, but the application this file
is to be fed after I change the contents requires all the data
separated by double-quotes to be in the one column.

Cell A2:
09/12/0509/25/05"1417385"121"307"111-11-1111"09/30/05"LASTNAME1
Cell A3:
09/12/0509/25/05"1417386"101"900000005"222-22-2222"09/30/05"LASTNAME2
..
..
..
etc

I need to do two things:

1. Add a constant to the 7-digit number which always appears after the
first double-quote; e.g. 1417385 becomes 1427385 and 1417386 becomes
1427386 (by adding constant of my choice in this case 10000). I need
to apply this constant to column A of all rows.

2. Change the date which appears after the fifth double-quote to a new,
constant date. 09/30/05 would become 10/14/05 for all rows. I don't
care what date is in that position, I just want to write over it with
the new one. Note that the number after the third quote can be
variable in length (e.g., 307 vs 900000005). I suppose part of the
solution could be to count (parse?) over five double-quotes to get to
this date.

All other data in the column remains the same.

Here's the after-image of the above example:

Cell A2:
09/12/0509/25/05"1427385"121"307"111-11-1111"10/14/05"LASTNAME1
Cell A3:
09/12/0509/25/05"1427386"101"900000005"222-22-2222"10/14/05"LASTNAME2

TIA for any help you can provide.

Ed

Hello, I'm new here - Thanks in advance for your help. I've included two photos below for reference...Screen Shot 2012-03-20 at 8.25.51 AM.pngScreen Shot 2012-03-20 at 8.25.39 AM.png

As you will see in the first photo I have about 1000 unique records with two fields, one above another (for a total of about 2000 cells in column A). I need to pull data from "field 1a" and move it beside "field 1" into column B. Then I need to delete the blank row that's created on line 2 where "field 1a" was. At that point I need to be able to do the same function all the way down the line. At the end of the day I need the date to look like the second photo = "field 1" data in column A then "field 1a" data in column B with no blank lines in between.

Any advise will be greatly appreciated.

Hey, this is my first post, so im new to this, if im doing anything wrong, feel free to point it out.

So, I have "number" and "Data" in my original worksheet; I need code to find data in the first 5 worksheets of my workbook (i.e. find: "CX10" in worksheets(1-5).range("3:3")) and, then making a vlookup in column A of number in the worksheet where data was found. If the number is found, then i want to paint it's whole row in the original worksheet.

I've tried everything and i keep getting errors.

Thanks in advance.

Santiago

I work for a company that has used a spreadsheet to track all cashflow (small company with 2-4 employees) in and out for the past year or so.

Here is the sheet where all transactions are entered:
Untitled-1.jpg

Column A is the date entered manually, D is a drop-down list of possible departments that any given transaction is categorized to, and E is the dollar amount.

I want a different sheet's cell to add all E values when Dx is a specific account (Say "Customer Payments") and Ax is within a given date range (say 1/1/2012-1/31/2012).

I was using OpenOffice to manage this sheet and had this equation working using SUMPRODUCT:

=SUMPRODUCT('Cash Flow Ledger'!A9:A65536>=DATE(12,1,1),'Cash Flow Ledger'!A9:A65536

I have a list of net returns per (stock) trade. Some of the trades had more than one trade per day. I want to locate the trades by date & add the total returns per date using macros so that it automatically finds the date with multiple trades & adds them up for a net total per date.

I can't seem to get the HTML Maker Excel Add-in to work for me. So I have just gave an example below of what I am working with.
(i.e.)
†07/08/03 07:00 †$62.36 $640.00
†07/10/03 06:58 † $62.08 $300.00
†07/11/03 07:42 † $62.65 $400.00
†07/14/03 06:42 † $63.50 $180.00
†07/17/03 06:56 † $61.89 $2,860.00
†07/23/03 08:48 † $64.36 $580.00
†07/24/03 06:38 † $64.96 $960.00
†07/25/03 06:40 † $65.38 ($920.00)
†07/25/03 08:28 † $65.33 $1,820.00
†07/28/03 06:56 † $66.22 $2,480.00
†07/29/03 06:36 † $68.64 ($1,860.00)
†07/29/03 07:28 † $68.37 ($580.00)
†07/29/03 10:30 †$68.45 ($20.00)
†07/30/03 06:34 † $68.90 ($380.00)
†07/30/03 09:36 † $69.95 ($2,500.00)

(Note: some days have only 1 & other days can have as much as 5-6 per date)
(*Note: there are three columns-1.)date/time....2.)sell price....3.)net profit/loss)
I want to identify the date in the first column & then get a net total per date.

If my explanation is not clear enough let me know. Thanks in advance for any input/help.

Hi all, I'm new to the forum, and have been an Excel user for about 2 years. I'm using Office 2003 on the PC (Windows XP SP2)

I've recently created a spreadsheet to track DVD rentals from online DVD mail-order companies. I've been able to develop formulas and macros to keep track of what DVDs I've reserved, which ones have been sent, returned by me, and then received back by the online company. Using this info I'm able to determine how many DVDs are in transit (coming or going) and what the turnaround time was (in days) between the time the DVD was sent and when the company reported it returned.

What I haven't been able to figure out is how to automatically tally the quantity of DVDs I was able to rent in a billing period (typically 30 days).

Here's how I have things set up:

I have "Worksheet 1" that keeps track of all the DVDs I've reserved/received, from which company, and dates sent/received, etc. I've created macros that allow me to sort the data by any column (Title, Company, Date Sent, Date Arrived, Date I Returned, Date Confirmed Returned by company).

I have "Worksheet 2" that I use to track my billing. It contains a "Start Date" and an "End Date" for the billing period, as well as the billing amount.

Using this data, combined with information from "Worksheet 1", I'm able to keep tabs on how much it's costing to rent a DVD (on average, based on total values), but I'd also like to be able to determine how many DVDs each company has sent me within the billing period in order to gauge mail efficiency, turnaround time, best bang for the buck, etc.

Is it possible to extract a tally of how many DVDs were sent by a company between two dates?

As a working example:

My billing period - August 8, 2005 (column A) to September 8, 2005 (column B)

Goal is to tally a 'counter' in a cell at the end of each row on my billing worksheet, which is based on a formula which looks at TWO columns on "Worksheet 1" (DVD Company and Date Sent) and compares this information to the company and date range in the two columns of the billing "Worksheet 2".

If a DVD was sent out by a company on August 10, 2005 based on data from Worksheet 1 (falling within the date range between cell A & cell B on a row in Worksheet 2) by company "X" (matching the billing company in cell C in Worksheet 2) then the counter (in a cell at the end of the same row on Worksheet 2) would be incremented by one. The formula would then continue to look for all other date matches within the date range AND company matches determined by the Start/End dates of the billing period as well as the DVD company matching the designated billing data; if a match was found then the number would continue to be incremented.

The end result I'm looking for is to have a dynamically created tally of how many DVDs I was able to rent monthly from each company, derived by the "Sent Dates" relation to the date range of the billing period.

If I can figure out a way to do this 'experimental' project then I also have a use for it on another project I have in mind for monthly customer job-tracking for my business purposes, which would prove to be a much more valuable and worthwhile task.

I've exhausted every avenue in trying to resolve this predicament, and maybe it's not possible... or perhaps it's so simple that I've overlooked the obvious?!

All help will be greatly appreciated.

Illusion

Hi, I am looking to do 2 things in a possible nested excel function using vlookup and if functions.

First I need to match a specific date from a summary tab to the same date in another tab that contains lots of raw data.
If the dates match exactly, then I need to find a specific value in that corresponding date row (that contains all the raw data) and drop it back into the 1st tab.
Any thoughts?

I have a column with 30 or so numbers in it. I need to to find the difference in the lowest and second lowest number. They can be equal. How would I write a command in cell A1 that would show me the difference if the numbers where in column B?


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