Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding unique data between 2 columns
I have a spread sheet as such:
column 1 column 2 a a b b c e d What I need to do is compare the 2 columns and figure out which entries are unique to both columns and which are only in column1 or column 2. The data will be alphanumeric. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding unique data between 2 columns
One visual way of achieving this is to use conditional formatting to
highlight which entries in column 1 also appear in column 2 (and vice versa), so that the non-highlighted cells are obviously unique in their respective column. Let's assume that your list in column A extends from A1 to A200, and in B from B1 to B150. Highlight the data in column A, then select Format | Conditional Formatting ... In the panels presented to you, select "Cell Value Is" and "equal to" and in the third panel enter the formula: =VLOOKUP(A1,$B$1:$B$150,1,0) Then click on the Format button and select as appropriate (eg Patterns {i.e. background colour} then select yellow). Then click OK. This will give a yellow background to any value in column A which is also present in column B. Then highlight the data in column B, and select Format | Conditional Formatting ... again. This time the formula needs to be: =VLOOKUP(B1,$A$1:$A$200,1,0) and choose the same yellow background as before, then click OK. Hope this helps. Pete |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding unique data between 2 columns
Use conditional formatting with formula of
=COUNTIF($B:$B,A1)0 and =COUNTIF($A:$A,B1)0 in column A and B respectively -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Joshua" wrote in message ... I have a spread sheet as such: column 1 column 2 a a b b c e d What I need to do is compare the 2 columns and figure out which entries are unique to both columns and which are only in column1 or column 2. The data will be alphanumeric. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding unique data between 2 columns
You can use conditional formating
Select the cells in column A you want to apply the format to Click Format-Conditional Formatting Select "formula is" and enter one of these formulas =COUNTIF($B:$B,A1)=0 =COUNTIF($B:$B,A1)=1 =COUNTIF($B:$B,A1)1 Select an obvious formatting like a yellow background color. Click on Okay Do the same for column B except use these formulas =COUNTIF($A:$A,B1)=0 =COUNTIF($A:$A,B1)=1 =COUNTIF($A:$A,B1)1 The first formula returns true if the cell value can not be found in the list The second formula returns true if the cell value can be found once in the list The third formula returns true if the cell value can be found multiple times in the list You can have up to three conditional formats, so you can apply different colors for each case. You can also insert similar formulas in helper columns =COUNTIF(B:B,A1) =COUNTIF(A:A,B1) this will return the number of times each cell is in the list. Example: a a 1 1 b b 1 1 c e 1 0 d c 0 1 "Joshua" wrote: I have a spread sheet as such: column 1 column 2 a a b b c e d What I need to do is compare the 2 columns and figure out which entries are unique to both columns and which are only in column1 or column 2. The data will be alphanumeric. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding unique data between 2 columns
One way, via non-array formulas ..
A sample construct is available at: http://cjoint.com/?ccdO3RQFok Finding unique data between 2 cols_Joshua_wks.xls Source data in cols A and B, from row1 down as posted In D1: =IF(ISERROR(SMALL(E:E,ROW(A1))),"", INDEX(A:A,MATCH(SMALL(E:E,ROW(A1)),E:E,0))) In E1: =IF(A1="","",IF(ISNUMBER(MATCH(A1,B:B,0)),ROW(),"" )) In F1: =IF(ISERROR(SMALL(G:G,ROW(A1))),"", INDEX(A:A,MATCH(SMALL(G:G,ROW(A1)),G:G,0))) In G1: =IF(A1="","",IF(ISNUMBER(MATCH(A1,B:B,0)),"",ROW() )) Select D1:G1, fill down until the last row of data in col A Col D returns items common to both cols A and B Col F returns items in col A not found in col B (Results will be neatly bunched at the top) In H1: =IF(ISERROR(SMALL(I:I,ROW(A1))),"", INDEX(B:B,MATCH(SMALL(I:I,ROW(A1)),I:I,0))) In I1: =IF(B1="","",IF(ISNUMBER(MATCH(B1,A:A,0)),"",ROW() )) Select H1:I1, fill down until the last row of data in col B Col H returns items in col B not found in col A (Results will be neatly bunched at the top) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Joshua" wrote in message ... I have a spread sheet as such: column 1 column 2 a a b b c e d What I need to do is compare the 2 columns and figure out which entries are unique to both columns and which are only in column1 or column 2. The data will be alphanumeric. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can you sum the last 5 columns of data from a range of data | New Users to Excel | |||
Data in narrow columns truncated when saving as DBF | Excel Discussion (Misc queries) | |||
Sort pages? | Excel Discussion (Misc queries) | |||
Finding common data in multiple columns and rows in Excel | Excel Worksheet Functions | |||
How do i copy columns of data in notepad into microsoft excel? | Excel Discussion (Misc queries) |