software

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 groupBlock 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, DominickPerhaps a possible play ..

A sample construct is available at:
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 C11 : 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=quot;quot;,$B$2=quot;quot;),quot;quot;,IF(AND(X!E2=$A$2,X!F2=$ B$2),ROW(),quot;quot;))

Put in F2:
=IF(OR($A$2=quot;quot;,$B$2=quot;quot;,X!E2=quot;quot;,X!F2=quot;quot;),quot;quot;,IF(AND( X!E2=$A$2,X!F2=$B$2),quot;quot;,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))),quot;quot;,
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
savefile.com/projects/236895
xdemechanik
---
quot;Kirk Pepperquot; gt; wrote in message
...
gt; This is the worksheet I have.
gt; What I need to do is Compare E and F -if there is a 2 in E and a 2 in F, I
gt; want the name in G copied to a chart that will show the percentage of
people
gt; in the same
gt; Census and Block groups and give their names.
gt; I also need to show the percentage that does not match and who they are.
I
gt; have tried to modify almost every function available but cannot obtain the
gt; desired result.
gt; Please help O Noble knower's of that which eludes me.
gt; Thank You
gt; Colum E Colum F Colum
G
gt; census group Block Group Name
gt;
gt; 2 2 Ashley, Jordan
gt; 2 2 Banks, Victoria
gt; 2 1 Beard, Frederick
gt; 2 2 Beard, Stephanie
gt; 2 2 Bixby, Michael
gt; 2 2 Bosco, Dominick
gt;
Clarification:

gt; In a new sheet: Summary,

A2:B2 would be the input cells for the Census amp; Block Group Nos
In the sample set-up, A2:B2 houses : 2, 2
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
My approach would be to utilize the AutoFilter feature to make the
separations of the lists and the SUBTOTAL formulas to do the math....maybe
all run by macros is I had to do it very often....

Vaya con Dios,
Chuck, CABGx3quot;Kirk Pepperquot; gt; wrote in message
...
gt; This is the worksheet I have.
gt; What I need to do is Compare E and F -if there is a 2 in E and a 2 in F, I
gt; want the name in G copied to a chart that will show the percentage of
people
gt; in the same
gt; Census and Block groups and give their names.
gt; I also need to show the percentage that does not match and who they are.
I
gt; have tried to modify almost every function available but cannot obtain the
gt; desired result.
gt; Please help O Noble knower's of that which eludes me.
gt; Thank You
gt; Colum E Colum F Colum
G
gt; census group Block Group Name
gt;
gt; 2 2 Ashley, Jordan
gt; 2 2 Banks, Victoria
gt; 2 1 Beard, Frederick
gt; 2 2 Beard, Stephanie
gt; 2 2 Bixby, Michael
gt; 2 2 Bosco, Dominick
gt;
I thank you Sir - A most elegant solutionquot;Maxquot; wrote:

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

You're welcome !
Thanks for the feedback ..
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;Kirk Pepperquot; gt; wrote in message
...
gt; I thank you Sir - A most elegant solution
One further question: the subject data takes up 148 cells per Colum and I am
getting a REF# error when trying to set up is there something special I need
for X to reference or a change in syntax?

quot;Maxquot; wrote:

gt; You're welcome !
gt; Thanks for the feedback ..
gt; --
gt; Max
gt; Singapore
gt; savefile.com/projects/236895
gt; xdemechanik
gt; ---
gt; quot;Kirk Pepperquot; gt; wrote in message
gt; ...
gt; gt; I thank you Sir - A most elegant solution
gt;
gt;
gt;

Disregard last - misspelled the reference works fine now
Thanks

quot;Maxquot; wrote:

gt; You're welcome !
gt; Thanks for the feedback ..
gt; --
gt; Max
gt; Singapore
gt; savefile.com/projects/236895
gt; xdemechanik
gt; ---
gt; quot;Kirk Pepperquot; gt; wrote in message
gt; ...
gt; gt; I thank you Sir - A most elegant solution
gt;
gt;
gt;

quot;Kirk Pepperquot; wrote
gt; One further question: the subject data takes up 148 cells per Colum
gt; and I am getting a REF# error when trying to set up
gt; is there something special I need
gt; for X to reference or a change in syntax?

I'm not sure what happened over there lt;ggt;
It's hard to tell w/o looking at your actual set-up / adaptation
why you're hitting the implementation problems

Which formulas are giving this error?
What is the actual sheetname for your source data? (I used: X)
Where is the actual source data range? In E2:G149 ??
In the sample file the posted data was assumed in E2:G7
(Labels in E1:G1 : Census, Blk, Name)

It's easier to re-name your actual source sheet similarly as: X first, then
paste and get all the suggested formulas [which reference X] working ok, and
then only change the source sheetname back to the desired name. Excel will
then auto-change the referenced sheetname in the formulas.

Perhaps you could upload a small sample copy of your file
(sanitized, if necessary), via a free filehost*
and then post the link to it in response here
(the link is generated when you upload, just copy and paste it here)

*Some free filehosts that could be used:
www.flypicture.com/
cjoint.com/index.php
www.savefile.com/index.php

For cjoint.com (it's in French), just click quot;Browsequot; button,
navigate to folder gt; select the file gt; Open, then click the button centred
in the page below (labelled quot;Creer le lien Cjointquot;) and it'll generate the
link. Then copy amp; paste the generated link as part and parcel of your
response here.

--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
Aha, glad to hear you got it sorted out !
... disregard my response to your earlier post on the error
(the posts crossed in cyberspace lt;ggt;)
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;Kirk Pepperquot; gt; wrote in message
...
gt; Disregard last - misspelled the reference works fine now
gt; Thanks