UKBouldering.com

technical => computers, technology and the internet => Topic started by: butters on June 15, 2010, 03:40:16 pm

Title: Excel sorting problem
Post by: butters on June 15, 2010, 03:40:16 pm: Right I have two columns of data that I wish to find the differences in.

What I need is to compare the Column A and B and export anything that is unique in Column B to another column or spreadsheet. Anyone got a clue how to do this as it is beyond me and Google isn't turning up anything relevant.
Title: Re: Excel sorting problem
Post by: Tris on June 15, 2010, 03:42:20 pm: Just use the unique records filter:

http://blogs.techrepublic.com.com/msoffice/?p=637 (http://blogs.techrepublic.com.com/msoffice/?p=637)

EDIT: sorry, wrong function - you will need to use the vlookup (http://office.microsoft.com/en-us/excel-help/vlookup-HP005209335.aspx) feature to compare columns...
Title: Re: Excel sorting problem
Post by: butters on June 15, 2010, 04:25:14 pm: vlookup doesn't look like it will do what I need - a quick example

Column A Column B
cat dog
dog pig
horse cat
horse
goat

What I want to do is take the first value in column A and compare it to all the values in column B - if it finds a comparison then apply some sort of conditional formatting to it or something. Repeat with the second value in Column A until it reaches the end.

I suspect that it will require some sort of macro doing... :(
Title: Re: Excel sorting problem
Post by: Tris on June 15, 2010, 04:49:05 pm: Quote from: butters on June 15, 2010, 04:25:14 pm
if it finds a comparison then apply some sort of conditional formatting to it or something.

When you say this - what exactly do you want to do with the matched results? It sounds to me like you will have to do it in 2 stages if you want to do more than just identify the duplicate cells.

First do the vlookup (compare the 2 columns for duplicates), save the duplicates in another column (say C for example). Then manipulate column C however you want...

Another method - you can also find duplicates in lists (but you will need to convert the columns first):

http://www.cpearson.com/excel/ListFunctions.aspx (http://www.cpearson.com/excel/ListFunctions.aspx)
Title: Re: Excel sorting problem
Post by: butters on June 15, 2010, 05:19:58 pm: Cheers for the help on this Tris.

Ideally I want to compare Column A and Column B and find anything in Column B that is unique - anything that is not unique can be deleted or whatever.

Thought I had found the answer within the List functions but it doesn't seem to select the "correct" data unless it is in the same order in both columns which is where the problem is arising here - oh well more looking at vlookups then.
Title: Re: Excel sorting problem
Post by: Fultonius on June 15, 2010, 10:01:59 pm: What you want is a vlookup mixed in with some if(isna) shizzle.

Summat like this:

=IF(ISNA(VLOOKUP(B2,$A$2:$A$4,1,FALSE)),B2,"") :thumbsup:

Send me the sheet and I can get it working if you want.