# comparing two coloumns in Excel

I have two column in Excel, which are SalesProduct and other one SalesTest..I would like to compare these two coloumns and then

1.if the products in SalesProduct coloumn is available in the other coloumn SalesTest,which mean if there are common products in both columns,these common products will be typed next to coloumn

2.if the products in SalesProduct coloumn is NOT available in the other column SalesTest, these noncommon products will be typed in the other coloumn.how can I do this?

Compare.xlsx
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
You use the macro in this below site. This will solve your problem.

https://support.microsoft.com/en-us/kb/213367
Excel & VBA ExpertCommented:
Try this Formula Solution.

The formulas used are Array Formulas which require confirmation with Ctrl+Shift+Enter instead of Enter alone.

In C2
``````=IFERROR(INDEX(\$A\$2:\$A\$24,SMALL(IF(ISNUMBER(MATCH(\$A\$2:\$A\$24,\$B\$2:\$B\$14,0)),ROW(\$A\$2:\$A\$24)-ROW(\$A\$2)+1),ROWS(C\$2:C2))),"")
``````
Confirm with Ctrl+Shift+Enter and then copy down until you get blank cells.

In D2
``````=IFERROR(INDEX(\$A\$2:\$A\$24,SMALL(IF(ISNA(MATCH(\$A\$2:\$A\$24,\$B\$2:\$B\$14,0)),ROW(\$A\$2:\$A\$24)-ROW(\$A\$2)+1),ROWS(D\$2:D2))),"")
``````
Confirm with Ctrl+Shift+Enter and then copy down until you get blank cells.

For details, refer to the attached.
Compare.xlsx
Author Commented:
many thanks..as far as I see, it had worked well so but I don't know how you have executed this formula and how you created it..attached file is only example but in my real excel,there are 500 rows and I don't know how to create this formula?

=IFERROR(INDEX(\$A\$2:\$A\$24,SMALL(IF(ISNA(MATCH(\$A\$2:\$A\$24,\$B\$2:\$B\$14,0)),ROW(\$A\$2:\$A\$24)-ROW(\$A\$2)+1),ROWS(D\$2:D2))),"")

=IFERROR(INDEX Is fixed.(....) then we choose all the rows in coloumn1 something like that..is that right?
Excel & VBA ExpertCommented:
The ranges used in the formulas are \$A\$2:\$A\$24 assuming that the data in col. A is in this range and \$B\$2:\$B\$14 assuming that the data in col. B is in this range.
You just need to change these range references in the formulas as per your actual data. So for example if the data in col. A goes to row 500 then change all the occurrences of \$A\$2:\$A\$24 to \$A\$2:\$A\$500.

When you type a regular formula in a cell, you confirm the formula by pressing Enter.
But in case of an Array Formula, you need to do it differently depending on the case...

1) Typing the Array Formula in Cell:
After you type your Array Formula in the formula cell, don't press Enter to confirm it but hold down the Ctrl + Shift keys together and then hit Enter to confirm the formula as an Array formula.

2) When you are editing an Array Formula.
Select the Array Formula Cell and press F2, now edit your formula as required and then hold down the Ctrl + Shift keys together and then hit Enter to confirm the formula as an Array formula.

Remember that when an Array Formula is entered correctly, you will notice the curly braces around the formula in the Formula bar. And if you don't see these curly braces around the formula in the formula bar, Press F2 in the formula cell and try again to enter that formula as an array formula by pressing Ctrl+Shift+Enter.
Author Commented:
I am so sorry but I am choosing the col.SalesProducts from A2 den A24 and it says with =iferror(index(A2:A24 so I could not understand how you created \$A\$2:\$A\$24..there are \$ symbols..could you please tell me how you created this \$A\$2:\$A\$24..
Excel & VBA ExpertCommented:
In the formula bar, select A2:A24 and press F4 once that will put the \$ sign in the referenced range.
Excel & VBA ExpertCommented:
Just for information, a \$ sign locks the cell reference.
\$A\$2:\$A\$24 means that when you drag the formula down the rows, the referenced range will be static and will not be changed, this is called absolute reference.

So if you just place A2:A24 in the formula and when you drag or copy the formula down the rows, the reference ranged will be changed. To understand this, in cell B2 place the formula =A2 and then drag the formula down the rows, the formula in below rows will be changed to =A3, =A4. =A5 and so on as the reference is not locked and this is called relative cell reference.

You have options to lock the Column or Row reference individually i.e. \$A4 or A\$4. After selecting the range if you keep on pressing F4 key, you will notice that the \$ sign appear differently.

Experts Exchange Solution brought to you by