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
ADVERTISEMENT
Jan 26, 2010
I've got a list of donor names, names listed multipled times in Column A, each row with different donation data. For instance:
Sheet 1
A1 - Mary Jones, B1- Internet Donation
A2 - Mary Jones, B2 - Silent Auction Purchase
A3 - Mary Jones, B3 - Event Ticket Purchase
A4 - Gary Jones, B4 - Foundation Grant
A5 - Gary Jones, B5 - Internet Donation
A6 - Sara Parker, B6 - Event Ticket Purchase
I want Excel to compare rows A1 and A2, and if the name has not changed, enter the donation data from Column B into another sheet that will just list that person's name once:
Sheet 2
A1 - Mary Jones, B1- Donation1, C1 -Donation2, D1 - Donation3
A2 - Gary Jones, B2 - Donation1, C2 - Donation2, D1 - Donation3 (which would return no value because there the name changes
Does this involve IF, MATCH or VLOOKUP formulas?
View 9 Replies
View Related
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
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
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
Mar 14, 2014
I have a very detailed spreadsheet with drop down lists. I would like to block other cells if previous cells have certain selections. I have 7 columns of data that get inputted throughout the month. These cells have drop down selections that I have created. Is there any way to create a drop down menu AND another data validation? So If a selection is made, then the next cells will be blocked, using data validation "custom"?
View 2 Replies
View Related
Jan 7, 2008
I have a simple list of all purchases made. ie)
Name.......Purchase date
John........01.01.07
Susan......06.08.07
John........07.07.07
John........01.05.07
I'd like to roll up the sames to create one customer row, but so I see the varience between purchase times. ie)
Name.......Ist Pur date....2nd pur date.....3rd pur date....time from pur 1 to 2
John........01.01.07.........01.05.07..........07.07.07........120 days
Susan......06.08.07...................................................(not sure to include this)
Is this possible in excel?
View 9 Replies
View Related
May 8, 2009
Have a small data base with customer details and comments. I would like a form to sit over that so that people on phones can enter customer # and check info and add further comments etc. for a specific customer. Before I try and design a form is there any templates for this type of thing. Have done a Google and Ozgrid search, but I maybe asking the incorrect names for the procedure.
View 2 Replies
View Related
Apr 1, 2013
I have problems with my project. I want to filter data between two dates with customer ID and when click Export Report button , result will copy on next sheet ( sheet's name is report in my file attachment ) and auto sum similar products.
eExample_report.xlsm
View 1 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
Mar 18, 2014
I have created a excel workbook in which I use for keeping up with storage unit rent. I have a tenant list sheet and each tenant has a sheet on which I keep up with charges and credits. What I want is a macro the will loop thru each tenants sheet based on tenant list and insert data and go to the next sheet. I found this on the web:
[Code] ......
View 1 Replies
View Related
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
Feb 2, 2012
I currently have a number of data sets relating to customer's and values, eg.
Customer 1 12 12 12 12 12
However within this data set I also have a number of items where different customers have been grouped but are separated with a “/”, eg.
Customer 1 / Customer 2 / Customer 3 12 12 12 12 12
I want to be able to, find these rows, and break them out so that each customer with relating data is on a separate row, eg.
Customer 1 4 4 4 4 4
Customer 2 4 4 4 4 4
Customer 3 4 4 4 4 4
View 7 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
