TechArena Community Find The Value of one column in another column

#1
23-02-2011
 Member Join Date: Feb 2011 Posts: 77
Find The Value of one column in another column

Hello friend I am working on Excel sheet. I newer to excel .
I want to check what are the elements of a column that exists in another sheet are same elements to new the column. What is Excel formula I can use for this. Or is there any shortcut method for this.
example:
Column A contains 10 rows of element with heading. Now In comes a new Set of NAMES containing 6 rows.
Now i want to know which all of these 6 new rows are present previously in Column A (NAME) and which all are new in the column B (NAMES).
Whichever element is new in column B(NAMES) copy those element into column A(NAME).
#2
23-02-2011
 Member Join Date: Nov 2008 Posts: 1,185
Re: Find The Value of one column in another column

You can do it with formula of an array.
In first column you can Array enter this formula =IF(\$A\$1<>"",SUMPRODUCT(--(TEXT(\$A\$2:\$A\$30,"0")=\$A\$1),B\$2:B\$30),"") this formula looks at the name in A1 then finds the same text in column A for each instance it finds it sums the value of the corresponding adjacent cell in column B.
When you have Array entered the formula it wil look like this {=IF(\$A\$1<>"",SUMPRODUCT(--(TEXT(\$A\$2:\$A\$39,"0")=\$A\$1),B\$2:B\$39),"")} NOTE: you cannot copy and paste it as an array formula nor can you just type { before and after it must be done as above, also if you edit the formula you must commit it as an array again.
#3
23-02-2011
 Member Join Date: Nov 2008 Posts: 1,514
Re: Find The Value of one column in another column

I have also same problem.
I have about 2000 records in Column 1 (a code consisting of letters and numbers). In Column 2 I have about 1500 records. All of the records in Column 2 are contained in Column 1. However, since about 500 are missing I cannot sort the equal values next to one another -
How to make it so that Excel matches the values in Column 2 next to the same values in Column1. Any help would appreciate.
#4
23-02-2011
 Member Join Date: Nov 2008 Posts: 1,192
Re: Find The Value of one column in another column

Use conditional formulas to highlight duplicates...
first select the column you want for copy
go to conditional formula and use formula given below
Formula=COUNTIF(\$A:\$A,\$A1)>1
then choose any bright color like RED to make duplicates jump out of
assumes that A1 is the first entry to be checked for that

 Tags: