Perhaps a possible play ..
A sample construct is available at:
http://savefile.com/files/8564300
Calc percent and extract lists of matched n unmatched names.xls
Source data assumed in sheet: X,
cols E to G, data from row2 down
In a new sheet: Summary,
Put labels in C1:D1 : Matched%, Unmatched%
In C2:=SUMPRODUCT((X!E2:E10=A2)*(X!F2:F10=B2))/COUNT(X!E:E)
In D2: =100%-C2
C2 gives the Matched%, D2 yields the Unmatched%
(Adapt the ranges X!E2:E10, X!F2:F10 to suit, but we can't use entire col
refs)
Put in E2:
=IF(OR($A$2="",$B$2=""),"",IF(AND(X!E2=$A$2,X!F2=$ B$2),ROW(),""))
Put in F2:
=IF(OR($A$2="",$B$2="",X!E2="",X!F2=""),"",IF(AND( X!E2=$A$2,X!F2=$B$2),"",RO
W()))
Select E2:F2, copy down as far as required
to cover the max expected extent of data in X
Put in C4:
=IF(ISERROR(SMALL(E:E,ROW(A1))),"",
INDEX(X!$G:$G,MATCH(SMALL(E:E,ROW(A1)),E:E,0)))
Copy C4 to D4, fill down to the extent done for cols E and F
C4 down returns the matched names, D4 down returns the unmatched names,
all names will be neatly bunched at the top.
(Cols E and F are the criteria cols to extract the matched and unmatched
names)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Kirk Pepper" wrote in message
...
This is the worksheet I have.
What I need to do is Compare E and F -if there is a 2 in E and a 2 in F, I
want the name in G copied to a chart that will show the percentage of
people
in the same
Census and Block groups and give their names.
I also need to show the percentage that does not match and who they are.
I
have tried to modify almost every function available but cannot obtain the
desired result.
Please help O Noble knower's of that which eludes me.
Thank You
Colum E Colum F Colum
G
census group Block Group Name
2 2 Ashley, Jordan
2 2 Banks, Victoria
2 1 Beard, Frederick
2 2 Beard, Stephanie
2 2 Bixby, Michael
2 2 Bosco, Dominick