Copying without repetition

[thong127]

Hello Gurus,

How can I copy my project code from Column B to column AJ without repetition?

Please find attached file.

Thanks.

thong

[Pete_UK]

Put this array* formula in AJ3:

=IFERROR(INDEX($B$3:$B$30,MATCH(0,COUNTIF($AJ$2:AJ2,$B$3:$B$30),0)),"")

and then copy down.

*An array formula needs to be committed using the key combination Ctrl-Shift-Enter (CSE), instead of the usual <Enter>.

Hope this helps.

Pete

[xladept]

Hi Pete,

I followed your instructions and got ACE for every one

(I'm trying to understand array formulas)

[Pete_UK]

Okay, well here's the file so you can check it out.

Pete

[thong127]

Thanks Pete, Works like a charm!

[FDibbins]

Here is a non-array version...
=IFERROR(INDEX($B$3:$B$30,MATCH(0,INDEX(COUNTIF($AJ$2:AJ2,$B$3:$B$30),),0)),"")

[newdoverman]

This solution will allow you to delete an entry in column B and it will also be deleted from column AJ closing the gap and without leaving a 0.

Please Login or Register  to view this content.

[xladept]

Thanks Pete,

I must have done something wrong