Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Compare columns and get a percentage
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Compare columns and get a percentage
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 --- |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Compare columns and get a percentage
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 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Compare columns and get a percentage
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 |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Compare columns and get a percentage
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 |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Compare columns and get a percentage
"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 --- |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Compare columns and get a percentage
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 |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Compare columns and get a percentage
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 |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Compare columns and get a percentage
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|