Excel Excel Vlookup Help for New User

T

Trish

Joined
Apr 14, 2008
Messages
5
Reaction score
0
Hi, if this is posted in the wrong place, could you please redirect me as i struggled to find the relevant place.

I am using excel 2003 under Microsoft Pro2000.

I have three columns of user id's in column A, B & C of excel. I need to identify the id's that are in more than 1 column. for example, if id 'abc123' is only in column A, i don't need to know about it. but if id 'abc123' is in column A AND in column B i want to know it is in column A and column B and not in column C.
I have been trying to use VLOOKUP function to do it and i'm sure it will, but i haven't cracked it yet and wondered if someone out there could help.
many thanks. trish
 
I'm not sure precisely what you want to do with the information but I think VLOOKUP will cause problems because of the #N/As which occur when there is no match. It might be easier to use COUNTIF. For example to find out how often an id in A occurs in B, add a new column D and if columns start at row 1 and end at row n, put D1=COUNTIF($B$1:$B$n,$A1). This will give you the number of times the first id in col A occurs in col B. Copy this down D and wherever you have a 1 in a row in column D then the id in the same row of the A column also occurs in the B column and so on.
 
Thanks

Hi, Thanks very much that is great. I have got it working over 2 columns by using =COUNTIF($B$1:$B$450,$A1) but what i really need is to compare 3 columns and find the duplicates betwen the 3 lists. I realise I can do the forumula 3 times changing the range and just sum the total count but is there a way I could cross reference the 3 columns in 1 go. Thank you so much for your help, this is really going to help LOADS!!!! Kind Regards Trish
 
The COUNTIF function range need not be a single column. So one possibility would be to add three extra columns D,E,F then in D1 put the formula

D1=COUNTIF($A$1:$C$450,A1) - 1

Copy this formula, select the three columns DEF and rows 1-450 and paste the formula into the block. The effect is that for each id in columns A-C you get a count, same row three columns across, of how often it is duplicated in the three columns (the -1 takes account of the one occurrence that you already know about).
 
Back
Top