Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas for a spreadsheet
I have a spreadsheet that contains multiple columns of Data. The first
column is a SSN. The fifth column is a value from one to six representing a school. I want to see if a value in column one (SSN) appears with more than one of the values in column five (School). I've tried several different formulas and haven't gotten it. I'd appreciate any help. Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas for a spreadsheet
One way, assume SSN column is A2:A500 and the school column is E2:E500,
insert a help column in F and in F2 put =COUNTIF($A$2:$A$500,A2) copy down as long as needed All entries in the help column greater than 1 occurs more than once To make it easier you can select the whole table (help column included, then do datafilterautofilter, filter on the help column select custom and greater than 1 Now you can select the visible table and copy it to another sheet and those would be all SSN entries that occurs more than once -- Regards, Peo Sjoblom "epowen" wrote in message ... I have a spreadsheet that contains multiple columns of Data. The first column is a SSN. The fifth column is a value from one to six representing a school. I want to see if a value in column one (SSN) appears with more than one of the values in column five (School). I've tried several different formulas and haven't gotten it. I'd appreciate any help. Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas for a spreadsheet
pick an empty column (F?)
in F1 enter =countif($A$1:$A$100,A1)-sumproduct(--($A$1:$A$100=A1),--($E$1:$E$100=E1)) copy and paste down to the end of the data any value greater than 1 will indicate more thatn one school associated with an SSN "epowen" wrote: I have a spreadsheet that contains multiple columns of Data. The first column is a SSN. The fifth column is a value from one to six representing a school. I want to see if a value in column one (SSN) appears with more than one of the values in column five (School). I've tried several different formulas and haven't gotten it. I'd appreciate any help. Thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas for a spreadsheet
Didn't work for me, and Peo misread.
I'm assuming the OP has title labels in row 1 and data starts in Row 2. To find the first occurrence of the SSN (text) in Column A F2: =MATCH($A2,$A$2:$A2,) Show "school" if school (number) in current row doesn't match that of first occurrence of the SSN. G2: =IF(E2<INDEX($E$2:$E2,F2),"Not "&INDEX($E$2:$E2,F2),"--") Indication will show school does not match the school of the first occurrence for the SSN (text). -- HTH, David McRitchie, Microsoft MVP - Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "bj" wrote in message ... pick an empty column (F?) in F1 enter =countif($A$1:$A$100,A1)-sumproduct(--($A$1:$A$100=A1),--($E$1:$E$100=E1)) copy and paste down to the end of the data any value greater than 1 will indicate more thatn one school associated with an SSN "epowen" wrote: I have a spreadsheet that contains multiple columns of Data. The first column is a SSN. The fifth column is a value from one to six representing a school. I want to see if a value in column one (SSN) appears with more than one of the values in column five (School). I've tried several different formulas and haven't gotten it. I'd appreciate any help. Thanks. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas for a spreadsheet
SSN SCHOOL Formula
473119027 5 3 473119027 6 3 473119027 6 3 473119027 5 3 473119027 5 3 473119027 6 3 474921351 3 3 474921351 4 3 474921351 4 3 474921351 3 3 474921351 3 3 474921351 4 3 482011040 5 3 482011040 2 1 482011040 2 1 482011040 2 1 The data above is what I got after using the formula you suggested. I wanted to clarify that I'm interpreting the results correctly. That the first social security number is associated with schools 5 and 6 three times each; the second is associated with schools 3 and 4 three times each; and the third is associated with schools 5 and 2 three times and once respectively. Why does the last one show school 2 with one occurrence three times? "bj" wrote: pick an empty column (F?) in F1 enter =countif($A$1:$A$100,A1)-sumproduct(--($A$1:$A$100=A1),--($E$1:$E$100=E1)) copy and paste down to the end of the data any value greater than 1 will indicate more thatn one school associated with an SSN "epowen" wrote: I have a spreadsheet that contains multiple columns of Data. The first column is a SSN. The fifth column is a value from one to six representing a school. I want to see if a value in column one (SSN) appears with more than one of the values in column five (School). I've tried several different formulas and haven't gotten it. I'd appreciate any help. Thanks. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas for a spreadsheet
So when I get two dashes there is no double occurence, when I get the text
"Not 5" it means this SSN is appearing with a school other than the one it first appeared with? Am I interpreting this correctly. "David McRitchie" wrote: Didn't work for me, and Peo misread. I'm assuming the OP has title labels in row 1 and data starts in Row 2. To find the first occurrence of the SSN (text) in Column A F2: =MATCH($A2,$A$2:$A2,) Show "school" if school (number) in current row doesn't match that of first occurrence of the SSN. G2: =IF(E2<INDEX($E$2:$E2,F2),"Not "&INDEX($E$2:$E2,F2),"--") Indication will show school does not match the school of the first occurrence for the SSN (text). -- HTH, David McRitchie, Microsoft MVP - Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "bj" wrote in message ... pick an empty column (F?) in F1 enter =countif($A$1:$A$100,A1)-sumproduct(--($A$1:$A$100=A1),--($E$1:$E$100=E1)) copy and paste down to the end of the data any value greater than 1 will indicate more thatn one school associated with an SSN "epowen" wrote: I have a spreadsheet that contains multiple columns of Data. The first column is a SSN. The fifth column is a value from one to six representing a school. I want to see if a value in column one (SSN) appears with more than one of the values in column five (School). I've tried several different formulas and haven't gotten it. I'd appreciate any help. Thanks. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas for a spreadsheet
Two dashes only indicates that particular row does not disagree
with the first occurrence of the school for the SSN. There can be other conflicts for the SSN on other rows. The "Not 5" does indeed indicate that the school on that row does not match the school for the first occurrence of the SSN which was 5. "epowen" ... So when I get two dashes there is no double occurence, when I get the text "Not 5" it means this SSN is appearing with a school other than the one it first appeared with? Am I interpreting this correctly. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formulas for a spreadsheet
Create a PivotTable with column 1 has the row field and column 5 in the data
area as count. "epowen" wrote in message ... I have a spreadsheet that contains multiple columns of Data. The first column is a SSN. The fifth column is a value from one to six representing a school. I want to see if a value in column one (SSN) appears with more than one of the values in column five (School). I've tried several different formulas and haven't gotten it. I'd appreciate any help. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I clear contents from a spreadsheet but not the formulas | Excel Discussion (Misc queries) | |||
Can't see formulas in spreadsheet | Excel Worksheet Functions | |||
Why is my spreadsheet not updating changed formulas? | Excel Discussion (Misc queries) | |||
How do I copy a spreadsheet, keeping the formulas but not the #s | Excel Discussion (Misc queries) | |||
How do i copy a spreadsheet with the formulas? | Excel Discussion (Misc queries) |