matching cell values

[ccarruth]

This forum rocks...hope this is simple...for someone LOL.

Two adjoining cell ranges contain text, with on value in each cell:

A D C F J and... P A L C D

without using a macro, need to find all values match between these ranges of cells, irrespective of where the value is within the range of cells.

Correct answer would be A, D, C, with each value placed in a different cell range apart from the original groupings of values (different cells, no overwriting of original values or cell locations)

Doesn't matter order of returned matching values...c'mon, someone can do this in their sleep!

 

[Nebu]

Hi:

Find the attached, array formula execute by pressing control+shift+enter keys

=IFERROR(INDEX($A$2:$A$6,SMALL(IF(ISNUMBER(MATCH($A$2:$A$6,$B$2:$B$6,0)),ROW($B$2:$B$6)-ROW($B$2)+1),ROWS(A$2:A2))),"")

Thanks

 

[bosco_yip]

Another option,

C2, array formula ( confirm pressing with SHIFT+CTRL+ENTER 3 keys altogether) copy down :

=IFERROR(INDEX(A$2:A$6,SMALL(IF(COUNTIF(B$2:B$6,A$2:A$6),ROW($1:$5)),ROWS($1:1))),"")

Regards
Bosco

 

[ccarruth]

Both of these are done for columns...as shown, the data is in cells in rows..

C

 

[ccarruth]

need outputs in adjoining cells in rows

 

[Nebu]

Hi:
May be something like this?

=IFERROR(INDEX(TRANSPOSE($E$2:$I$2),SMALL(IF(ISNUMBER(MATCH(TRANSPOSE($E$2:$I$2),TRANSPOSE($E$3:$I$3),0)),ROW($A$2:$A$6)-ROW($A$2)+1),ROWS($A$2:A2))),"")

Thanks

 

[ccarruth]

Thanks for all the help..not quite it...see wkbk cells with headings in bold.

 

[Nebu]

Hi:

May be this.

Thanks

 

[ccarruth]

Ok...this is working off prior suggestions versus the cells I bolded. Have removed all others. Assume the first batch and second batch cells are the only values there are. Output in cells next to it.

 

[ccarruth]

with workbook

 

[NARAYANK991]

Hi ,

If you do not mind the output being in any order , this might do.

Narayan

 

[ccarruth]

Hi ,

If you do not mind the output being in any order , this might do.

Narayan

Thanks Narayan.

What if there are multiple rows of data? Does this work given the use of naming the range? If there are 200 rows does that mean 400 named ranges? And the brackets mean this is an array formula?

 

[ccarruth]

And.. what purpose does A1, B1, C1 serve?

Thanks Narayan.

What if there are multiple rows of data? Does this work given the use of naming the range? If there are 200 rows does that mean 400 named ranges? And the brackets mean this is an array formula?

 

[ccarruth]

Attached workbook is more clear. Help!

 

[Nebu]

Hi:

I am not sure, why you were saying the workbook I have uploaded on post# 8 was not working. It was giving exactly what you have asked for. Anyhow, I have replicated the same formula on your latest upload, find the attached.

Thanks