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?

please see attached file.
Compare.xlsx
Teoman SahinAsked:
Who is Participating?
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.

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

https://support.microsoft.com/en-us/kb/213367
0
Subodh Tiwari (Neeraj)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))),"")

Open in new window

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))),"")

Open in new window

Confirm with Ctrl+Shift+Enter and then copy down until you get blank cells.

For details, refer to the attached.
Compare.xlsx
0
Teoman SahinAuthor 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?
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Subodh Tiwari (Neeraj)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.
0
Teoman SahinAuthor 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..
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
In the formula bar, select A2:A24 and press F4 once that will put the $ sign in the referenced range.
0
Subodh Tiwari (Neeraj)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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.