Identify Repeat Purchases Within Specific Time?

Jan 21, 2014

I am trying to identify repeat customers with a view to removing the purchase if the customer buys the same product within 5 days.

If the same customer buys a different product each day then I would like to count each purchase.

If the same customer buys the same product every day I would only like to count it once every 5 days.

I have tried using COUNTIFS for this. Formula in column D where A contains the Customer, B the Purchase Date and C the Product:

Formula:

View 5 Replies


ADVERTISEMENT

Identify Start Month And Repeat Same Value In Next 12 Cells

Jun 3, 2014

I am working on a formula by it is repeatedly failing.

Based on input 1 which is the month name, I am looking for a formula to identify the position across columns as the starting cell and then the value in input 2 needs to be repeated 12 times from there.

Example:
Input 1 is Apr-14
Input 2 is 51.8

I have columns with month names starting Apr-13 to Mar-15

Since the input 1 is Apr-14, the formula should identify the appropriate column named as Apr-14 and from there it should copy the value (input 2 ) 51.8 for the next 12 cells which is until Mar-15. I have attaches the examples for your quick reference.

View 3 Replies View Related

Different Country Time: Reflect The India Time After Pressing A Specific Key In A Specific Cell

Mar 3, 2007

I am working on a system where the system is having "australia time". Now, I want a worksheet where it should reflect the India Time after pressing a specific key in a specific cell.

View 3 Replies View Related

Macro To Repeat Row Specific Number Of Times?

Apr 19, 2014

I have never built or used a macro before and need very clear step by step instructions. I am just assuming that a macro is the answer to what I am looking for but if there is a simplier solution that would be fantastic.

I need to be able to repeat a row of data a specified amount of times.

Example:

3 Yellow James
2 Green Mark
5 Purple Sue
1 Orange David

Needs to look like:

3 Yellow James
3 Yellow James
3 Yellow James
2 Green Mark
2 Green Mark
5 Purple Sue
5 Purple Sue
5 Purple Sue
5 Purple Sue
5 Purple Sue
1 Orange David

View 7 Replies View Related

Identify A Specific Computer

Sep 9, 2009

I have few macros that I use to reconfigure a particular workbook into a sort of "developer mode". I keep them in a text file that I can paste them from when I'm working on it and then delete them before I send it out to other people.

I'd like to be able to script them so they only run if the workbook is open on a specific machine (namely mine). Is there any way to identify the specific computer a spreadsheet is being run on?

View 2 Replies View Related

Identify Range Names With Specific Value In Them

Aug 27, 2006

I am developing a macro to select cells within a worksheet based on certain criteria and to make a list of their range names. My approach is to copy the range names to a column of cells using something like the following text:

Sheets("mySheet").Range("myRange").Name.Copy

but I get an error message saying "Object doesn't support this property or method". Why can't I copy a range name, and is there a way to work around this?

View 9 Replies View Related

Sorting Purchases By Customer / By Date

Jun 9, 2014

I have a spreadsheet that contains over 3000 customers. On this there is much data...but what I am interested in is purchase date. The problem I am having is that customers have purchased multiple times and have multiple purchase dates. I was wondering if there was a way to get rid of all but the most recent purchase dates for each customer...thus having only one line per customer as opposed to 5 lines per customer?

In other words: Jon purchased on 1/2/14; 2/4/14 & 5/8/14
Bill purchased on 2/17/14; 3/19/14 & 4/21/14
Tom purchased on 4/2/14; 5/1/14 & 6/5/14

I need to get rid of the oldest dates and keep one line item by most recent purchase date for each customer...so instead of having 300o line spreadsheet I will only have 1000 lines.

View 8 Replies View Related

Identify Which Rows Have Blank Values In Specific Columns?

Feb 19, 2013

I have a spreadsheet with columns and columns of data. What I'd like to do is identify which rows have blank/no data in all specified columns (H, K, O, S, U, W, and Y).

To clarify, I need to identify each row where all of these columns are blank (as opposed to each row where at least 1 column is blank).

View 3 Replies View Related

Identify Values Specific To Defined Groups Of Columns?

Sep 2, 2013

I have these kind of matrices (below) and I'd like to identify unique values specific to different groups of columns:

[Code] .........

For instance, in the example above, if we decide that A, B, C are "Group 1" and D, E, F are "Group 2", and that the values in rows 1, 2, 3 are independent (i.e., "x" in "1" is not comparable to "x" in "2", etc):
- In condition (row) 1: "x" is a specific value only found in Group 1
- In condition 1: "y" is a specific value only found in Group 2
- In condition 2: "x" is a value found in majority in Group 1
- In condition 3: no specific value can be associated to Group 1 or 2.

What I would like to get is a measure of whether:
(1) there are values over-represented in one of the groups, or 100% specific to one group.
(2) what are these values
(3) if multiple values are a bit tricky, then: what is the value which is the most over-represented in one group compared to the other (the maximum being 100% in one group and 0% in the other)

View 1 Replies View Related

Identify Specific Keywords (plural) In A Column Of Text

Nov 3, 2009

So i'm try to automate a process that I currently do using filters.

Here is the problem:

I have a list of text in a column (usually 700 or so entries).

I need to remove any entries that contain 1 of 42 keywords.

So I need a fast way to search each cell for each one of the 42 key words and flag the cell for deletion if contains anyone of the 42.

example
A
1 This field has keyword1
2 This field has keyword2
3 This field doesn't have any of the 42 keywords

somewhere else I would have a list of keywords in a column.

View 9 Replies View Related

Showing List Of Data From Total Purchases

Jan 29, 2014

I have a basic knowledge of spreadsheets, involving typical business needs (Average, Sum, And, Or, Logical Operators) but after roughly 35 minutes searching for a simple solution I'm stumped.

If I was to calculate the most popular (lets say fruit), I know it would be the MAX function, but what would the formula to show the Name of the most popular fruit and not just the amount of sales?

Name
Total Sales
Most Popular
How many Apple 100

[Code]....

View 5 Replies View Related

Macro - Move Purchases Every Hour To Keep Track Of Employees

Jun 17, 2014

I have a spreadsheet that has two Columns.

Column (B) has names of flowers
Column (C) has price

I want to at the top of each hour have those two columns be moved to Col(D) and Col(E) . However if D and E are full I want them in F and G and so on. Essentially i want them to go to the closest empty column to the right.

How to do this automatically in a macro ?

This would allow me to keep track of my employees.

Also is there a way that I could automatically generate a graph of price vs. name for each hour when they are moved ?

View 10 Replies View Related

Identify Occurrences Based On Date And Time

Feb 21, 2014

How to identify the criteria (break or lunch) that he took first based on the time stamps. Here's the sample scenario: I would like to know which one is the 1st break or 2nd break they took based on the Date & Time criteria. See attached file for the sample data.

Sample results:

Agent 001 : 1st Break = 2/21/2014 10:13:00 AM
Agent 001 : 2nd Break = 2/21/2014 7:13:00 PM

View 2 Replies View Related

Summarizing Times For Specific Items Within Specific Time-frame

Mar 26, 2014

I got Excel for Mac 2011 installed. I have a table called 'Raw" that holds data from the beginning until the end of 2013 (in total ~7000 rows). I have columns for Category, Start and End time, and time period in Hours, Minutes, and Seconds:

Code:
A
B
C
D
E
F

Category
Start
End
Hours
Minutes
Seconds

Transportation
2013/01/01 01:00:00
2013/01/01 01:15:00
0:15:00
15:00
900

[Code] ...........

I'm trying to get an overview of how much time I've spent in January on Transportation. I would need to look at the Category entries for Transportation, then only use the ones which have the Start set to 2013/01, and then take the Hours/Minutes/Seconds, and summarise it in a new table called 'Data':

