Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with OR function in IF function formula | Excel Worksheet Functions | |||
Function or formula | Excel Worksheet Functions | |||
A formula/function to return a formula/function | Excel Worksheet Functions | |||
Function or Formula | Excel Worksheet Functions | |||
function/formula help | Excel Worksheet Functions |