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


ADVERTISEMENT

How To Look For A Customer And A Date At The Same Time

Jul 14, 2014

i have 2 dilemmas

**dilemma 1**

i have a date of purchase in column A

i have a customer number in column B

now if i want to search for a date and for it to display the results from columns C,D,E and F (purchased items) i know i use HLOOKUP

but how would i look for a customer and a date at the same time.

**dilemma 2**

i want to be able to put in a customer number and excel to tell me all the dates they purchased items.

currently i use

={IFERROR(INDEX(Table1[DATE],SMALL(IF(Table1[NUM]=$L$3,ROW(Table1[NUM])-MIN(ROW(Table1[NUM]))+1),ROWS($1:1))),"error")}

and i change the ROWS($1:1) to ROWS($1:2) or ROWS($1:3) etc

to bring in that result, but i want it to bring in unique dates, as a customer might ring us back with another purchase on the same date and i dont want it to duplicate the date.

View 13 Replies View Related

Transpose By Customer & Date

Aug 8, 2008

I need to create a list that has one row per paid date per customer on a new worksheet. My source data has one row per customer with the paid dates across 12 month columns. I have many rows in the source data with zero paid on all 12 months and I'd like to filter those out in the process.

This is not a simple Transpose because I want the customer number, and customer name to repeat for every instance of a paid amount.

I have attached a sample file with the current and desired layouts of the data.

View 6 Replies View Related

Find Latest Date For Particular Customer

Feb 18, 2014

I've attached a quick sample, I have two customer ID's, AB1 and AB2, I want to be able to put a formula into column C that will return the latest date for each customer. So AB1 will show 01/04/2013 and AB2 will show 01/05/2013. I know I can use a MAX formula but I don't know how to connect it to the specific customer ID.

Max Date Query.xlsx

View 3 Replies View Related

Can Capture Date Of First Sale To A Customer?

May 14, 2013

I am looking for a formula to capture the "date" of my first sale to new customers.

A B C D E F G H Date of first sale
1 Co. Name 2/2/2012 6/5/2012 7/6/2012 10/16/2012 1/22/2013 4/17/2013
2 ABC Co. $10 $11 $5 $12 $12 2/2/2012
3 Smith Co. $19 1/22/2013
4 Brown Co. $11 $15 6/5/2012
5 AAA Co. $5 $10 7/6/2012

View 2 Replies View Related

Last Invoice Date Per Customer Number

Jun 27, 2006

I am trying to automatically show the date of the last invoice per the customer number.

I have attached an example.

View 9 Replies View Related

Using DMAX To Find Most Current Date By Customer#

Jul 9, 2009

I have a spreadsheet of our Customers rankings with a column with the date that they sent their Report Cards in. I need to pull by Cust# the most current date. I tried using DMAX but I can not get the formula to work. The sheet is not sorted and can not be sorted by Cust#.

=DMAX(A$6:D$886,"Report dated",G$6:G7)

range field criteria

Title row starts at 6

A B C DCust #Marketing LocationCustomerReport dated1034ColmarScientific Systems11-Nov-021032ColmarRaymond4-Feb-031029ColmarPhiladelphia Gear6-Feb-031028ColmarPall4-Apr-031016ColmarGreene Tweed 22-Apr-031032ColmarRaymond6-May-031032ColmarRaymond15-Apr-041012ColmarATK Launch Systems, Inc.27-Apr-041032ColmarRaymond22-Oct-04

View 20 Replies View Related

I Have A List Containing Customer Data.. Can I Insert A Date Automatically?

Nov 4, 2008

I have a spreadsheet which I am trying to use to capture customer data. What I want to happen is whenever I enter a new record, the date appears automatically in a Date column ....

View 6 Replies View Related

Automatically Record The Last Date (and Time) Of Customer Last Massage Session

Aug 14, 2013

I ran a Home Service Massage, with ten massagers and it's continually growing. I know I'm supposed to use a database for this, but I'm not very good in creating one. I am however confident in using MS Excel, so in a much as possible, I only want to use MS Excel, and strictly use formulas (if I can get away with it).

Anyway, I keep customer records in one tab, and all their personal info is listed there. There are two types of columns, which I'd like to call 'static' and 'automatic'.

Static columns includes Unique ID (number), Name, Address, Phone Number, etc.

