help with function/formula
 

hi der

I have 2 columns containing names/codes. I want to create a 3rd column
containing the names/codes which are not present in both columns.
Or i want to create 2 columns. Column C containing the names/codes which are
not in column A and Column D containing the names/codes which are not in
column B.
Can anyone help with formula or tell which function would suite best or if
it is possible. I have no idea how to use macros so i want to stay away from
this.
Thanx

help with function/formula
 

One simple play to get there ..

Assume source data in A2:B2 down
In C2: =IF(A2="","",IF(COUNTIF(B:B,A2),"",ROW()))
In D2: =IF(B2="","",IF(COUNTIF(A:A,B2),"",ROW()))
Leave C1:D1 blank

In E2:
=IF(ROWS($1:1)COUNT(C:C),"",INDEX(A:A,SMALL(C:C,R OWS($1:1))))
Copy E2 to F2. Select C2:F2, copy down to cover the max extent of source
data. Minimize cols C & D. Col E will return items in col A not in col B,
while col F will return the converse, ie items in col B not in col A. All
results will be neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,000 Files:354 Subscribers:53
xdemechanik
---
"mac" wrote:
I have 2 columns containing names/codes. I want to create a 3rd column
containing the names/codes which are not present in both columns.
Or i want to create 2 columns. Column C containing the names/codes which are
not in column A and Column D containing the names/codes which are not in
column B.
Can anyone help with formula or tell which function would suite best or if
it is possible. I have no idea how to use macros so i want to stay away from
this.
Thanx

help with function/formula
 

Thanx mAX

"Max" wrote:

One simple play to get there ..

Assume source data in A2:B2 down
In C2: =IF(A2="","",IF(COUNTIF(B:B,A2),"",ROW()))
In D2: =IF(B2="","",IF(COUNTIF(A:A,B2),"",ROW()))
Leave C1:D1 blank

In E2:
=IF(ROWS($1:1)COUNT(C:C),"",INDEX(A:A,SMALL(C:C,R OWS($1:1))))
Copy E2 to F2. Select C2:F2, copy down to cover the max extent of source
data. Minimize cols C & D. Col E will return items in col A not in col B,
while col F will return the converse, ie items in col B not in col A. All
results will be neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,000 Files:354 Subscribers:53
xdemechanik
---
"mac" wrote:
I have 2 columns containing names/codes. I want to create a 3rd column
containing the names/codes which are not present in both columns.
Or i want to create 2 columns. Column C containing the names/codes which are
not in column A and Column D containing the names/codes which are not in
column B.
Can anyone help with formula or tell which function would suite best or if
it is possible. I have no idea how to use macros so i want to stay away from
this.
Thanx

help with function/formula
 

Welcome, Mac
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,000 Files:354 Subscribers:53
xdemechanik
---
"mac" wrote in message
...
Thanx Max