Code:
A
B
C
D
E
F

Month
Category
Occurrence
Hours
Minutes
Seconds

2013/01
Transportation
48

[Code] .......

I have figured out a way to count the occurrences for Transportation for January:

=COUNTIFS(Raw!B:B,">="&"2013/01/01 00:00:00"+0,Raw!B:B,"

View 1 Replies View Related

Macro - Repeat Macro A Specific Number Of Times

Aug 22, 2008

I have created a macro that copies the outcome of a specific calculation (that is driven by random numbers) and pastes it (as values) into a seperate table. It then recalculates the sheet, copies the new outcome and pastes it into the same table at a row beneath the previous one.

However, I would like to be able to determine how many times this loop is repeated (i.e. how many rows of outcomes will be generated) without having to change the macro each time. Is this possible? Maybe by just linking it to a cell where the number of outcomes/repeats is specified.

View 9 Replies View Related

IF Function To Compare Specific Time To Time In Cell

Nov 19, 2008

I am looking for a formula to change a condition based on the time of day. Essentially, before 1pm I would like the formula to be:

=((I12/100)*25)/D12

After 1pm I would like the formula to be

=((I12/100)*25)/B12

I tried various ways with "=if(now()> ..." formulas to no avail because of the way Excel handles Now().

View 6 Replies View Related

Use Dynamic Formula Driven Text From Cell To Identify And Extract From Specific Cell?

Dec 21, 2013

I paste new data into a sheet of a monthly report I prepare. For this sheet, the # of data rows change (and is unpredictable) every month. I need the value inside a specific cell that dynamcially moves up and down based on the # of rows for that month (because it's below the rows of data).

So I made a formula to identify the exact cell # every month.

Example:

This month the exact cell is F255 in the "Refi" sheet.

So my formula in the "Summary" sheet cell A1 first finds the cell row # only (255) and since it's always column F, in B1 I have

VB:
="F" & (A1)

This outputs "F255" in B1, successfully identifying the target cell.

Now how do I write a formula in C1 to grab the value from whatever cell is named in B1. (For this month, the value in cell F255 from the "Refi" sheet)

View 7 Replies View Related

Highlight Repeat Cells In One Column If Cells In The Adjacent Column Contain Specific Text?

Apr 14, 2014

I am trying to find a solution for highlighting cells in a column that are repeats, ie. >3. I also need these cells to only be highlighted if the adjacent cell in the next column contains specific text. I have tried using conditional formatting with a countifs formula to no avail.

View 2 Replies View Related

Macro At Specific Time And Day?

Sep 18, 2012

I want to run a macro at a specific time and day. For exemple the macro needs to be run every Friday at noon.

Also, I don't want to worry about opening the file all the time.

View 3 Replies View Related

Average For Specific Dates Of Gap Time.

Mar 16, 2009

I would like to create an average function that will take an average of the Column labeled "Gap Time (Hours/Min/Sec)". I only want it to take the average for this for each new start date. These values will change daily so I was hoping that someone may help me write a function that will work when data changes instead of manually taking the average every time data is entered.

View 5 Replies View Related

Opening A Workbook At A Specific Time

Oct 2, 2007

to open a workbook at a specific time. I want to beable to flip my computer on at 8:50am, go grab some coffee and then at 9:00:00am have my workbook open and performing tasks. Besides turning on my computer, I dont want to have to open or activate anything prior to having my workbook open. However if there is code to turn on my computer from a sedative state that would be great too.

View 9 Replies View Related

Running A Macro At A Specific Time

Jun 5, 2009

I want to know if I can schedule a macro to run at specific times?

If so would 6 requests at the same time slow excel down?

View 6 Replies View Related

Time Difference With Specific Calendar

Feb 5, 2009

I am looking to calculate the time difference between 2 date time fields using a specific calendar.

I work in an engineering company and am having problems calculating machine utilization efficiencies.

Example:
Job 1: Start date 06/01/2009 10:00:00
Job 1: End date 06/02/2009 12:00:00

Calendar:
Dayshift: Monday - Thursday 07:15:00 - 16:15:00, Friday 07:15:00 - 12:15:00
Nightshift: Monday - Wednesday 21:00:00 - 07:45:00, Thursday 21:00:00 - 05:45:00

Using the specified calendar I am trying to calculate the time difference between the start and end date of job 1.

View 11 Replies View Related

Convert Time To Specific Number?

Mar 5, 2012

Is there a way i can convert the time to a specific number? for Eg. If incell A1 i have the time in following format 01:15:08 (MM:SS:MS)

i want the result in A2 to show 7508
01 minute = 60 seconds
Plus 15sec = 60 + 15 = 75
and 08 miliseconds would give 7508 ??

View 1 Replies View Related

How To Get Average Time For Four Specific Entries

Apr 7, 2013

I have a database of over 10,000 entries. I am trying to get the average time for four specific entries (department, test 1, test 2, test 3). I did an averageifs for the tests individually and calculated the average time individually.

The three tests comprise of the department.

Average of test 1 = 40.8
Average of test 2 = 39.9
Average of test 3 = 94.8

Average of dept using the averageifs is 41.3
Average of dept by taking the average of the values above is 58.8

Why is that discrepancy there?

View 6 Replies View Related

Open Workbook At Specific Time

Jun 18, 2008

Need to have a particular workbook open at a specific time...

I wrote this and placed it in a Personal.xls module, did not run automatically. I then placed the 2nd SUB() in the "This Workbook" and nothing. Where or how is the proper way to run this...

----------------------------
Sub Open_IndexAnalysis()

Workbooks.Open Filename:="e:Index Analysis.xls"

End Sub
------------------------------
Sub Run_OpenIndexAnalysis()

Application.OnTime TimeValue("09:40:00"), "Open_IndexAnalysis"

End Sub
-----------------------

View 9 Replies View Related

Time In Specific Cell When A1 Contains Data

Mar 24, 2009

A vehicle has been assigned to collection, time of entry appears in Load cell coresponding with vehicle eg.e vehicle 1 'A1' = load 1 'B1'. I can get the exact time to appear in the cell currently but when I try to assign a second truck, the load 2 'B2' cell AND the load 1 'B1'. Im sure that there is an easy way to do this but i just cant figure it out. Can someone please help me out. Can i disable the cell that is updating?

View 9 Replies View Related

Running Module At Specific Time

Jun 24, 2006

I have a module which runs behind a command button which creates a table and outputs it to an excel workbook. Is it possible to cause this module to execute at a specified time, say 9.00 pm or when the database is closed. It is not necessarily the case that the database is closed every day.

View 9 Replies View Related

Closing Workbook At Specific Time

Mar 9, 2007

I can open a file at specific time using TaskSchedule. How do I close it at specific time?

I am trying to write a simple one-line code for that but do not know how to combine two statements:

Application .OnTime TimeValue("17:01:00")

and

ActiveWorkbook.Close

Also, this workbook is updated every 1 minute using the same OnTime method and everything works perfectly. However, when I try to close file there is a message saying that "OnTime method object application failed" and I have to click "Debug" or "End".

Only when I click "End", the file is closed.

View 6 Replies View Related

Extract Data From Webpage At Specific Time?

Jul 15, 2014

I'm trying to extract some data from an online page, but I require a specific cell at an exact time, each day.

For example, I would like to acquire the main data from the following page: [URL]

into excel, but additionally, I require the ESU14 (Sep '14) Open cell at exactly 0700 BST. I am currently using the 'Data' - 'From Web' feature to scrape the necessary fields into Excel however I haven't found a way to acquire one of those cells, at exactly a certain time, every day, even when Excel et al aren't open.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved