modified formula delete duplicated item

[leap out]

hello
I need amending this formula it should ignore duplicated items

=OFFSET(Sheet1!$A$1,MATCH(G10,Sheet1!$A:$A,0)-1,1,COUNTIF(Sheet1!$A:$A,G10),1)

thanks in advance

 

[StephenCrump]

I'm not clear what you mean?

To return just the first occurrence: =INDEX(Sheet1!B:B,MATCH(G10,Sheet1!$A:$A,))

 

[leap out]

this formula is existed in COL B linked with COL A if I select from COL A then show the items in COL B but the problem the items are repeated in COL B
this formula shows like this in COL B after select from COL A
IT-1000
IT1000
ITD-2000
ITD-2000
it should like this
IT-1000
ITD-2000

 

[StephenCrump]

Do you mean like this?

	A	B
1	MyList	Uniques
2	IT1000	IT-1000
3	ITD-2000	ITD-2000
4	ITD-2000	IT3000
5	IT-1000	X-XXX
6	ITD2000	YYYYY
7	IT3000	Z-Z
8	X-XXX
9	YYYYY
10	XXXX
11	ZZ
12	XXXX
13	Z-Z
Sheet7

Cell Formulas
Range	Formula
B2:B13	B2	=IFERROR(INDEX(MyListHyphen,MATCH(0,MMULT(--(MyListHyphen=TRANSPOSE(B$1:B1)),ROW(INDIRECT("1:"&ROWS(B$1:B1)))^0),)),"")

Named Ranges
Name	Refers To	Cells
MyList	=Sheet7!$A$2:$A$13	B2:B13

where MyListHyphen: =IFERROR(INDEX(MyList,MATCH(SUBSTITUTE(MyList,"-",""),IF(ISNUMBER(FIND("-",MyList)),SUBSTITUTE(MyList,"-","")),)),MyList)

 

[leap out]

actually your formula doesn't work correctly for me

Microsoft Excel (7).xlsx
	A	B
1	MyList	Uniques
2	IT1000	IT1000
3	ITD-2000
4	ITD-2000
5	IT-1000
6	ITD2000
7	IT3000
8	X-XXX
SH

Cell Formulas
Range	Formula
B2:B8	B2	=IFERROR(INDEX(MyListHyphen,MATCH(0,MMULT(--(MyListHyphen=TRANSPOSE(B$1:B1)),ROW(INDIRECT("1:"&ROWS(B$1:B1)))^0),)),"")

Named Ranges
Name	Refers To	Cells
MyListHyphen	=SH!$A$2:$A$7	B2:B8

XR][/RANGE]

 

[leap out]

actually your formula doesn't work correctly for me

Microsoft Excel (7).xlsx
	A	B
1	MyList	Uniques
2	IT1000	IT1000
3	ITD-2000
4	ITD-2000
5	IT-1000
6	ITD2000
7	IT3000
8
SH

Cell Formulas
Range	Formula
B2:B8	B2	=IFERROR(INDEX(MyListHyphen,MATCH(0,MMULT(--(MyListHyphen=TRANSPOSE(B$1:B1)),ROW(INDIRECT("1:"&ROWS(B$1:B1)))^0),)),"")

Named Ranges
Name	Refers To	Cells
MyListHyphen	=SH!$A$2:$A$7	B2:B8

 

[StephenCrump]

My formula uses two range names:

MyList: =SH!$A$2:$A$7

MyListHyphen: =IFERROR(INDEX(MyList,MATCH(SUBSTITUTE(MyList,"-",""),IF(ISNUMBER(FIND("-",MyList)),SUBSTITUTE(MyList,"-","")),)),MyList)

 

[leap out]

MyListHyphen actually I no know how the second formula how works I put in another COL C but I did not see any thing changes

 

[StephenCrump]

Here's my workbook: MyList.xlsx

To create the name MyListHyphen, I used Formula/Name Manager/New ...
Name:=MyListHyphen
Refers to: =IFERROR(INDEX(MyList,MATCH(SUBSTITUTE(MyList,"-",""),IF(ISNUMBER(FIND("-",MyList)),SUBSTITUTE(MyList,"-","")),)),MyList)

We could combine into a single formula, but it's ugly:

=IFERROR(INDEX(IFERROR(INDEX(MyList,MATCH(SUBSTITUTE(MyList,"-",""),IF(ISNUMBER(FIND("-",MyList)),SUBSTITUTE(MyList,"-","")),)),MyList),MATCH(0,MMULT(--(IFERROR(INDEX(MyList,MATCH(SUBSTITUTE(MyList,"-",""),IF(ISNUMBER(FIND("-",MyList)),SUBSTITUTE(MyList,"-","")),)),MyList)=TRANSPOSE(B$1:B1)),ROW(INDIRECT("1:"&ROWS(B$1:B1)))^0),)),"")

 

[leap out]

sorry for delaying to answer you , the problem seems version office because when I open your file and before enable the macro it shows the data as post#4 but when enable it just show the first value