If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
2 Columns - Show extra items in Column B
I have 2 Columns A & B
Column B contains all of the data in Column A and then some. I need a function that will show all of the extra items in Column B. |
#2
|
|||
|
|||
Put this formula in C2 and copy down the length of column B's entries
"orbojeff" wrote: I have 2 Columns A & B Column B contains all of the data in Column A and then some. I need a function that will show all of the extra items in Column B. |
#3
|
|||
|
|||
Try...
C1, copied down: =INDEX($B$1:$B$100,SMALL(IF(COUNTIF($A$1:$A$50,$B$ 1:$B$100)=0,ROW($B$1:$B $100)-ROW($B$1)+1),ROWS($C$1:C1))) To include an error trap, try... =IF(ROWS($C$1:C1)=SUM(--(COUNTIF($A$1:$A$50,$B$1:$B$100)=0)),INDEX($B$1: $B$100,SMALL(IF(COUNTIF($A$1:$A$50,$B$1:$B$100)=0, ROW($B$1:$B$100)-ROW($B $1)+1),ROWS($C$1:C1))),"") Both formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Adjust the ranges accordingly. Hope this helps! In article . com, "orbojeff" wrote: I have 2 Columns A & B Column B contains all of the data in Column A and then some. I need a function that will show all of the extra items in Column B. |
#4
|
|||
|
|||
Oops...
Put this formula in C2 and copy it down the length of column B's entries =IF(COUNTIF(A:A,B2)=0,"Unique","") "orbojeff" wrote: I have 2 Columns A & B Column B contains all of the data in Column A and then some. I need a function that will show all of the extra items in Column B. |
#5
|
|||
|
|||
Hi,
You can use the following array formula (Ctrl+Shift+Enter) =if(or(exact(A3,$B$3:$B$80)),"",A3) Regards, Ashish Mathur "orbojeff" wrote: I have 2 Columns A & B Column B contains all of the data in Column A and then some. I need a function that will show all of the extra items in Column B. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Table Design | A. Williams | Database Design | 3 | April 29th, 2005 07:02 PM |
Calendar Question | Josh | General Discussion | 7 | March 28th, 2005 11:19 PM |
add a column to a pivot table that would show the difference between 2 other Columns | [email protected] | Worksheet Functions | 7 | January 30th, 2005 05:21 PM |
Using Validation to force entry into cells? | Mark | General Discussion | 16 | October 27th, 2004 09:23 PM |
creating matching data in different columns | Tig | General Discussion | 6 | August 13th, 2004 04:26 AM |