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


ADVERTISEMENT

Create Formula That Will Determine Number Of Orders For Specific Customer?

Aug 29, 2012

I am trying to create a formula that will determine the number of orders for a specific customer whose orders have a pallet count between a certain range. The customer code is a cell reference, as well as the beginning and ending values I am looking for. I have the data on a separate tab from where I am putting the formula.

Here is what I was trying to use, but it was coming up with "0" for everything:

=COUNTIFS(Data!$I:$I,E7,Data!$I:$I,">=A8",Data!$I:$I,"

View 1 Replies View Related

Formula That Calculates AVG Number Based On Exact Criteria

Jan 14, 2014

Is there any formula that could calculate total average number of recruitment days (i'ts one number), but only for "Diana" and "Skipper" (excluding "Jennifer")?

Please see an example:

Recruitment daysResponsible
12Diana
13Diana
19Diana
16Skipper
23Jennifer

[Code] .....

I guess it has something to do with AVG and IF formulas.

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

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

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

Count Number Of Customer Accounts In A Report

Feb 18, 2014

I'm looking to count the number of customer accounts in a report based on the branch number. I generally need to know, how many accounts has been opened per each branch without double counting the same account number (as it happens that the report may have duplicate number.

finally I need to know the sub total showing the total per branch and subtotal of all branches together.

View 14 Replies View Related

How To Know Exact Number Of Month

May 12, 2014

I need formula to change the the date to number of months. Example : May or 1-5-2014 to be changed to days like this = 31.

View 4 Replies View Related

Sum Of Exact Number Of Cells

May 29, 2007

Let's say I have one column A (A1 TO A10) and each one of these 10 cells are filled with numbers.

Second, in cell B1 I have a random number (1 - 10).

Now, the problem is: I don't know how to make a sum of exact number of A following column cells, like number in B1.

Example: If there is number 4 in B1 than I need Excel to make sum only for first 4 cells (A1-A4).

Please what is command for this action?

View 9 Replies View Related

Create A Loop: Find Out For Each Item Number Which Customer Put Order

Oct 8, 2008

I’m getting data from query. My data has fields: Item No, Customer ID & Sales rep.
I need to find out for each Item number which customer put order and who was the sales rep. (Excel 2002). see file attached.

View 4 Replies View Related

How To Do VLookup To 1 Sheet Based On Customer Number And Return Price

Dec 9, 2011

I am trying to do a vlookup to 1 sheet based on customer number and return price. If no customer number than I want to do a vlookup based on dunnage number to return the price. This is what I have for a formula but it never returns a value if one is found in the first lookup.

=IFERROR(VLOOKUP(C4,'Dunnage Exception'!A:C,3,FALSE),IFERROR(VLOOKUP('Raw Data'!D4,'Dunnage Master'!A:C,3,FALSE),"0"))

View 2 Replies View Related

Ranking Number Within Range (Not Exact Match)

Apr 18, 2013

I have a number that I'd like to see ranked within a separate range. Ie if the number is 25 and I want to see how it would be ranked if it were in a range of:

3
43
12
19
22
7
88

(The answer would be 3 - 3rd highest). The RANK formula requires an exact match...

View 2 Replies View Related

Vlook-up Exact Number Match In Sheet

Dec 19, 2008

Sheet 1
23456
23479
23409
23512

Sheet 2
23455
23456
23457
23478
23479
23480
23408
23409
23410
23511
23512
23513

In Sheet 1 there's a Column B with data I'd like to pull using a VLOOK-UP formula. However, I need it too look-up not only the exact number match in Sheet 2, but also the cell directly above it and the cell directly below it. In the sample above if I placed the VLOOK-UP formula in Sheet 2 (at for instance the numbers 23455, 23456, 23457), I'd need it to pull data from Sheet 1, Column B (for the 23456).

View 9 Replies View Related

Specify Finite And Exact Number Of Characters Between Colons When Searching?

Mar 1, 2012

How I can find instances of text strings containing two colon characters separated by any two (arbitrary) characters?

So in other words, "xxxx:xx:xxxx"

If I use :

Code:
SheetName.Cells.Find(What:=":**:", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious)

...I get a return of any strings containing two colon characters (regardless of the number of characters in-between) - because obviously the asterisk(s) denote any text whatsoever.

How do I specify a finite and exact number of characters between the colons when searching?

View 3 Replies View Related

Formula Does Not Calculate Numbered Customer Accounts

Feb 4, 2009

following calculation that someone created for me.

=LOOKUP(10^10,CHOOSE({1,2},0,AVERAGE(IF(Sheet1!$C2:$C13=$A2,IF(Sheet1!$B2:$B13-DAY(Sheet1!$B2:$B13)+1=B$1,Sheet1!$D2:$D13)))))

My problem is that I have some customer account numbers that are alpha/numeric (1B0012) and some that are just numeric (1675) . The above formula does not pick up the numeric accounts correctly.

Is there something that I need to add/remove from this formula so that it will pick up both? or is it a format issue?

I have attached an example of the spreadsheet with this problem. If you select either of the alpha numeric codes it works, but if you select the numeric code it doesn't.

View 9 Replies View Related

Formula For Getting Exact Names

Jul 11, 2014

I have percentages in Column "A" & Names in "B".

There are duplicate percentages and i need have the Names in between the percentages for 90% - 99%. Here i am using Vlook up function in Column "H" and i am getting error. This will only takes the first Name but i need all other Names for these percentages.

Hereby I am attaching the sample sheet : Lookup Values.xlsx‎

View 6 Replies View Related

Exact Formula Across Worksheets

May 8, 2007

I need to compare a particular range of cells using Exact and display the result of comparison of ( Sheet1 and Sheet2 ) in Results screen. It can be done by statically putting up range of values but Issue comes when user dynamically input the range of values

Dim FirstCell, LastCell As Range

Set FirstCell = Application.InputBox("Click the Last cell to end comparing to", _
Type:=8)

Set LastCell = Application.InputBox("Click the Last cell to end comparing to", _
Type:=8)

'Statically specifying the range - Works fine
Worksheets("Results").Range("A1:D4").Formula = "=EXACT(Sheet1!A1,Sheet2!A1)"

'User Inputs the range - Does Not work
Worksheets("Results").Range(LeftCell, RightCell).Formula = "=EXACT(Sheet1!FirstCell ,Sheet2!FirstCell )"

View 8 Replies View Related

False Negative Results From Exact Formula

Aug 4, 2014

I have several thousand values to compare, using = Exact(A2, B2) to find exact matches and return TRUE/FALSE. I've gotten a bunch of incorrect results especially where both values equal zero.

Data attached : Exact_values.xlsx‎

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

Add "x" If Customer Number Matches A List

Jan 9, 2007

I have 1) a list of 10,000 customer records with customer numbers (including duplicates) in field A, and 2) a separate list of 25 customer numbers that are "red" accounts.

