Is this possible??

[quark]

Hi,

I was wondering if this is possible, I have a 65000 row list of pcnames and software status Red amber green statuses and I would like to create a formula that checks all the matching names in col A and if all the corrosponding values in col B equal green it would the vaule of col A to another column.

So the results from the sample below would be pcname1, pcname3

col A col B
pcname1 green
pcname1 green
pcname1 green
pcname2 green
pcname2 red
pcname2 amber
pcname3 green
pcname3 green
pcname3 green

Thanks in advance for any help or ideas you may have

Cheers

 

[Robert Mika]

If this is one time solution
Put this formula into C2
=IF(COUNTIF($A$2:$A$20,A2)=SUMPRODUCT(--($A$2:$A$20=A2),--($B$2:$B$20="green")),1,0)

copy down,
Filter by column C
Copy Column A to diffrent colum.
Remove duplicates

 

[Peter_SSs]

What version of Excel are you using?

These instructions for Excel 2007+

1. Leave C1 blank and put the formula in C2.
2. Data ribbon|Sort & Filter group|Advanced|Copy to another location|List range: A1:A65000|Criteria range: C1:C2|Copy to: D1|Unique records only|OK

Excel Workbook
	A	B	C	D
1	pc Name	Status		pc Name
2	pcname1	green	TRUE	pcname1
3	pcname1	green		pcname3
4	pcname1	green
5	pcname2	green
6	pcname2	red
7	pcname2	amber
8	pcname3	green
9	pcname3	green
10	pcname3	green
11
Advanced Filter

If you have Excel 2003-, change the C2 formula to
=SUMPRODUCT(--(A$2:A$65000=A2),--(B$2:B$65000<>"green"))=0
and look for the Advanced Filter dialog under Data|Filter|ADvanced Filter...

 

[quark]

Thanks a million both Robert and Peter, both options work perfectly.

Thanks again, having this sorted means i can have a worry free weekend!!

Cheers

 

[Peter_SSs]

I have a 65000 row list of pcnames and software status

On second thoughts, if the above is true, then I question the 'perfectly' part of the response below.

both options work perfectly.

COUNTIF and SUMPRODUCT are resource-hungry functions and with that many rows of data the re-calculation time is pretty slow! If a macro solution is acceptable, try this (in a copy of your workbook).

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> pcNames()<br>    <SPAN style="color:#00007F">Dim</SPAN> Data, Result<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> pcN <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> bGreen <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> Stat <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "green"<br>    <br>    LR = Range("A" & Rows.Count).End(xlUp).Row + 1<br>    Data = Range("A1:B" & LR).Value<br>    <SPAN style="color:#00007F">ReDim</SPAN> Result(1 <SPAN style="color:#00007F">To</SPAN> LR, 1 <SPAN style="color:#00007F">To</SPAN> 1)<br>    Result(1, 1) = Data(1, 1)<br>    j = 1<br>    <SPAN style="color:#00007F">For</SPAN> i = 2 <SPAN style="color:#00007F">To</SPAN> LR<br>        <SPAN style="color:#00007F">If</SPAN> Data(i, 1) = pcN <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> bGreen <SPAN style="color:#00007F">Then</SPAN><br>                bGreen = (Data(i, 2) = Stat)<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Else</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> bGreen <SPAN style="color:#00007F">Then</SPAN><br>                j = j + 1<br>                Result(j, 1) = pcN<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            pcN = Data(i, 1)<br>            bGreen = (Data(i, 2) = Stat)<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> i<br>    Range("C1:C" & LR).Value = Result<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[quark]

Hi Peter,

I wasn't aware that both COUNTIF and SUMPRODUCT are resource-hungry functions, when I ran it on my spreadsheet it did take a little while, but it wasn't to bad the machine I'm using is pretty beefy.

I've tried the code you supplied for the macro and it worked great too, much quicker. As I'm going to be sharing this spreadsheet with other users, I'll use your marco, just in case they are running slower clients.

Thanks again

Cheers

 

[Peter_SSs]

I forgot to state that for my macro solution, the data needs to be sorted by pcName. Your sample data appeared that way so I made the assumption that your whole real data was also. If that is not the case, or you wouldn't be sure, then the code could be tweaked to accommodate that. Post back if you did want more help in that regard.