Formulas for a spreadsheet

[Guest]

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.

 

[Peo Sjoblom]

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 data>filter>autofilter, 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

 

[Guest]

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

 

[David McRitchie]

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).

 

[Guest]

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?

 

[Guest]

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]

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" ...

 

[a7n9]

Create a PivotTable with column 1 has the row field and column 5 in the data
area as count.