0

Apologies if this has been asked before.

Suppose we have 2 columns of data, without loss of generality A and B. I would like to be able to produce a column C which contains entries that appear in columns A and B - in other words the entries of C are found in a cell An in column A and a cell Bm in column B where obviously m,n are integers.

If it helps, entries don't repeat in a column.

Simple example: Where A contains the entries 3, 7, 1, 4, 5 and B contains the entries 9, 1, 4, 5, 2 then C should contain 1, 4, 5 (order of entries in column C doesn't matter for my purpose)

  • Welcome to Super User. New members commonly mistake this for a service site where we will do the work. It is a Q&A community where specific questions are asked after you have attempted something and get stuck. Please add details of what you have tried so far, including scripts, code or formulas, and we will try to help. If you need more info about asking questions, check out How to Ask in the help center. – CharlieRB Jun 2 '16 at 16:04
  • If i did that, the explanation would be TL;DR. People are free not to reply. – potat0 Jun 2 '16 at 17:12
  • Actually, this reply really irks me. I would wager there are a number of topics (e.g. pure mathematics) where I would have specialist knowledge you do not. So am I not supposed to post anything regarding a comparatively basic topic that i just don't have the time to read the entire internet and waste a lot of time for offending your sensibilities? – potat0 Jun 2 '16 at 19:25
  • You are wasting your time ranting. There is no reason to take anything here personal. I simply want to help you realize your question sounds like you expect us to do your work (which we realize is not always the case). Asking for additional details helps us to know what you have attempted so we don't duplicate the effort and offer solutions you have already tried. We see this over and over again. Bottom line is, if you do not want to add details, then don't. I really don't care either way. – CharlieRB Jun 2 '16 at 19:49
0

Here is a solution with a "helper" column that avoids array formulas.

In C1 enter:

=IF(COUNTIF(B:B,A1)<>0,1,"")

In C2 enter:

=IF(COUNTIF(B:B,A2)<>0,1+MAX($C$1:C1),"")

and copy down. (column C "marks" the "good" rows.)

Finally in D1 enter:

=IFERROR(INDEX(A:A,MATCH(ROWS($1:1),C:C,0)),"")

and copy down:

enter image description here

and if you do not mind array formulas, see Chip Pearson's Write-up

  • Do you mean 'in C3' .. 'in C4' as your illustration shows (instead of 'in C1' and 'in C2' as you have written?) – potat0 Jun 2 '16 at 18:24
  • @potat0 NO...............use C1 and C2..............they will show as blank, but that's O.K. – Gary's Student Jun 2 '16 at 18:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.