software

I am using the formula:
=if(countif($h3:$h$4143,h2)=0,quot;singlequot;,quot;quot;)
because I have a list of 4000 clients and I need to weed out all the
codes that are single. I am getting a quot;singlequot; on the last client of
the group. For example:
CLIENT |STATUS
ABC |
ABC |
ABC | Single
123 | Single
456 | Single

I am trying to eliminate that last quot;singlequot; to where it truely is one
singular clients with the word quot;single.quot; I tried it the opposite way
where I would output quot;duplicatequot; but that didnquot;t seem to work any
better... suggestions are appreciated if you think there is a better
way to find all single clients.--
Delaina
------------------------------------------------------------------------
Delaina's Profile: www.excelforum.com/member.php...oamp;userid=24151
View this thread: www.excelforum.com/showthread...hreadid=493067Try

=if(countif($h$3:$h$4143,h2)=0,quot;singlequot;,quot;quot;)--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Delainaquot; gt; wrote in
message news
gt;
gt; I am using the formula:
gt; =if(countif($h3:$h$4143,h2)=0,quot;singlequot;,quot;quot;)
gt; because I have a list of 4000 clients and I need to weed out all the
gt; codes that are single. I am getting a quot;singlequot; on the last client of
gt; the group. For example:
gt; CLIENT |STATUS
gt; ABC |
gt; ABC |
gt; ABC | Single
gt; 123 | Single
gt; 456 | Single
gt;
gt; I am trying to eliminate that last quot;singlequot; to where it truely is one
gt; singular clients with the word quot;single.quot; I tried it the opposite way
gt; where I would output quot;duplicatequot; but that didnquot;t seem to work any
gt; better... suggestions are appreciated if you think there is a better
gt; way to find all single clients.
gt;
gt;
gt; --
gt; Delaina
gt; ------------------------------------------------------------------------
gt; Delaina's Profile:
www.excelforum.com/member.php...oamp;userid=24151
gt; View this thread: www.excelforum.com/showthread...hreadid=493067
gt;
quot;Delainaquot; gt; wrote in
message news
gt;
gt; I am using the formula:
gt; =if(countif($h3:$h$4143,h2)=0,quot;singlequot;,quot;quot;)
gt; because I have a list of 4000 clients and I need to weed out all the
gt; codes that are single.

I'd use

=IF(COUNTIF(Clients,H2)gt;1,quot;quot;,quot;singlequot;)

and use quot;Clientsquot; as a named range for $H$3:$H$4143, that way you can adjust
the range or move it to a new location without having to update all the
formulas.

I tried the suggestion and it got rid of all of the output. There was 1
single and it was the last client on the list.
Any other suggestions?
Thanks!--
Delaina
------------------------------------------------------------------------
Delaina's Profile: www.excelforum.com/member.php...oamp;userid=24151
View this thread: www.excelforum.com/showthread...hreadid=493067quot;Danny@Kendalquot; gt; wrote in message
...
gt; quot;Delainaquot; gt; wrote in
gt; message news
gt;gt;
gt;gt; I am using the formula:
gt;gt; =if(countif($h3:$h$4143,h2)=0,quot;singlequot;,quot;quot;)
gt;gt; because I have a list of 4000 clients and I need to weed out all the
gt;gt; codes that are single.
gt;
gt; I'd use
gt;
gt; =IF(COUNTIF(Clients,H2)gt;1,quot;quot;,quot;singlequot;)
gt;
gt; and use quot;Clientsquot; as a named range for $H$3:$H$4143, that way you can
gt; adjust the range or move it to a new location without having to update all
gt; the formulas.

Not quite Mr D! Just realised quot;H2quot; wont move when Clients is pointed
somewhere else. Doh!

I found out how to make it work...using both of our formulas

=if(countif($h$3:$h$4243,h2)=1,quot;singlequot;,quot;quot;)

Thanks!--
Delaina
------------------------------------------------------------------------
Delaina's Profile: www.excelforum.com/member.php...oamp;userid=24151
View this thread: www.excelforum.com/showthread...hreadid=493067
Expanding on this topic a lil bit. I'm looking to do something very
similar.
I have a list of names(about 20) in column A. In the columns to the
right I have
certain data - some text, some numbers.

What I'm looking to do is count the number of times a person is listed
(it is possible for these names to repeat during the year) in column
A,
then all of the data to the right of that person's name?

For instance, I have -
NAME DATE HOURS TEXT TEXT
John Doe 12/13/05 8 Text Text
Jane Doe 12/12/05 4 Text Text
ETC
ETCJohn will be listed several times in column A, I'm wanting to find out
how many times John is listed along with the data in the row.
The results will be on another worksheet.

Is this possible?

Thanx.--
Ltat42a
------------------------------------------------------------------------
Ltat42a's Profile: www.excelforum.com/member.php...oamp;userid=24735
View this thread: www.excelforum.com/showthread...hreadid=493067Hi

