Listing matches in Excel 2

[frantzx]

Sorry if this has been posted before, I'm fairly new to this. I'm looking to list matches or duplicate cells found in my spread sheet. Here's an example of what I have:

A B C D E


R-1-A1A1 R-1-B3A2 R-2-S1W4 R-3-G23P4 R-4-Y4I7
R-1-A30M2 R-1-Z90X1 R-2-E1A1 R-3-U21A6 R-4-J44A2
R-2-H2R1 R-1-F84D3 R-2-H2R1 R-3-Y1D77 R-4-R88I8
R-3-G23P4 R-1-A30M2 R-2-E5G8 R-3-X22M1 R-4-T9A4
R-4-R88I8

Coulmn A is my main data. I need to find the data in column A in coulmns B-E and list it in column F. Column F should list the duplicates found between coulmn A and columns B-E

Any help would be greatly appreciated.

Thanks

 

[SkipVought]

Hi,

[tt]
=IF(ISNA(MATCH($A1,B$1:B$4,0)),IF(ISNA(MATCH($A1,C$1:C$4,0)),IF(ISNA(MATCH($A1,D$1$4,0)),IF(ISNA(MATCH($A1,E$1:F$4,0)),"",$A1),$A1),$A1),$A1)
[/tt]
But how does that solve anything?

Take a look at Format > Conditional Formatting.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[frantzx]

I'm not sure I follow. Like I said I'm still a noob at excel

 

[SkipVought]

that's your column F formula. Paste in F1 and then copy down.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[frantzx]

I'm just getting blank cells, none of the matches are showing up in column F.

 

[SkipVought]

hit F9 to CALCULATE.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[frantzx]

Still nothing

 

[SkipVought]

Please copy the formula that you have in

F1 and paste.
F2 and paste.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[bluedragon2]

Skip,

Could you simplify the formula to:

=IF(COUNTIF(B1:E1,A1)>0,A1,"")



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[frantzx]

I did that, the formula will paste but nothing shows up in the cell. I pasted in F1, copied down, hit F9 to calculate and nothing happens.

 

[bluedragon2]

Sorry, copied the wrong formula:

=IF(COUNTIF($B$1:$E$5,A1)>0,A1,"")

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

I am asking you to COPY the forumlas that you have in F1 and in F2 and PASTE THEM HERE, so we can see exactly what FORMULAS you have in each of these two cells.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[frantzx]

F1:

=IF(ISNA(MATCH($A1,B$1:B$4,0)),IF(ISNA(MATCH($A1,C$1:C$4,0)),IF(ISNA(MATCH($A1,D$1$4,0)),IF(ISNA(MATCH($A1,E$1:F$4,0)),"",$A1),$A1),$A1),$A1)


F2:

=IF(ISNA(MATCH($A2,B$1:B$4,0)),IF(ISNA(MATCH($A2,C$1:C$4,0)),IF(ISNA(MATCH($A2,D$1$4,0)),IF(ISNA(MATCH($A2,E$1:F$4,0)),"",$A2),$A2),$A2),$A2)

 

[SkipVought]

Do you have any SPACE character either BEFORE or AFTER any of the data?

Select ALL ou data, Edit > Replace and and enter a SPACE character in the FIND box (nothing in the Replace box)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[frantzx]

Ah ha! That worked! Thank you so much.

 

[SkipVought]

Blue's formula will work and it's much simpler than mine.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}