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 knowers 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

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

Clarification:

In a new sheet: Summary,

A2:B2 would be the input cells for the Census & Block Group Nos
In the sample set-up, A2:B2 houses : 2, 2
--
Max
Singapore
http://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, CABGx3


"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

I thank you Sir - A most elegant solution"Max" wrote:

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

You're welcome !
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Kirk Pepper" wrote in message
...
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?

"Max" wrote:

You're welcome !
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Kirk Pepper" wrote in message
...
I thank you Sir - A most elegant solution

Disregard last - misspelled the reference works fine now
Thanks

"Max" wrote:

You're welcome !
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Kirk Pepper" wrote in message
...
I thank you Sir - A most elegant solution

"Kirk Pepper" wrote
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?

I'm not sure what happened over there <g
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:
http://www.flypicture.com/
http://cjoint.com/index.php
http://www.savefile.com/index.php

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

--
Max
Singapore
http://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 <g)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Kirk Pepper" wrote in message
...
Disregard last - misspelled the reference works fine now
Thanks

Ok after further review €“ the result was not as expected. The numbers in the
groups columns represent text only.
I get a mixed result when I did a visual comparison.

Therefore, to clarify,

If there is a 2 in the census group and a 2 in the Block Group then that
meets the criteria for the name to be listed.

Next if there is a 2 in either the census group or Block Group with some
other number, that meets the criteria for the name to be listed.

Then any one without a 2 in either census group or Block Group would be
listed.

And a percentage given to each of these i.e.: 25% are 2-2, 25% are 2-1, 50%
are other.

I am sorry if I didnt explain myself well enough the first time, and I am
sorry I cant post the whole sheet - I got my but chewed for what little I
did put up. I like what you did it just didnt quite give me what I need.



"Max" wrote:

Aha, glad to hear you got it sorted out !
... disregard my response to your earlier post on the error
(the posts crossed in cyberspace <g)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Kirk Pepper" wrote in message
...
Disregard last - misspelled the reference works fine now
Thanks

See new sheet: Summary (2),

Revised sample file available at:
http://cjoint.com/?docV3pyW4H
CalcPercent_n_ExtractLists_Matched_n_UnMatched_V2. xls

In C2:
=SUMPRODUCT((X!E2:E10=A2)*(X!F2:F10=B2))/COUNT(X!E:E)

In D2:
=SUMPRODUCT(((X!E2:E10=A2)*(X!F2:F10<B2))+((X!E2: E10<A2)*(X!F2:F10=B2)))/C
OUNT(X!E:E)

In E2: =100%-SUM(C2:D2)

C2 gives the Matched% (Census & Blk)
D2 returns the Matched% (Census OR Blk)
E2 yields the Unmatched%

(Adapt the ranges X!E2:E10, X!F2:F10 to suit,
but note that we can't use entire col refs)

Criteria cols F to H
(for extract of names for each of the 3 categories)
-------------------------------
In F2:
=IF(OR($A$2="",$B$2=""),"",IF(AND(X!E2=$A$2,X!F2=$ B$2),ROW(),""))

In G2:
=IF(OR($A$2="",$B$2="",X!E2="",X!F2=""),"",IF(AND( ISNUMBER(X!E2),ISNUMBER(X!
F2)),IF(OR(AND(X!E2=$A$2,X!F2<$B$2),AND(X!E2<$A$ 2,X!F2=$B$2)),ROW(),"")))

In H2:
=IF(OR($A$2="",$B$2="",X!E2="",X!F2=""),"",IF(AND( F2="",G2=""),ROW(),""))

Select F2:H2, copy down as far as required
to cover the max expected extent of data in X

Then placed in C4 (as before, no change):

=IF(ISERROR(SMALL(E:E,ROW(A1))),"",
INDEX(X!$G:$G,MATCH(SMALL(E:E,ROW(A1)),E:E,0)))

Copy C4 to E4, fill down to the extent done
for the criteria cols F to H

C4 down returns the Matched Names (Census & Blk)
D4 down returns the Matched Names (Census OR Blk)
E4 down returns the Unmatched Names
(All names will be neatly bunched at the top)

Lightly tested here, the above seems to return correctly the required
results. Try it out on your actual data ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Kirk Pepper" wrote in message
...
Ok after further review - the result was not as expected. The numbers in
the
group's columns represent text only.
I get a mixed result when I did a visual comparison.

Therefore, to clarify,

If there is a 2 in the census group and a 2 in the Block Group then that
meets the criteria for the name to be listed.

Next if there is a 2 in either the census group or Block Group with some
other number, that meets the criteria for the name to be listed.

Then any one without a 2 in either census group or Block Group would be
listed.

And a percentage given to each of these i.e.: 25% are 2-2, 25% are 2-1,
50%
are other.

I am sorry if I didn't explain myself well enough the first time, and I am
sorry I can't post the whole sheet - I got my but chewed for what little I
did put up. I like what you did it just didn't quite give me what I need.

.. sorry I can't post the whole sheet -
I got my butt chewed for what little I did put up

Sorry to hear that .. A little late here, perhaps, but remember that we
could/should always sanitize data (especially names) in posts, via using
representations eg: ABC, DEF, etc or Name1, Name2, .. etc.

But it's always important to describe specific sheet set-ups with enough
sample data, the expected results, etc so that responders have a clear view
of what you're trying to accomplish, and proceed to tailor easily adaptable
suggestions to suit ..

Anyway, trust the revised suggestions posted earlier
will work well to serve your purposes over there ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---