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

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

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

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

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

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

Multiple Sheets + Transpose + Date

Jun 18, 2007

I joined a file so that you guys can understand what my problem is! In test4.xls there is 4 sheets. The one named "End" should appear in a new workbook. So i'm trying to make a macro that will transform sheet A,B and C into the sheet "End" in a new workbook! i know i have to transpose the data, but i didnt find how! The problem is that the macro have to work for each month of any years(2006,2007,etc)! Anyone know how to do it?

View 14 Replies View Related

VBA To Auto Transpose Lists According To Date

Jul 3, 2009

I have attached a spreadsheet of the data i need altered.

Column 1 - Time
Column 2 - Date
Column 4 - Data for Site

I need these to be easily formatable, Is there any way of producing a macro which will format these as follows?

Row 1 - TIME
Column 1 - Date
Data filled in correct place,

I have placed a sample of the output format in the work book.

View 2 Replies View Related

Isolate Customer Name

Mar 30, 2009

I have a list of data in which it will ALWAYS have the following set up:
S-CustomerName-####

"S-" and "-####" will always remain the same - however, the customer name may be 2 digits or 20 digits - or anything inbetween.

I'm looking for a formula that will allow me to isolate the Customer name

I've attached a sample list of my data.

View 3 Replies View Related

Customer Filter

Mar 30, 2009

I have data for customers and the amounts they have paid.

In the amounts column i have figures and word.

What i wish to do is exclude/ filter from the list customers which only say large in the amount column.

However the problem i have is some customer which also have figures which i do not wish to exclude.

So basically i what to look down the whole list of customers for j blogg ( example) and see in the corresponding amount column. if it says large on every
amount line then i want to exclude them from the list.

View 9 Replies View Related

Total Units For Each Customer

Feb 3, 2009

I have been tasked with tracking the expected income for our Advertising Department. (I work for a newspaper.) The rate I charge an ad depends upon the total number of advertising units a customer purchases during an entire billing period. Essentially: I need to have a total of column G for when column B matches the current row. I need this in column K. The value of K should be the same when the value of B is the same.

My programing flow chart I sketched out checked to see if any row in column B that is above the current row matched the current row's data. If it did then it would use that row's value for the total number of units. If it did not it would add up the units from G when B matched the current column. I attached a document

View 2 Replies View Related

Matching Customer Data

Apr 16, 2009

I have 2 sheets in a workbook.Sheet1 is a master sheet with Customer code and there Sale.And Sheet2 is the sheet which has only Customer code.

Note: Its just a Example i have total 1924 data.

Now in Sheet 1 there are some Customer code which is Repeated some times 2 and 3 or even 4 times. In Sheet 2 Same as sheet 1 there are some data which is also Repeated. Now i how can i know which Customer Code in sheet 2 Missing from Sheet1
I have Colored Some Data in sheet 2 which is not match with Sheet1

For Example.
In Sheet 1 221000000232 is repeated 2 times but in Sheet2 has only 1 times.

View 2 Replies View Related

Subtotal Each Month For Every Customer

Dec 13, 2012

I have a worksheet that contains the following columns:

Month
Customer
Invoice
Freq.
Total

[Code]...

Now, I want to have subtotals for each customer and each month.

Right now, I have to manually insert =count & =sum functions at the appropriate rows.

Ultimately, I want to know the average sales activity per month and average sales nominal per month.

Is there a better way to achieve what I want?

I had thought of COUNTIFS and SUMIFS, but then I'll have to change the criteria at every change of month & customer.

The average sales activity & average sales will be derived manually.

View 6 Replies View Related

Formula To Exact Customer Number Only?

Jan 27, 2014

I have a list of customer including customer name and customer number. Is there a way to exact customer number out only? This is the example list.

WAL-MART STORES INC - 1028
KOHL'S - 28983
JOHN'S PET PRODUCTS - 3692
PET SUPERMARKET, INC. - 2298
COSTCO WHOLESALE - INTERNATIONAL - 2783
LOBLAW CO-WESTERN GROCERS - 7424

All I want is the number behind the dash. There are few customers that have more than 2 dashes.

View 8 Replies View Related

Dropdown List For Customer In Job File

Apr 25, 2014

I have a workbook that I open for each job we receive. On the first sheet I call the job information sheet I hand enter the following info

Customer name in one merged cell
address in one merged cell
city and state zip in one merged cell

This info is then transferred to a few other sheets within the workbook IE invoice sheet, Labor sheet ect

I would like to created a customer list that I can use a pull down list and input this info on the job information sheet

I have attached a copy of my file for reference.

Master CoopyALL SEASONS MATERIAL & LABOR REPORT.xlsm‎

View 4 Replies View Related

Create Customer ID Number And Then Find?

May 3, 2014

I want to create a customer ID for thousands of customers and then be able to find the customer by doing a search for the ID later. I'm even thinking of using a customer phone number for the ID. What function can I use to later find the customer?

View 10 Replies View Related

Summing Total Per Customer For Each Month

Nov 4, 2009

Please see the attached example.

I am working with Office 97 and I know that if I had Office 2007 this question could be easily answered with the "SUMIFS" function. But since Office 97 doesn't have that formula I need to find another way to accomplish what i am trying to do.

In the gray boxes I need a formula that will sum the amounts for each customer for each month. For example, the total amount that shipped to customer A in October is 180.4.

I will be applying this to a much larger amount of data, so please do not provide a formula that only works for the data supplied in this example. I will be continuously adding more info to my list over time and I want the count to automaticaly include the data when I enter it.

View 13 Replies View Related

Customer Records Data Sheet

Nov 8, 2008

