Looking for unique values by comparing 2 columns and put them into next column

[barkaba]

Hi,
Im having problem finding proper formula. Let me show you first what I need. I have 3 columns
Column A
donkey
horse
cow
dolphin

Column B
dog
cat
mouse
owl
donkey
horse
cow
chicken
rat
turtle
baboon
camel
dolphin
monkey
goat
koala
moth
puma


So i need a formula which will fill in column C based on column B, but not include whats in colums A. So if something exist in colums A, column C shuold take next cell from column B, but should check if its in column A, if so, use next one, and again check it. I have tried index mixed with vlookup, but can get it right. It gives mi big headache now. Maybe someone here can help me please

[alansidman]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[barkaba]

Sorry, its been done

[XOR LX]

Hi.

Array formula**:

=IFERROR(INDEX($B$1:$B$18,SMALL(IF(1-ISNUMBER(MATCH($B$1:$B$18,$A$1:$A$4,0)),ROW($B$1:$B$18)-MIN(ROW($B$1:$B$18))+1),ROWS($1:1))),"")

Note that if your dataset is in reality much larger than the example you posted, i.e. thousands of rows, then please let me know as this set-up should then be tweaked so as to make it more efficient.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[barkaba]

Thank you so much, I have spent 2 days trying to sort it. Im gonna have up to 400 cell to compare to, so I think it should be ok. Thanks again

[XOR LX]

You're welcome.

Yes, that size a range should not post too much of a problem with this IFERROR set-up.

Cheers

[barkaba]

Hi again, I have another problem with this formula. I didnt expect it, but I gonna have to compare column B to two other columns, not one. Im trying to do this myself using yours formula, but no luck so far. Your help would be greatly appreciated.

[XOR LX]

Hi again.

I think an actual workbook would be beneficial in that case.

Make sure you replace any confidential data with dummy data if necessary. And also be sure to include your expected results.

Regards

[barkaba]

Thanks for quick reply. I can work with what I already got from you, but it can be done what I need, it would simlyfy what I need to do. I have created short version what I need to do, here is the file. I have explained in file what I need exacly. Thank you for your time

[XOR LX]

I'm sorry, but the layout is too poor for me to understand. I'm not sure where "here" means in a lot of instances, and everything seems to overlap.

But more importantly you don't seem to have included your expected results for this dataset, so I'm not sure what I'm aiming for.

Hopefully someone else will pick up on this thread shortly who can help you.

Regards

[barkaba]

Sorry for not explaining it more clearly. I have updated that file, and if you can just take a quick look it please. If its still not clear, don't worry, you have already helped me a lot. Thank you again for your time