On sheet2
=SUMPRODUCT(--(LEFT(Sheet1!$A$2:$A$1000=quot;Johnquot;))
Change sheet name and range to suit

Regards

Roger GovierLtat42a wrote:
gt; Expanding on this topic a lil bit. I'm looking to do something very
gt; similar.
gt; I have a list of names(about 20) in column A. In the columns to the
gt; right I have
gt; certain data - some text, some numbers.
gt;
gt; What I'm looking to do is count the number of times a person is listed
gt; (it is possible for these names to repeat during the year) in column
gt; A,
gt; then all of the data to the right of that person's name?
gt;
gt; For instance, I have -
gt; NAME DATE HOURS TEXT TEXT
gt; John Doe 12/13/05 8 Text Text
gt; Jane Doe 12/12/05 4 Text Text
gt; ETC
gt; ETC
gt;
gt;
gt; John will be listed several times in column A, I'm wanting to find out
gt; how many times John is listed along with the data in the row.
gt; The results will be on another worksheet.
gt;
gt; Is this possible?
gt;
gt; Thanx.
gt;
gt;


Roger Govier Wrote:
gt; Hi
gt;
gt; On sheet2
gt; =SUMPRODUCT(--(LEFT(Sheet1!$A$2:$A$1000=quot;Johnquot;))
gt; Change sheet name and range to suit
gt;
gt; Regards
gt;
gt; Roger Govier
gt;
gt;
gt; Ltat42a wrote:
gt; gt; Expanding on this topic a lil bit. I'm looking to do something very
gt; gt; similar.
gt; gt; I have a list of names(about 20) in column A. In the columns to the
gt; gt; right I have
gt; gt; certain data - some text, some numbers.
gt; gt;
gt; gt; What I'm looking to do is count the number of times a person is
gt; listed
gt; gt; (it is possible for these names to repeat during the year) in column
gt; gt; A,
gt; gt; then all of the data to the right of that person's name?
gt; gt;
gt; gt; For instance, I have -
gt; gt; NAME DATE HOURS TEXT TEXT
gt; gt; John Doe 12/13/05 8 Text Text
gt; gt; Jane Doe 12/12/05 4 Text Text
gt; gt; ETC
gt; gt; ETC
gt; gt;
gt; gt;
gt; gt; John will be listed several times in column A, I'm wanting to find
gt; out
gt; gt; how many times John is listed along with the data in the row.
gt; gt; The results will be on another worksheet.
gt; gt;
gt; gt; Is this possible?
gt; gt;
gt; gt; Thanx.
gt; gt;
gt; gt;Thanx for the reply, I put the above formula on Sheet2, I'm getting the
#VALUE! error. I'm using Excel from Office XP. I've got all my data in,
can't figure out why I'm getting the error.

JF--
Ltat42a
------------------------------------------------------------------------
Ltat42a's Profile: www.excelforum.com/member.php...oamp;userid=24735
View this thread: www.excelforum.com/showthread...hreadid=493067Hi

My apologies, the formula is missing the number of characters to take, and
the closing bracket after the left statement.

=SUMPRODUCT(--(LEFT(Sheet1!$A$2:$A$1000,4)=quot;Johnquot;))

Regards

Roger GovierLtat42a wrote:
gt; Roger Govier Wrote:
gt;
gt;gt;Hi
gt;gt;
gt;gt;On sheet2
gt;gt;=SUMPRODUCT(--(LEFT(Sheet1!$A$2:$A$1000=quot;Johnquot;))
gt;gt;Change sheet name and range to suit
gt;gt;
gt;gt;Regards
gt;gt;
gt;gt;Roger Govier
gt;gt;
gt;gt;
gt;gt;Ltat42a wrote:
gt;gt;
gt;gt;gt;Expanding on this topic a lil bit. I'm looking to do something very
gt;gt;gt;similar.
gt;gt;gt;I have a list of names(about 20) in column A. In the columns to the
gt;gt;gt;right I have
gt;gt;gt;certain data - some text, some numbers.
gt;gt;gt;
gt;gt;gt;What I'm looking to do is count the number of times a person is
gt;gt;
gt;gt;listed
gt;gt;
gt;gt;gt;(it is possible for these names to repeat during the year) in column
gt;gt;gt;A,
gt;gt;gt;then all of the data to the right of that person's name?
gt;gt;gt;
gt;gt;gt;For instance, I have -
gt;gt;gt;NAME DATE HOURS TEXT TEXT
gt;gt;gt;John Doe 12/13/05 8 Text Text
gt;gt;gt;Jane Doe 12/12/05 4 Text Text
gt;gt;gt;ETC
gt;gt;gt;ETC
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;John will be listed several times in column A, I'm wanting to find
gt;gt;
gt;gt;out
gt;gt;
gt;gt;gt;how many times John is listed along with the data in the row.
gt;gt;gt;The results will be on another worksheet.
gt;gt;gt;
gt;gt;gt;Is this possible?
gt;gt;gt;
gt;gt;gt;Thanx.
gt;gt;gt;
gt;gt;gt;
gt;
gt;
gt;
gt; Thanx for the reply, I put the above formula on Sheet2, I'm getting the
gt; #VALUE! error. I'm using Excel from Office XP. I've got all my data in,
gt; can't figure out why I'm getting the error.
gt;
gt; JF
gt;
gt;