Sumproduct a column where 2 adj text columns contain same value

Hi,
I'm using a sumproduct formula to ascertain the number of times that a value 
between 0 and 1 occurs in column A (range A3:A26), where the adjacent cell in 
column B (B3:B26) contains either "Smith", "Draper" or "Jones":

=SUMPRODUCT((A3:A26>0.00)*(A3:A26<1.01)*(B3:B26={"Smith","Draper","Jones"}))

This formula works fine and details the number of entries where the value in 
column A is between 0 and 1, and the name in the adjacent column B cell is 
Smith Draper or Jones. 

What I need to do is the same SUMPRODUCT of 0-1 entries in column A but this 
time where the names listed in column B are equal to more names listed in 
Column C (i.e. where B7 = "Jones" and C7 = "Jones", 1 will be the added to my 
SUMPRODUCT total (if the value in A7 is between 0 and 1).

Keep skirting around the edges of this one without being quite able to nail 
it.
Any pointers gratefully received.
Cheers,
Steve.
0
3/5/2009 2:29:01 AM
excel.newusers 15348 articles. 2 followers. Follow

5 Replies
925 Views

Similar Articles

[PageSpeed] 38

Use cells to hold the criteria** :

E3 = 0
F3 = 1.01
G3 = Jones
H3 = Draper
I3 = Smith

=SUMPRODUCT(--(A3:A26>E3),--(A3:A26<F3),--(B3:B26=G3),--(C3:C26=G3))

** Use cells to hold the criteria:

You'd have to redo this formula since array constants can't use cell 
references.

>=SUMPRODUCT((A3:A26>0.00)*(A3:A26<1.01)*(B3:B26={"Smith","Draper","Jones"}))

=SUMPRODUCT(--(A3:A26>E3),--(A3:A26<F3),--(ISNUMBER(MATCH(B3:B26,G3:I3,0))))

-- 
Biff
Microsoft Excel MVP


"Struggling in Sheffield" <StrugglinginSheffield@discussions.microsoft.com> 
wrote in message news:21497C12-991F-4ECE-83DD-3F96D718455B@microsoft.com...
> Hi,
> I'm using a sumproduct formula to ascertain the number of times that a 
> value
> between 0 and 1 occurs in column A (range A3:A26), where the adjacent cell 
> in
> column B (B3:B26) contains either "Smith", "Draper" or "Jones":
>
> =SUMPRODUCT((A3:A26>0.00)*(A3:A26<1.01)*(B3:B26={"Smith","Draper","Jones"}))
>
> This formula works fine and details the number of entries where the value 
> in
> column A is between 0 and 1, and the name in the adjacent column B cell is
> Smith Draper or Jones.
>
> What I need to do is the same SUMPRODUCT of 0-1 entries in column A but 
> this
> time where the names listed in column B are equal to more names listed in
> Column C (i.e. where B7 = "Jones" and C7 = "Jones", 1 will be the added to 
> my
> SUMPRODUCT total (if the value in A7 is between 0 and 1).
>
> Keep skirting around the edges of this one without being quite able to 
> nail
> it.
> Any pointers gratefully received.
> Cheers,
> Steve. 


0
biffinpitt (3172)
3/5/2009 3:00:22 AM
Hi,
I get the gist of what you're saying but can't see how B3:B26 is being 
compared to C3:C26 for the three names?

            A                B                  C
3        0.12           Smith            Jones
4        2.03           Draper          Draper
5        0.65           Jones            Jones
6        0.81           Smith            Smith
7        0.33           Jones           Draper
8        1.52           Smith           Smith
9        0.74           Jones           Jones
10      3.02           Draper          Jones

For the table illustrated I need my formula to return the value of "3".

A5 has a value less than 1 (0.65), whilst B5 & C5 have the same name "Jones" 
(count 1)
A6 has a value less than 1 (0.81), whilst B6 & C6 have the same name "Smith" 
(count 2)
A9 has a value less than 1 (0.74), whilst B9 & C9 have the same name "Jones" 
(count 3)

Whilst names match on other rows or the value in column A is less than 1, 
only three times do all these criteria line up and this is what I need to 
count.
Cheers,
Steve.



"T. Valko" wrote:

> Use cells to hold the criteria** :
> 
> E3 = 0
> F3 = 1.01
> G3 = Jones
> H3 = Draper
> I3 = Smith
> 
> =SUMPRODUCT(--(A3:A26>E3),--(A3:A26<F3),--(B3:B26=G3),--(C3:C26=G3))
> 
> ** Use cells to hold the criteria:
> 
> You'd have to redo this formula since array constants can't use cell 
> references.
> 
> >=SUMPRODUCT((A3:A26>0.00)*(A3:A26<1.01)*(B3:B26={"Smith","Draper","Jones"}))
> 
> =SUMPRODUCT(--(A3:A26>E3),--(A3:A26<F3),--(ISNUMBER(MATCH(B3:B26,G3:I3,0))))
> 
> -- 
> Biff
> Microsoft Excel MVP
> 
> 
> "Struggling in Sheffield" <StrugglinginSheffield@discussions.microsoft.com> 
> wrote in message news:21497C12-991F-4ECE-83DD-3F96D718455B@microsoft.com...
> > Hi,
> > I'm using a sumproduct formula to ascertain the number of times that a 
> > value
> > between 0 and 1 occurs in column A (range A3:A26), where the adjacent cell 
> > in
> > column B (B3:B26) contains either "Smith", "Draper" or "Jones":
> >
> > =SUMPRODUCT((A3:A26>0.00)*(A3:A26<1.01)*(B3:B26={"Smith","Draper","Jones"}))
> >
> > This formula works fine and details the number of entries where the value 
> > in
> > column A is between 0 and 1, and the name in the adjacent column B cell is
> > Smith Draper or Jones.
> >
> > What I need to do is the same SUMPRODUCT of 0-1 entries in column A but 
> > this
> > time where the names listed in column B are equal to more names listed in
> > Column C (i.e. where B7 = "Jones" and C7 = "Jones", 1 will be the added to 
> > my
> > SUMPRODUCT total (if the value in A7 is between 0 and 1).
> >
> > Keep skirting around the edges of this one without being quite able to 
> > nail
> > it.
> > Any pointers gratefully received.
> > Cheers,
> > Steve. 
> 
> 
> 
0
3/5/2009 4:00:00 AM
I misunderstood your requirement.

I thought you just wanted to match Jones and Jones.

Try this:

E3 = 0
F3 = 1.01
G3 = Jones
H3 = Draper
I3 = Smith

=SUMPRODUCT(--(A3:A26>E3),--(A3:A26<F3),--(B3:B26=C3:C26),--(ISNUMBER(MATCH(B3:B26,G3:I3,0))))

-- 
Biff
Microsoft Excel MVP


"Struggling in Sheffield" <StrugglinginSheffield@discussions.microsoft.com> 
wrote in message news:7294072D-82C0-4922-BBE7-A18BC7A651B3@microsoft.com...
> Hi,
> I get the gist of what you're saying but can't see how B3:B26 is being
> compared to C3:C26 for the three names?
>
>            A                B                  C
> 3        0.12           Smith            Jones
> 4        2.03           Draper          Draper
> 5        0.65           Jones            Jones
> 6        0.81           Smith            Smith
> 7        0.33           Jones           Draper
> 8        1.52           Smith           Smith
> 9        0.74           Jones           Jones
> 10      3.02           Draper          Jones
>
> For the table illustrated I need my formula to return the value of "3".
>
> A5 has a value less than 1 (0.65), whilst B5 & C5 have the same name 
> "Jones"
> (count 1)
> A6 has a value less than 1 (0.81), whilst B6 & C6 have the same name 
> "Smith"
> (count 2)
> A9 has a value less than 1 (0.74), whilst B9 & C9 have the same name 
> "Jones"
> (count 3)
>
> Whilst names match on other rows or the value in column A is less than 1,
> only three times do all these criteria line up and this is what I need to
> count.
> Cheers,
> Steve.
>
>
>
> "T. Valko" wrote:
>
>> Use cells to hold the criteria** :
>>
>> E3 = 0
>> F3 = 1.01
>> G3 = Jones
>> H3 = Draper
>> I3 = Smith
>>
>> =SUMPRODUCT(--(A3:A26>E3),--(A3:A26<F3),--(B3:B26=G3),--(C3:C26=G3))
>>
>> ** Use cells to hold the criteria:
>>
>> You'd have to redo this formula since array constants can't use cell
>> references.
>>
>> >=SUMPRODUCT((A3:A26>0.00)*(A3:A26<1.01)*(B3:B26={"Smith","Draper","Jones"}))
>>
>> =SUMPRODUCT(--(A3:A26>E3),--(A3:A26<F3),--(ISNUMBER(MATCH(B3:B26,G3:I3,0))))
>>
>> -- 
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Struggling in Sheffield" 
>> <StrugglinginSheffield@discussions.microsoft.com>
>> wrote in message 
>> news:21497C12-991F-4ECE-83DD-3F96D718455B@microsoft.com...
>> > Hi,
>> > I'm using a sumproduct formula to ascertain the number of times that a
>> > value
>> > between 0 and 1 occurs in column A (range A3:A26), where the adjacent 
>> > cell
>> > in
>> > column B (B3:B26) contains either "Smith", "Draper" or "Jones":
>> >
>> > =SUMPRODUCT((A3:A26>0.00)*(A3:A26<1.01)*(B3:B26={"Smith","Draper","Jones"}))
>> >
>> > This formula works fine and details the number of entries where the 
>> > value
>> > in
>> > column A is between 0 and 1, and the name in the adjacent column B cell 
>> > is
>> > Smith Draper or Jones.
>> >
>> > What I need to do is the same SUMPRODUCT of 0-1 entries in column A but
>> > this
>> > time where the names listed in column B are equal to more names listed 
>> > in
>> > Column C (i.e. where B7 = "Jones" and C7 = "Jones", 1 will be the added 
>> > to
>> > my
>> > SUMPRODUCT total (if the value in A7 is between 0 and 1).
>> >
>> > Keep skirting around the edges of this one without being quite able to
>> > nail
>> > it.
>> > Any pointers gratefully received.
>> > Cheers,
>> > Steve.
>>
>>
>> 


0
biffinpitt (3172)
3/5/2009 4:41:58 AM
Hi Biff,
Works a treat, many thanks.

"T. Valko" wrote:

> I misunderstood your requirement.
> 
> I thought you just wanted to match Jones and Jones.
> 
> Try this:
> 
> E3 = 0
> F3 = 1.01
> G3 = Jones
> H3 = Draper
> I3 = Smith
> 
> =SUMPRODUCT(--(A3:A26>E3),--(A3:A26<F3),--(B3:B26=C3:C26),--(ISNUMBER(MATCH(B3:B26,G3:I3,0))))
> 
> -- 
> Biff
> Microsoft Excel MVP
> 
> 
> "Struggling in Sheffield" <StrugglinginSheffield@discussions.microsoft.com> 
> wrote in message news:7294072D-82C0-4922-BBE7-A18BC7A651B3@microsoft.com...
> > Hi,
> > I get the gist of what you're saying but can't see how B3:B26 is being
> > compared to C3:C26 for the three names?
> >
> >            A                B                  C
> > 3        0.12           Smith            Jones
> > 4        2.03           Draper          Draper
> > 5        0.65           Jones            Jones
> > 6        0.81           Smith            Smith
> > 7        0.33           Jones           Draper
> > 8        1.52           Smith           Smith
> > 9        0.74           Jones           Jones
> > 10      3.02           Draper          Jones
> >
> > For the table illustrated I need my formula to return the value of "3".
> >
> > A5 has a value less than 1 (0.65), whilst B5 & C5 have the same name 
> > "Jones"
> > (count 1)
> > A6 has a value less than 1 (0.81), whilst B6 & C6 have the same name 
> > "Smith"
> > (count 2)
> > A9 has a value less than 1 (0.74), whilst B9 & C9 have the same name 
> > "Jones"
> > (count 3)
> >
> > Whilst names match on other rows or the value in column A is less than 1,
> > only three times do all these criteria line up and this is what I need to
> > count.
> > Cheers,
> > Steve.
> >
> >
> >
> > "T. Valko" wrote:
> >
> >> Use cells to hold the criteria** :
> >>
> >> E3 = 0
> >> F3 = 1.01
> >> G3 = Jones
> >> H3 = Draper
> >> I3 = Smith
> >>
> >> =SUMPRODUCT(--(A3:A26>E3),--(A3:A26<F3),--(B3:B26=G3),--(C3:C26=G3))
> >>
> >> ** Use cells to hold the criteria:
> >>
> >> You'd have to redo this formula since array constants can't use cell
> >> references.
> >>
> >> >=SUMPRODUCT((A3:A26>0.00)*(A3:A26<1.01)*(B3:B26={"Smith","Draper","Jones"}))
> >>
> >> =SUMPRODUCT(--(A3:A26>E3),--(A3:A26<F3),--(ISNUMBER(MATCH(B3:B26,G3:I3,0))))
> >>
> >> -- 
> >> Biff
> >> Microsoft Excel MVP
> >>
> >>
> >> "Struggling in Sheffield" 
> >> <StrugglinginSheffield@discussions.microsoft.com>
> >> wrote in message 
> >> news:21497C12-991F-4ECE-83DD-3F96D718455B@microsoft.com...
> >> > Hi,
> >> > I'm using a sumproduct formula to ascertain the number of times that a
> >> > value
> >> > between 0 and 1 occurs in column A (range A3:A26), where the adjacent 
> >> > cell
> >> > in
> >> > column B (B3:B26) contains either "Smith", "Draper" or "Jones":
> >> >
> >> > =SUMPRODUCT((A3:A26>0.00)*(A3:A26<1.01)*(B3:B26={"Smith","Draper","Jones"}))
> >> >
> >> > This formula works fine and details the number of entries where the 
> >> > value
> >> > in
> >> > column A is between 0 and 1, and the name in the adjacent column B cell 
> >> > is
> >> > Smith Draper or Jones.
> >> >
> >> > What I need to do is the same SUMPRODUCT of 0-1 entries in column A but
> >> > this
> >> > time where the names listed in column B are equal to more names listed 
> >> > in
> >> > Column C (i.e. where B7 = "Jones" and C7 = "Jones", 1 will be the added 
> >> > to
> >> > my
> >> > SUMPRODUCT total (if the value in A7 is between 0 and 1).
> >> >
> >> > Keep skirting around the edges of this one without being quite able to
> >> > nail
> >> > it.
> >> > Any pointers gratefully received.
> >> > Cheers,
> >> > Steve.
> >>
> >>
> >> 
> 
> 
> 
0
3/5/2009 12:28:12 PM
You're welcome. Thanks for the feedback!

-- 
Biff
Microsoft Excel MVP


"Struggling in Sheffield" <StrugglinginSheffield@discussions.microsoft.com> 
wrote in message news:997BBC0C-FAC1-4022-87B8-B532CD7B2490@microsoft.com...
> Hi Biff,
> Works a treat, many thanks.
>
> "T. Valko" wrote:
>
>> I misunderstood your requirement.
>>
>> I thought you just wanted to match Jones and Jones.
>>
>> Try this:
>>
>> E3 = 0
>> F3 = 1.01
>> G3 = Jones
>> H3 = Draper
>> I3 = Smith
>>
>> =SUMPRODUCT(--(A3:A26>E3),--(A3:A26<F3),--(B3:B26=C3:C26),--(ISNUMBER(MATCH(B3:B26,G3:I3,0))))
>>
>> -- 
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Struggling in Sheffield" 
>> <StrugglinginSheffield@discussions.microsoft.com>
>> wrote in message 
>> news:7294072D-82C0-4922-BBE7-A18BC7A651B3@microsoft.com...
>> > Hi,
>> > I get the gist of what you're saying but can't see how B3:B26 is being
>> > compared to C3:C26 for the three names?
>> >
>> >            A                B                  C
>> > 3        0.12           Smith            Jones
>> > 4        2.03           Draper          Draper
>> > 5        0.65           Jones            Jones
>> > 6        0.81           Smith            Smith
>> > 7        0.33           Jones           Draper
>> > 8        1.52           Smith           Smith
>> > 9        0.74           Jones           Jones
>> > 10      3.02           Draper          Jones
>> >
>> > For the table illustrated I need my formula to return the value of "3".
>> >
>> > A5 has a value less than 1 (0.65), whilst B5 & C5 have the same name
>> > "Jones"
>> > (count 1)
>> > A6 has a value less than 1 (0.81), whilst B6 & C6 have the same name
>> > "Smith"
>> > (count 2)
>> > A9 has a value less than 1 (0.74), whilst B9 & C9 have the same name
>> > "Jones"
>> > (count 3)
>> >
>> > Whilst names match on other rows or the value in column A is less than 
>> > 1,
>> > only three times do all these criteria line up and this is what I need 
>> > to
>> > count.
>> > Cheers,
>> > Steve.
>> >
>> >
>> >
>> > "T. Valko" wrote:
>> >
>> >> Use cells to hold the criteria** :
>> >>
>> >> E3 = 0
>> >> F3 = 1.01
>> >> G3 = Jones
>> >> H3 = Draper
>> >> I3 = Smith
>> >>
>> >> =SUMPRODUCT(--(A3:A26>E3),--(A3:A26<F3),--(B3:B26=G3),--(C3:C26=G3))
>> >>
>> >> ** Use cells to hold the criteria:
>> >>
>> >> You'd have to redo this formula since array constants can't use cell
>> >> references.
>> >>
>> >> >=SUMPRODUCT((A3:A26>0.00)*(A3:A26<1.01)*(B3:B26={"Smith","Draper","Jones"}))
>> >>
>> >> =SUMPRODUCT(--(A3:A26>E3),--(A3:A26<F3),--(ISNUMBER(MATCH(B3:B26,G3:I3,0))))
>> >>
>> >> -- 
>> >> Biff
>> >> Microsoft Excel MVP
>> >>
>> >>
>> >> "Struggling in Sheffield"
>> >> <StrugglinginSheffield@discussions.microsoft.com>
>> >> wrote in message
>> >> news:21497C12-991F-4ECE-83DD-3F96D718455B@microsoft.com...
>> >> > Hi,
>> >> > I'm using a sumproduct formula to ascertain the number of times that 
>> >> > a
>> >> > value
>> >> > between 0 and 1 occurs in column A (range A3:A26), where the 
>> >> > adjacent
>> >> > cell
>> >> > in
>> >> > column B (B3:B26) contains either "Smith", "Draper" or "Jones":
>> >> >
>> >> > =SUMPRODUCT((A3:A26>0.00)*(A3:A26<1.01)*(B3:B26={"Smith","Draper","Jones"}))
>> >> >
>> >> > This formula works fine and details the number of entries where the
>> >> > value
>> >> > in
>> >> > column A is between 0 and 1, and the name in the adjacent column B 
>> >> > cell
>> >> > is
>> >> > Smith Draper or Jones.
>> >> >
>> >> > What I need to do is the same SUMPRODUCT of 0-1 entries in column A 
>> >> > but
>> >> > this
>> >> > time where the names listed in column B are equal to more names 
>> >> > listed
>> >> > in
>> >> > Column C (i.e. where B7 = "Jones" and C7 = "Jones", 1 will be the 
>> >> > added
>> >> > to
>> >> > my
>> >> > SUMPRODUCT total (if the value in A7 is between 0 and 1).
>> >> >
>> >> > Keep skirting around the edges of this one without being quite able 
>> >> > to
>> >> > nail
>> >> > it.
>> >> > Any pointers gratefully received.
>> >> > Cheers,
>> >> > Steve.
>> >>
>> >>
>> >>
>>
>>
>> 


0
biffinpitt (3172)
3/5/2009 2:36:32 PM
Reply:

Similar Artilces:

Public Folder Auto-reply #2
Hi Is there any way that I can enable an auto-reply for emails that are going to a Public Folder that is mail enabled. The emails will not only becoming from inside th company but externally as well. Thanx for the help Sulaiman yes, you can...on the Administration tab, click Folder Assistant...but be very cautious with this...it's very easy to get into a "mail loop" condition that could conceivably crash the store... -- Susan Conkey [MVP] "Sulaiman" <Sulaiman@discussions.microsoft.com> wrote in message news:9E3D127F-2CBE-40D3-9AC3-333F3D25B465@microsoft....

clustered stack column charts #2
I am trying to create a chart with three stacks in two columns. I tried using the method described by Bernard Liengmen's but it doesn't allow me to add a secondary axis for the last (sixth) data series. I don't get it??? How does one series in a stacked cluster use the secondary axis? - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Lou T wrote: > I am trying to create a chart with three stacks in two columns. I tried > using the method described by Bernard Liengmen's but it ...

Store.exe crashing on 2 Exchange 2003 servers
We have 2 Exchange 2003 SP1 servers (Win 2K SP4) which have developed a problem with the store crashing every half hour or so. System event log: The Microsoft Exchange Information Store service terminated unexpectedly. It has done this 2 time(s). The following corrective action will be taken in 0 milliseconds: No action. Application event log Faulting application store.exe, version 6.5.7226.3, stamp 407db771, faulting module storevs2.dll, version 7.1.367.0, stamp 40746ce5, debug? 0, fault address 0x00002238. I think it is interesting that both servers are crashing, but not at exact...

What happened? #2
Does anybody know why many of the discussion topics have been deleted from this forum? Not specific to this forum, but the MS newsgroups have a "retention period". Anything too old is automatically purged (which sometimes leaves answers with no questions. IIRC this period is 6 months. They are generally moving everything across to the web-based forums instead. On 24/06/2010 14:57, Squeaky wrote: > Does anybody know why many of the discussion topics have been deleted from > this forum? ...

How can I convert text to all lowercase?
How can I convert text to all lowercase? I know that I can use the Format menu to convert a block of text to all uppercase, but is there any way to convert it to lowercase? If not with Excel, how about Word, DOS, any other way. I'm using Excel 7.0 (Office 95). Thanks in advance! Take a look at David McRitchie's Lower() Macro (I believe it will work in XL7): http://www.mvps.org/dmcritchie/excel/proper.htm#lower In article <20031207155812.23043.00000330@mb-m27.aol.com>, hbyardsale@aol.comnetorg (HBYardSale) wrote: > How can I convert text to all lowercase? > &g...

Getting date stored as text into real date?
A database query program outputs everything as a text string. One of the fields is a date, formatted as yyyymmdd. Is there a worksheet function that will change this to an Excel-recognized date? Or a macro? The error checking doesn't flag this. Ed With your text date in A1, try this in B1: =DATE(LEFT(A1,4),MID(A1,5,2),(RIGHT(A1,2)) Hope this helps. Pete On Dec 17, 1:12 pm, Ed from AZ <prof_ofw...@yahoo.com> wrote: > A database query program outputs everything as a text string. One of > the fields is a date, formatted as yyyymmdd. Is there a worksheet > function t...

x values and plotting points
I need to plot points and draw a straight line to calculate the slope. I will use this same method to present a trend for data points over time ( three years per compound)! Help! I have Excel 97. Thanks! annette christian wrote: > I need to plot points and draw a straight line to > calculate the slope. I will use this same method to > present a trend for data points over time ( three years > per compound)! > > Help! I have Excel 97. > > Thanks! You should be able to chart your data points using the Chart Wizard. See http://www.geocities.com/jonpeltier/Excel/...

IS there a way to compare 2 excel files
I like to be able to compare 2 excel files to see how different they are.Is there such a thing? Hi Now imagine how totally different two excel files can possibly be. The report would say ... ? But if you have two pretty similar sheets, try Myrna Larson and Bill Manville's "Compare", downloadable from http://www.cpearson.com/excel/download.htm HTH. Best wishes Harald "PeterM" <pmaston@comcast.net> skrev i melding news:%23BAzyBkOFHA.3940@TK2MSFTNGP12.phx.gbl... > I like to be able to compare 2 excel files to see how different they are.Is > there such a ...

Creating a view with concatenated columns
I have a table with about 20 columns and I am trying to create a view of it that includes about 10 columns. The problem is that in the process, I am trying to concatenate some fields into one field and I am unable to get any thing working. Below is one example of my attempts to tackle this. Any ideas will be highly appreciated. // The following should be considered as pseudo-SQL CREATE function [dbo].[fn_myview] (@CardNo varchar(11)) returns Table as BEGIN declare @FullName as nvarchar(128) (SELECT FirstName, FatherName, GFatherName, FamilyName, Birthdate, Birthplace...

Spacing #2
Hi Trim removes spacing in the beginning and end of word. How can i delete a spacing betwee two words (name) ex "De Wit" > i want "dewit" THX Luc You can use: =substitute(a1," ","") to remove all the spaces (leading/trailing/embedded) or =lower(substitute(a1," ","")) if you really wanted lower case. Luc Vandenhoeck wrote: > > Hi > Trim removes spacing in the beginning and end of word. > How can i delete a spacing betwee two words (name) ex "De Wit" > i want > "dewit" > THX Luc -- ...

Rich Text control formatted as bold??
I have a control field on my form that is setup as textformat = rich text. In the memo field on the form I need specific parts of the text to show up as bold. Upon form load I am populating the field with string data such as: Me.MyTextBox = "This is a test string generation." I need to set bold only one or two words of this string. The way I understood it was that if I was using Rich Text format it would convert the formatting to HTML style. But I don't see and havent found examples HTML formatting like [b] [/b] working in VBA. What is the correct way I can do t...

special orders #2
What is the best way to handle special orders... would it be via work orders? We would like to have two "special order" items that require 6 or so input criteria, i.e. if a cashier selects the item, they would have to input various selections such as color, finish, cloth type, etc. Any ideas on how to handle this? Thanks, Dallas. We use Layaway for special orders and I don't believe there is any fundamental difference between Layaway and Work Order other than the name. For the past year we've been using SO Tracker from Digital Retail Solutions. You can find it at: http...

Automatically inserting text into a cell
I have a formula in a cell that reads like this; =IF(A12="","",WORKDAY,(A12,5) In column M I am asking if a report is due Y/N. Can I add to the above formula to automatically insert "N/A" into N12 if there is a "N" in column M? One way: N12: =IF(M12="N","N/A",IF(A12="","",WORKDAY(A12, 5))) In article <39A51AAF-D44A-4356-B20A-71C8AA661338@microsoft.com>, Roy <Roy@discussions.microsoft.com> wrote: > I have a formula in a cell that reads like this; > =IF(A12="","",W...

Service Call SQL Object creation error #2
Hi, I am trying to configure Service Call Management, and when I execute the step to set up permissions for the GP users who can perform service call escalations (i.e Tools \ Setup \ Project \ Service Setup \ Service button \ SQL Objects button - Set permissions for Sql Objects for Escalation, then I get the following error: 'An error occurred executing add group statements'. When I click OK the next error is: 'The role FSSQLJOB does not exist in the current database.' I am on GP10 and am doing this in the Fabrikam database. Thanks Neil You will want to run the follow...

Combining 2 Text Strings in Body of E-Mail Q
I am trying to create a string of text to place in the message body of an e-mail. Using Ron De Bruins code I've run in to the "Too many line continuations". I've a requirement for 31 lines, but it hits this error on line 24. How can I combine 2 text strings to appear in the message body of the reports. My code with only the first stringbody is:- Sub Mail_New_Version() Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim TempFilePath As String Dim TempFileName As String Dim OutApp As O...

Text Wrapping and received Emails
I have received an email that has lines in it longer than the page width and dont wrap. I have tried help and searching here but cant find a way to have outlook automatically word wrap incoming messages to fit the text to the window any ideas please? Cheers JD Are you sure this is a Plain Text e-mail? These should be wrapped automatically for Outlook 2002 and 2003. HTML messages will be displayed in the way got formatted. -- Roady [MVP] www.sparnaaij.net Microsoft Office and Microsoft Office related News Also Outlook FAQ, How To's, Downloads and more... Tip of the month: -Tips for...

Display lists in an arbitrary number of columns
I have written and posted the following articles which will prove useful = to some: Display Lists in Columns Horizontally Using One Cell per Column http://www.braintrove.com/default.aspx?p=3D3&s=3D1&i=3D34 Display Lists in Columns Horizontally Using Individual Cells http://www.braintrove.com/default.aspx?p=3D3&s=3D1&i=3D33 Display Lists in Columns Vertically Using One Cells per Column http://www.braintrove.com/default.aspx?p=3D3&s=3D1&i=3D32 Display Lists in Columns Vertically Using Individual Cells http://www.braintrove.com/default.aspx?p=3D3&s=3D1&i=3D31 ...

Script to count duplicate lines in a text file
We have a requirement to count the number of duplicate entries in a text file and then list the line and the number of duplicates. For example, we have a file call test.txt that has the following entries: A1 A1 B1 B2 B2 B2 B2 This would return the following: A1,2 B1,1 B2,4 Any assistance would be greatly appreciated Gadgetman schrieb: > We have a requirement to count the number of duplicate entries in a text file > and then list the line and the number of duplicates. For example, we have a > file call test.txt that has the following entries: > A1 > A1 ...

COUNTIF with adjacent columns
I have a spreadsheet which has a list of tasks in one column and directly next to it whether that task is complete/not complete/ rescheduled. For example in cell B2 the task is CL and in column C2 the task is complete: B C 2 CL Complete I would like to add a tally box at the bottom for all CL tasks which are complete, then another for not complete and another for rescheduled. At the moment, I can only get it to COUNTIF on either the CL or the complete value but not both together. Any help greatly appreciated! Try this: A B C ...

Rules do not run #2
I have erase all my rules and set them up one at a time as a new message arrives. Next time that rule is supposed to run it does not. Using "Run Rules Now" does work, why are my rule not running automatically when new messages arrive. I am using Outlook 2003. Andrew M. what type of email account do you have? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Ou...

Migration From 1.2 to 3.0
Dear all, We want to do a new installation of CRM3.0 on a new server. How to migrate data from 1.2 to 3.0 ? (1)Somebody suggested to use data migration tools. I don't like it because I have experienced before (when I migrate Goldmine to CRM 1.2). It is very headache and time-consuming. (2)Somebody suggested to do a new installation for CRM1.2 and upgrade to CRM3.0. Does it mean I can copy the database of 1.2 to a new server and install CRM1.2 ? I would actually first upgrade your existing system. Then look to migrating to the new hardware. With v3, the DB is a lot easier to mov...

How to split numbers and decimal in 2 columns
One column for the dollars and the other for the cents? If so, use the Text to Columns, Use Delimited as the split option (Step 1) and select Other, type in the decimal point in the blank to the right of the Other option in step 2. This is good, but how do i keep it in the colums is i introduce new data and how do i sum it up (the colums) to get a final number with decimals in there corresponding colums? Dollars in in B, cents in C, Data shown Dollars Cents Cu Sum 7 56 7.56 3 44 11.00 2 24 13.24 45 23 58.47 C2: =SUM($B$2:B2)+SUM($C$2:C2)/100 and copy down Regards Peter A "...

outlook web access
Hello, Thanks in advance for your help. I am using outlook web access. When I attempt to reply to a message, the text box just has a box with a red "X" in the top left corner. The same thing happens when I open a new message. Any ideas? Amy "Amy" <Amy@discussions.microsoft.com> wrote in message news:FEA46A8B-3116-4850-BE0F-AB2B9590F20A@microsoft.com... > Thanks in advance for your help. I am using outlook web access. When I > attempt to reply to a message, the text box just has a box with a red "X" > in > the top left corner. The same t...

Reporting #2
I receive email for two domains into my Exchange Server. I'd like to know how much email is received total and for each of these domains individually. Are there any built in tools for this type of reporting? Thanks, Andy Hi No built in tools to do this in Exchange, you would need to use a product lik MOM or a 3 rd pary product like: Mail Access Monitor http://www.internetaccessmonitor.com/eng/products/mam/ Admin Report Kit for Exchange Server http://www.vyapin.com/products/enterprisenetworktools/arkxchange.htm Exchange Monitor http://www.fortissoftware.com/ Regards -- Ben Ho...

Update Rollup 2 and Terminal Server
I've been using the terminstall.cmd script that MS put together for installing CRM 3 for Outlook on Terminal Server. How should I install the UR 2 on Terminal Server? I can't find any direction on this so any hints would be greatly appreciated! Thanks. Having the same issue myself. Can anyone point us to some suggestions? -Eric "NW" wrote: > I've been using the terminstall.cmd script that MS put together for > installing CRM 3 for Outlook on Terminal Server. > > How should I install the UR 2 on Terminal Server? > > I can't find any dir...