If/Count IF
 

I am using the formula:
=if(countif($h3:$h$4143,h2)=0,"single","")
because I have a list of 4000+ clients and I need to weed out all the
codes that are single. I am getting a "single" 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 "single" to where it truely is one
singular clients with the word "single." I tried it the opposite way
where I would output "duplicate" but that didn"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: http://www.excelforum.com/member.php...o&userid=24151
View this thread: http://www.excelforum.com/showthread...hreadid=493067

If/Count IF
 

Try

=if(countif($h$3:$h$4143,h2)=0,"single","")


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Delaina" wrote in
message ...

I am using the formula:
=if(countif($h3:$h$4143,h2)=0,"single","")
because I have a list of 4000+ clients and I need to weed out all the
codes that are single. I am getting a "single" 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 "single" to where it truely is one
singular clients with the word "single." I tried it the opposite way
where I would output "duplicate" but that didn"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:
http://www.excelforum.com/member.php...o&userid=24151
View this thread: http://www.excelforum.com/showthread...hreadid=493067

If/Count IF
 

"Delaina" wrote in
message ...

I am using the formula:
=if(countif($h3:$h$4143,h2)=0,"single","")
because I have a list of 4000+ clients and I need to weed out all the
codes that are single.

I'd use

=IF(COUNTIF(Clients,H2)1,"","single")

and use "Clients" 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.

If/Count IF
 

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: http://www.excelforum.com/member.php...o&userid=24151
View this thread: http://www.excelforum.com/showthread...hreadid=493067

If/Count IF
 

"Danny@Kendal" wrote in message
...
"Delaina" wrote in
message ...

I am using the formula:
=if(countif($h3:$h$4143,h2)=0,"single","")
because I have a list of 4000+ clients and I need to weed out all the
codes that are single.

I'd use

=IF(COUNTIF(Clients,H2)1,"","single")

and use "Clients" 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.

Not quite Mr D! Just realised "H2" wont move when Clients is pointed
somewhere else. Doh!

If/Count IF
 

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

=if(countif($h$3:$h$4243,h2)=1,"single","")

Thanks!


--
Delaina
------------------------------------------------------------------------
Delaina's Profile: http://www.excelforum.com/member.php...o&userid=24151
View this thread: http://www.excelforum.com/showthread...hreadid=493067

If/Count IF
 

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
ETC


John 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: http://www.excelforum.com/member.php...o&userid=24735
View this thread: http://www.excelforum.com/showthread...hreadid=493067

If/Count IF
 

Hi

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

Regards

Roger Govier


Ltat42a wrote:
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
ETC


John 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.

If/Count IF
 

Roger Govier Wrote:
Hi

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

Regards

Roger Govier


Ltat42a wrote:
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
ETC


John 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.




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: http://www.excelforum.com/member.php...o&userid=24735
View this thread: http://www.excelforum.com/showthread...hreadid=493067

If/Count IF
 

Hi

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)="John"))

Regards

Roger Govier


Ltat42a wrote:
Roger Govier Wrote:

Hi

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

Regards

Roger Govier


Ltat42a wrote:

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
ETC


John 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.





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

If/Count IF
 

Roger Govier Wrote:
Hi

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)="John"))

Regards

Roger Govier


Ltat42a wrote:
Roger Govier Wrote:

Hi

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

Regards

Roger Govier


Ltat42a wrote:

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
ETC


John 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.





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



That worked. It now returns the number of times "John" is listed in
column A,
however, it doesn't copy of the data in columns B-E on the row that
contains "John".

Is that still possible?

Thanx...JF


--
Ltat42a
------------------------------------------------------------------------
Ltat42a's Profile: http://www.excelforum.com/member.php...o&userid=24735
View this thread: http://www.excelforum.com/showthread...hreadid=493067

If/Count IF
 

Hi

In that case, i would mark the header row, DataFilterAutofilter
Select the dropdown on column A and choose Custom, =LEFT(A2,4)="John"
Copy the block of Filter rows that result.
Paste to another sheet.

Regards

Roger Govier


Ltat42a wrote:
Roger Govier Wrote:

Hi

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)="John"))

Regards

Roger Govier


Ltat42a wrote:

Roger Govier Wrote:


Hi

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

Regards

Roger Govier


Ltat42a wrote:


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
ETC


John 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.





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




That worked. It now returns the number of times "John" is listed in
column A,
however, it doesn't copy of the data in columns B-E on the row that
contains "John".

Is that still possible?

Thanx...JF