| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
