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