Free Microsoft Excel 2013 Quick Reference

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!

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!

- Need to split data according to date and language
- Find and replace the date format in Word file
- Pivot table to ignore data where n
- Copy data in the date range
- Creating a P&L worksheet, need to grab data within the quarter (by the date column)
- I need to find the first empty row in a sheet to paste data to
- Need Formula To Find Data
- Formula help needed to work with negative values
- Need to find the a row of data based on criteria
- VLOOKUP with a Concatenate formula help needed.to the guru's
- Help!!! Need to export data from Excel to Access
- Help...Need to modify data within a column in a .csv file
- I need a macro to find cut and paste data to new cell
- I need to find out the YEAR, MONTH, DAY between two dates
- In an array, I need to find the row # that meets 2 criteria
- Need a quicker way to find data
- Help...Need to modify data within a column in a .csv file
- I need to move data up one row and over one column, then delete blank row... X 1000 record
- Find Data in worksheets, Then look a number where Data was found
- Need to track controllable expenses with date range & string criteria
- How to find data on same date & add it us
- Help?! Need to create formula for date-range counter
- Matching the date and then finding a value
- Need to find MIN and the second lowest number in a column

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

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

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 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

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

******** ******************** ************************************************************************>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 ?

=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!

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...

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

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

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

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 $$$

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

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

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.

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

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 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.

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

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?

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