i use excel in my dispatch office. i am working on a new workbook and could use some help. In one sheet of the workbook we enter in customer calls. i would like to keep customer records in another sheet, which would include their address, phone numbers, and past invoice numbers all on the same row of the customer database sheet. At the top of the sheet that we enter caller information i would have a table set up to automatically fill in the customer info based on the name typed in,

if the customer exists in the customer database sheet of the same workbook the table will automatically fill in their address, phone number and past invoice numbers. i was thinking something like this =IF(COUNTIF('Customer Database'!A:A,Blanks!D6)>0,'Customer Database'!A2,"Not Found") ....the problem is that no matter who's name i enter, it will always add the information from row 2 of the customer database sheet. i need it to take information from the corresponding row(if customer name is found on row 45 then my formula needs to enter the info from row 45, not row 2. not sure how to do this.

View 5 Replies View Related

Look Up Customer Information Based On Dates

Jan 9, 2009

I'm still having trouble with this formula. I have a sheet, sample attached, that has a list of transactions, with customer name, date, cash-in & cash-out information that is listed down a set of rows. I'll have a tab for each year & the information will be seperated by months.

My other sheet is a monthly report that I will be printing. I have a seperate "section" for each customer & I want excel to fill the information from that customer based on the month. I tried using a index match formula & a vlookup formula in a helper column, but it doesn't bring out the info that I want.

View 6 Replies View Related

Custom Format In VBA - Name Of Sheet For Customer

May 28, 2013

I have to create about 100 sheets based off of a customer number in column a. The customer number in column a is something like 000000001905. I want the name of the sheet for that customer to be 019.05. Here is my code which is returning the sheet name as 01905.00

Code:
Sub createWorksheets()
nRow As Long

nRow = 7

Do Until nRow = 108
Sheets("Blank Customer Statement").Copy after:=Sheets("Table of Contents")
Sheets(2).Name = Format(Right(Sheets("Table of Contents").Range("A" & nRow).Value, 5), "000.00")
nRow = nRow + 1
Loop

End Sub

View 3 Replies View Related

Code To Create Customer Number

Dec 14, 2007

I have the following formula copied down from A3 to A200 in a spreadsheet, which creates a unique standardized customer number for the each names listed in B3 to B200:

=IF(B3="","","S"&LEFT(B3,1)&"-"&TEXT(SUMPRODUCT(--(LEFT($B$3:B3,1)=LEFT(B3,1)))*10,"0000"))

Therefore, with the following names listed in B3 to B10:

UPS
FedEx
Fisher Price
Bell Canada
Grand & Toy
Rogers Cable
Dominion
Blue Jays

The respective customer numbers would be:

SU-0010
SF-0010
SF-0020
SB-0010
SG-0010
SR-0010
SD-0010
SB-0020

I would like these numbers to be static, so I would appreciate it very much if someone can give me a VBA code to replace the formula.

View 9 Replies View Related

Identify The Customer And The Discounted Amount

Jul 21, 2009

I am trying to write a fromula that will identify the customer and the discounted amount for that customer. The problem that I am having is I have more customer than what I am allowed to put into my formula.

here are the formulas that I am using, I am not sure how to make this work for more customers.

=IF(A2="","",(IF(A2="Customer 1","Discounted Price:",(IF(A2="Customer 2","Discounted Price:",(IF(A2="customer 3","Discounted Price:",(IF(A2="customer 4","Discounted Price:",(IF(A2="customer 5","Discounted Price:",(IF(A2="customer 6","Discounted Price:",(IF(A2="customer 7",c1,"")))))))))))))))

=IF(A2="","",(IF(A2="customer 1",100-C1*F2,(IF(A2="customer 2",100-C1*F3,(IF(A2="customer 3",100-C1*F4,(IF(A2="customer 4",100-C1*F5,(IF(A2="customer 5",100-C1*F6,(IF(A2="customer 6",100-C1*F7,(IF(A2="customer 7",100-C1*F8,"")))))))))))))))

I have tried to put in the excel cell to show the example, but I can't seem to figure out how to add it to this.

View 9 Replies View Related

Summarize Customer Order Data

Jul 3, 2009

I have created a simple order form to take customer orders . I would like to summarize the orders on a summary page .(The order Form will be very long so i don't want to print the entire document every time .

I have started to create a summary page using information from a similar post on this forum. I am having trouble creating a summary when the products on my order form have multiple sizes rather than just one

View 9 Replies View Related

Display Chosen Customer Details

Dec 14, 2007

I have 3 sheets (Master,Detail1,Details2) in my workbook. In the master sheet I w'll have customer names. In the detail1 & 2 sheet2 I will have their details.
When I click on the customer name from my Master sheet it will go to corresponding sheet. I have provided link for that and it's working fine.

But my requirement is when I click the customer from Master sheet, I want to get into the detiled sheet as well as want to display the records only related to the custmor I clicked/selected in my master sheet.

View 2 Replies View Related

Edit Customer Information Rows

Jul 1, 2008

I am trying to develop a procedure that allows the user select a customer from a drop down list (using Data validation), thereby providing the user with a summary of customer information. That I can do. What I would like to do is allow the user to add new information in the summary section which is then appended to the customer record further down the sheet (see attached example). New data could only be added to the last two columns. The user would be required to press a form button to append the data.

The catch would be if the customer was selected again, the new data would also display in the summary section.

View 4 Replies View Related

Index And Match - Display Customer Name Taken From Table

Jul 27, 2014

Look at the image I've attached.

I'm trying to have the table on the right match the first two columns to display the customer name taken from the table on the right.

testtable.jpg‎

View 13 Replies View Related