Automatic columns, by the term I used, is filled automatically. This includes the total number of hours they have availed the massage from us, the names of massagers who serviced them, --- and my biggest issue --- the date of last massage. I can manually input this, but I normally forget, so I want this filled automatically.

Aside from customer tab, I have one tab for each massager. First column is the date, then 48 columns for time. Starting from 8AM, 830AM, 9AM... 730AM. Hence I have 48 of them. Each column is equals to 30mins of massage availed. I copy and paste the Unique ID from customer tab.

so A2:A32 are dates, and B2:BC50 is where I copy paste the unique ID. B1:BC1 are time labes.

Is it possible for me, just by using formulas, to populate the 'last massage date' column?

View 2 Replies View Related

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

Converting Uneven Rows Of Data Per Customer Into 1 Row Of Data Per Customer

Aug 1, 2014

I have a list of Spid numbers and a service category identifying if it is a Water Spid or a Waste spid.

The Spid is split into 2 parts. The Core is the identifier and the Category states if it is a Water or a Waste spid.

If the 1st digit in the Category is a 1 = Water.

If the 1st digit in the Category is a 2 = Waste.

There may only be a water spid or a property may have a water and waste spid.

What I want to do is have the information on 1 row rather than 2 rows

I have data in this format

SPID
Core
Category
Service Category

100000000151
100000000
151
1

[code].....

and I need to convert it into this format.

Core spid
Water
Waste

100000020
100000020101
100000020251

I have tried IF(lookup) and it works for the 1st row but I can't get it to look at the 2nd row.My data consists of 400,000 plus rows

View 3 Replies View Related

Converting Text To Date And Sorting On Date

Mar 10, 2014

I am trying to convert a long list of dates from text to date (see A) so that I can sort them from newest to oldest. I've tried converting them using differnt formulas (see B:C) but these don't work.

View 8 Replies View Related

Sorting By Date For Irregular Date Format

Mar 14, 2014

I have a dataset where the dates are in the following format:

19970803 = 3rd August 1997 (YYYYMMDD)

My aim is to sort the data into months and into 'turn of the month' periods.

-Is there a way to isolate rows using a search term like "****08**" for all the august entries?
-Likewise if i want data between 27th-3rd for every month is there a way to sort for entries between "******27 - *******03".

View 6 Replies View Related

Sorting Data By Date Within A Date Range

Dec 31, 2009

Hello, I have a question about sorting information using dates but using a range. I need this to sort when I enter a date range into the spreadsheet (the blue blocks on the attached spreadsheet). The factors in colum G need to be sorted by month and input into the yellow highlighted area in column B. There may be an easier way of pulling the data from the "Data" sheet but this was the best I could figure out. Overall, January (or any of the months)needs to have the January (or appropriate) factor that fits within the date range that was input but sorted properly based on the months listed in Column A.

View 6 Replies View Related

Date Sorting With Key Field As Date And Month Only.

Apr 13, 2009

I need VBA code to sort the birthdays for all the employees in my company.

See the attached file for example.

I need to sort according to their month and then date and not with their year.
i.e it should not consider Year for sorting.

View 2 Replies View Related

Sorting By Date...?

Oct 7, 2009

I have a column showing date DD/MM/YYYY. I want to be able to get a new column stripping out the days so that I can chart changes over a month rather than each day of the last 2 years. I have tried altering the cell format to MM-YYY although this shows the format I want when i run a pivot chart it will still create records for individual days. I then tried copying and pasting the above as text only. This results in the pivot chart sorting A-Z rather than by date.

View 2 Replies View Related

Same Date And Day Sorting

Aug 17, 2009

1. I downloaded from my online bank a Excel statment file.
2. When I open it in Excel. The date order is wrong, it is newest entries first (top).
3. I need it the other way, oldest first. I get Excel to sort oldest to newest. Which it does, but....

When I have more than 1 entry on date, they are the wrong way around.

Example:
It should be,
01/06/09 £10
01/06/09 £105
01/06/09 £200.....

View 9 Replies View Related

Sorting A Table By Date In VBA

May 23, 2014

I am trying to sort by date in a table (It is named Linked_file_table), C173:I188. I would like the table to be sorted by I173 AZ.

View 1 Replies View Related

Sorting Data By Date

Dec 1, 2008

