Compare columns and get a percentage

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> Disregard last - misspelled the reference works fine now
> Thanks

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.



"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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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
---