Index find next no match

[Bonkers Bill]

Hi every one, i am trying to index a list 'Got Upload'!A$1:A$1325, find the 1st one that is not in another list Remix!K$2:K$795, drag this down so it finds the 2nd, 3rd and so on
=index('Got Upload'!A$1:A$1325(nomatch(Remix!K$2:K$795,'Got Upload'!A$1:A$1325),0)
I know this formula is total rubbish but i dont know where to start, these are the ranges, any help would be great.

Today i will mostly be sailing down our street

 

[Aladin Akyurek]

Thanks facethegod that works great, there is another bit to this if you could help, the formula returns more than one result that is the same eg if there are 3 D0001 it returns 3, i would like the formula to only return 1 of each, no duplicates, do you know how to do this. Cheers

Today I will set sail to find land after the floods!

Got Upload

Bonkers Bill.xls
	A	B	C	D
1	Got
2	D001
3	D002
4	D003
5	D004
6	D005
7	D006
8	D003
9	D005
10
Got Upload

Remix

Bonkers Bill.xls
	J	K	L	M
1		Remix
2		D001
3		D002
4		D004
5		D006
6		D007
7		D008
8
Remix

Results

Bonkers Bill.xls
	A	B	C	D
1	4	2
2	List	D-List
3	D003	D003
4	D005	D005
5	D003
6	D005
7
8
Results

A1:

Control+shift+enter...

=SUM(IF('Got Upload'!A2:A9<>"",IF(ISNA(MATCH('Got Upload'!A2:A9,Remix!K2:K7,0)),1)))

A3:

Control+shift+enter...

=IF(ROWS($A$3:A3)<=$A$1,INDEX('Got Upload'!$A$2:$A$9,SMALL(IF(ISNA(MATCH('Got Upload'!$A$2:$A$9,Remix!$K$2:$K$7,0)),ROW('Got Upload'!$A$2:$A$9)-ROW('Got Upload'!$A$2)+1),ROWS($A$3:A3))),"")

then copy down.

Define List as referring to:

=OFFSET(Results!$A$3,0,0,Results!$A$1)

B1:

Control+shift+enter...

=SUM(IF(FREQUENCY(MATCH("~"&List,List&"",0),ROW(List)-ROW(A3)+1),1))

B3:

Control+shift+enter...

=IF(ROWS($B$3:B3)<=$B$1,INDEX(List,SMALL(IF(FREQUENCY(MATCH("~"&List,List,0),ROW(List)-ROW($A$3)+1),ROW(List)-ROW($A$3)+1),ROWS($B$3:B3))),"")

then copy down.

 

[Bonkers Bill]

Hi Aladin, thanks for your reply. i tried to do above but unsure about some of it. i put the offset formula in the column i want the search results and got value, i copied the results page and got name in B1 and B3 to B1325, the remix column is K2:K795, do you know where i have gone wrong, define list as refering to i do not understand and not sure where this goes.

 

[Aladin Akyurek]

Hi Aladin, thanks for your reply. i tried to do above but unsure about some of it. i put the offset formula in the column i want the search results and got value, i copied the results page and got name in B1 and B3 to B1325, the remix column is K2:K795, do you know where i have gone wrong, define list as refering to i do not understand and not sure where this goes.

The Offset formula defines List as a named range and goes in Insert|Name|Define. Note that the formula points to references in a sheet with Results as name. You need to adjust that to yours.

 

[Bonkers Bill]

Cheers Aladin, it works great.