I have a Column J with Dates formated in the dd/mm/yyyy format how do I used the Selection.Sort key:= xx order:= to sort the rows that have a date in the selected columns. Where J_End in the code below is the Column I need to refer to its sorting to a point but im not sure whats wrong with it.

All I really need it to do is group all the ones with a date (completed) and without a date (not completed) thus why im sorting it and the other stuff prioritize it.

View 3 Replies View Related

Sorting Based On Date?

Dec 2, 2009

I have 3 rows to each customer. I need to sort according to column B, by date. However I need to keep all 3 rows togeather. Is this possible?

View 6 Replies View Related

Auto Date Sorting

Sep 21, 2007

Is there a way to have a sheet automatically sort itself by date? I keep selecting cells and sorting manually but it get tedious doing it over and over every time I enter something new. Like if I had dates in row G every time I added something new it would automatically sort all rows by date.

View 9 Replies View Related

Sorting Data By Date ..

Dec 23, 2008

I Have a daily spreadsheet that I want to sort automatically by date. It has a header in the first two rows ( which shouldnt be a problem as I can sort the data i want from from cell A3 to J100) etc..

Problem 1) Each date is separated by a blank row which I need to retain.

Problem 2) some records have multiple rows which are together and need to be retained

For example ...

View 9 Replies View Related

Sorting Rows On Different Tabs By Date?

Oct 23, 2012

I have a excel doc that have multiple tabs (different clients) with information (task, date due, who's responsible, and client), and I want to have a master tab that lists all tasks on any tab put in order by date. Essentially so everyone in the office can quickly see what their most pressing task's are.

View 3 Replies View Related

Pivot Table Not Sorting By Date

Dec 27, 2012

I have a birthday list with name, client, birthday. I want to create a pivot table that groups by month. Report layout is tabular. Then within that month, name, client & birthday (in date order). So my end result would look like;

January Jim Jones Company 1/4
Then rest of January names 1/15

Etc.

Right now, I can't get the birthday to sort by date within the month.

View 1 Replies View Related

Pivot Table And Date Sorting?

Jun 12, 2014

I am having the hardest time trying to get my pivot table to sort right. I downloaded data over a 7 year period and its sorted by months (January-2005 etc). The issue I am having is excel is putting it into this format

January-2005 January- 2006 ......... February - 2005 February-2005.

I need it to sort chronologically

jan 2005, Feb 2005, March 2005...... Jan 2006, Feb 2006 etc.

What do I have to do to get this fixed. I would rather do not have to do it manually each time I need this type of data.

View 3 Replies View Related

Advanced Sorting Based On Time And Date?

Jul 29, 2013

I am trying to do an analysis on 4 spreadsheets, each with approximately 4500 cells.

Each vertical cell represents a patient visit to a hospital. There is a timestamp, then a bunch of other metrics that I will be comparing. I want to sort the 4500 visits by time and date. This is how the timestamp of their arrival shows up:

Jul 02 2012 10:25 AM

^A single cell contains both the date and time next to each other.

I want to first separate all visits by time of day. I want to make one group that checked in between 10a and 10p and another for 10p-10a. Then I want to sort both groups in chronological order by date (i.e. starting with 2011 and ending in May 2013).

For one, I'm thrown off because the time and date are next to each other in the same cell.

View 4 Replies View Related

Custom Sorting A Column By Date....and Text

Jul 17, 2009

I need to custom sort a column. I have 3 different types of data in the column. First - multiple dates, Second - "TBA", and Third - "ASAP". What I need is when the column is sorted the "ASAP" rows will be first, the dates (sorted) will be next and finally the "TBA"s. I have been trying to use a custom list.

View 2 Replies View Related

Sorting Duplicates And Displaying Latest Date?

Dec 23, 2011

I am working with a large spread sheet of people names and the courses that they have attended and what date they attended. The Sheet Identifies : ID No. Names, Courses, Date Attended, Due Date How can i remove all entries execpt for the last date for each qualification entered on each person?

ABCDEID. No.Name CourseDate AttendedDue Date22SmithFirst Aid2010/10/102011/10/1022SmithFirst Aid2011/10/102012/10/1021BobECDL2010/09/092011/09/0921BobECDL2011/10/102012/10/1022SmithECDL2010/08/082011/08/0822SmithECDL2011/09/092012/09/0920TerryFirst Aid2010/09/092011/09/0920TerryFirst Aid2011/10/102012/10/10

View 5 Replies View Related







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