I want to add a new ID field to the larger customer list, and using the 25 red IDs, I want to mark any of the 10K records that match the list of 25 with an "x".

I thought about using multiple nested-function formulas and concatenating the results, but then, I always do things the hard way! ...

View 9 Replies View Related

Cells Not Being Populated With Exact Same Formula As Previous Working Cells

Jun 27, 2008

formula using if's and vlookup's to populate a calendar with x's for day a person is gone. however the last couple of people that are gone are not being marked on the calendar even though i use the exact same formula (except for cell numbers and such) for previous, working entries. Here is my formula

View 9 Replies View Related

Sticky Paste And Paste Exact Formula

Feb 5, 2008

I m working with office 2003 pro in Windows XP.

2 Excel questions:

Is there a way to make the "paste" option in excel "sticky" (that is, so that you can do some other work in excel then come back and click "paste"? currently excel makes you go back and recopy then only can you paste. You cannot do anything between "copy" and "paste".

Is there a way to paste so that the cell is exactly copied? What i mean is, so that the formulas that are copied are pasted exactly as they were (without changing references)? (i get the right result when draggin and moving a cell, except that the original cell is emptied - but u get the idea). A work around is to add absolute refrences, and then take them away later - or to copy the formula in the formula bar and then paste it into another cell (one cell at a time ... )

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

Formula To Calculate Based On Start Month Number And Duration Number Of Months

Mar 14, 2014

I need a formula that will calucalte the monthly total based on the following conditions:

Col A = Yearly Cost
Col B = The number of the month when the costs are starting (1=Jan etc)
Col C = The duration or the number of months for which the costs are to spread
Col D is Year 2013 with the months across columns D-O. Row 1 above those columns shows the month's corresponding number.

Right now I have =IF($B3<=D$1,$A3/$C3,0) however if my start month is 1 and my duration is 5, I need the costs to stop after May. I've attached a sample file. Calculate based on start month and duration.xlsx

View 3 Replies View Related

Excel 2013 :: Formula To Auto Delete Number In Dropdown List When Number Appears In Another Cell

Apr 27, 2014

I'm making a Excel 2013 spreadsheet that has formula in a column that auto enters a number 1-40 when something is entered to the left of that cell. There are 300 rows in the spreadsheet. I would like to make a drop down list in a column cell to the right that would delete that number in that cell from the drop down list. For example cell C1 has 39, that 39 then is deleted from the drop down list. C2 has 22 in it, click on the drop down list cell and it shows 1-40 less 39 and 22.

View 11 Replies View Related

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

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







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