Re: Looking for Matches in SPSS and Microsoft Excel

[Hans Chen]

Dear Sir or Madam:



There are two Columns of data which are as long as 8000 rows. Is there any
way in SPSS and Microsoft Excel to find the matches between the two columns
(they might be in different rows) and pick out those without matches?



1  9

3  5

5  7

7  3

9  10

…



Hans Chen
Canada

====================To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Nancy Darling-2]

compute diffscore=var1-var2.
compute match=1.
recode diffscore (lo thru -1 =0) (1 thru hi=0) into match.
execute.

OR

compute match2=0.
if var1=var2 match2=1.
execute.

Hans Chen wrote:

> Dear Sir or Madam:
>
>
>
> There are two Columns of data which are as long as 8000 rows. Is there any
> way in SPSS and Microsoft Excel to find the matches between the two columns
> (they might be in different rows) and pick out those without matches?
>
>
>
> 1  9
>
> 3  5
>
> 5  7
>
> 7  3
>
> 9  10
>
> …
>
>
>
> Hans Chen
> Canada
>
> ===================
> To manage your subscription to SPSSX-L, send a message to
> [hidden email] (not to SPSSX-L), with no body text except the
> command. To leave the list, send the command
> SIGNOFF SPSSX-L
> For a list of commands to manage subscriptions, send the command
> INFO REFCARD
>

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Richard Ristow]

At 08:07 PM 12/11/2008, Hans Chen wrote:

>There are two Columns of data ['Variables', in SPSS terminology]
>which are as long as 8000 rows. Is there any way in SPSS to find the
>matches between the two columns (they might be in different rows)
>and pick out those without matches?
>
>1  9
>3  5
>5  7
>7  3
>9  10

So, you want all values that occur at least once in each column? That
is, 9, 3, 5, and 7 match; 1 and 10 don't? Will this do it for you?
|-----------------------------|---------------------------|
|Output Created               |12-DEC-2008 02:11:32       |
|-----------------------------|---------------------------|
[TestData]
COL1 COL2

    1    9
    3    5
    5    7
    7    3
    9   10

Number of cases read:  5    Number of cases listed:  5


DATASET COPY     Summary.
DATASET ACTIVATE Summary  WINDOW=FRONT.

VARSTOCASES
    /ID   =  RowNumb
    /MAKE    Value FROM COL1 COL2
    /INDEX = Column(2)
    /NULL  = DROP.

Variables to Cases
|-----------------------------|---------------------------|
|Output Created               |12-DEC-2008 02:11:33       |
|-----------------------------|---------------------------|
[Summary]

Generated Variables
|-------|------|
|Name   |Label |
|-------|------|
|RowNumb|<none>|
|Column |<none>|
|Value  |<none>|
|-------|------|

Processing Statistics
|-------------|-|
|Variables In |2|
|Variables Out|3|
|-------------|-|


AGGREGATE OUTFILE=*
    /BREAK = VALUE
    /WHERE = MEAN(Column).

RECODE
     WHERE
     (1        = 1)
     (2        = 2)
     (1 THRU 2 = 3)
     (MISSING  = 9)
     (ELSE     = 8).

VAR LABELS WHERE 'In which columns this value occurs'.

VAL LABELS WHERE
     1 'Col 1'
     2 'Col 2'
     3 'Both'
     8 'Miscode'
     9 'Missing'.
MISSING VAL WHERE (8,9).

STRING  WhereIs  (A8).
COMPUTE WhereIs = VALUELABEL(Where).

LIST.
List
|-----------------------------|---------------------------|
|Output Created               |12-DEC-2008 02:11:34       |
|-----------------------------|---------------------------|
Value    WHERE WhereIs

    1      1.00 Col 1
    3      3.00 Both
    5      3.00 Both
    7      3.00 Both
    9      3.00 Both
   10      2.00 Col 2

Number of cases read:  6    Number of cases listed:  6
=============================
APPENDIX: Test data, and code
(WRR: Not saved separately)
=============================
DATA LIST LIST/
   COL1  COL2
   (F3,  F3).
BEGIN DATA
     1  9
     3  5
     5  7
     7  3
     9  10
END DATA.
DATASET NAME     TestData WINDOW=FRONT.
LIST.

DATASET COPY     Summary.
DATASET ACTIVATE Summary  WINDOW=FRONT.

VARSTOCASES
    /ID   =  RowNumb
    /MAKE    Value FROM COL1 COL2
    /INDEX = Column(2)
    /NULL  = DROP.

AGGREGATE OUTFILE=*
    /BREAK = VALUE
    /WHERE = MEAN(Column).

RECODE
     WHERE
     (1        = 1)
     (2        = 2)
     (1 THRU 2 = 3)
     (MISSING  = 9)
     (ELSE     = 8).

FORMATS    WHERE (F2).
VAR LABELS WHERE 'In which columns this value occurs'.

VAL LABELS WHERE
     1 'Col 1'
     2 'Col 2'
     3 'Both'
     8 'Miscode'
     9 'Missing'.
MISSING VAL WHERE (8,9).

STRING  WhereIs  (A8).
COMPUTE WhereIs = VALUELABEL(Where).

LIST.

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Brad Kirby]

You can do this in excel quite easily. Sort column A ascending. Insert new
column B, give each cell an arbitrary value, say 1. In colum D do a vlookup
- something like =VLOOKUP(C1,$A$1:$B$5,2,FALSE) where its looking for the
value in C and returning the lookup value in your newly created lookup
table. If D = 1, then C exists in A. If D = #N/A, then C does not exist in A.

To find where A exists in C, repeat the process by creating a new lookup
table for Column C.

Needs refinement but basic methodology is there.

On Thu, 11 Dec 2008 19:07:30 -0600, Hans Chen <[hidden email]> wrote:

>Dear Sir or Madam:
>
>
>
>There are two Columns of data which are as long as 8000 rows. Is there any
>way in SPSS and Microsoft Excel to find the matches between the two columns
>(they might be in different rows) and pick out those without matches?
>
>
>
>1  9
>
>3  5
>
>5  7
>
>7  3
>
>9  10
>
>�
>
>
>
>Hans Chen
>Canada
>
>====================To manage your subscription to SPSSX-L, send a message to
>[hidden email] (not to SPSSX-L), with no body text except the
>command. To leave the list, send the command
>SIGNOFF SPSSX-L
>For a list of commands to manage subscriptions, send the command
>INFO REFCARD

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Mario Giesel]

Hello, Hans,

do you want to say that you need unique numbers in the combined set of columns 1 and 2? So in your example below only row 1 and 5 are picked out because only numbers 1 and 10 are unique? Is repetition within a column possible / allowed?

Thanks, Mario




________________________________
Von: Hans Chen <[hidden email]>
An: [hidden email]
Gesendet: Freitag, den 12. Dezember 2008, 02:07:30 Uhr
Betreff: Re: Looking for Matches in SPSS and Microsoft Excel

Dear Sir or Madam:



There are two Columns of data which are as long as 8000 rows. Is there any
way in SPSS and Microsoft Excel to find the matches between the two columns
(they might be in different rows) and pick out those without matches?



1  9

3  5

5  7

7  3

9  10

…



Hans Chen
Canada

====================To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD





====================To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD