Unique formula killing my machine

[pauldaddyadams]

Hi,

I am using the formula

=IFERROR(INDEX(Veco!$B$2:$B$2229,MATCH(0,INDEX(COUNTIF($N$6:N7,Veco!$B$2:$B$2229),0,0),0)),0)

However it isn’t working as I intended for 2 reasons.

1. The first item it pulls in is a zero – can this be prevented?
2. The Formula is VERY slow and kills my machine – is there any way to speed it up?

What I need to do is pull in (column N), Unique Serivice order numbers from the tab “Veco” column B.

There will be blank rows and I need these ignored if possible.

Is there a quicker way to do this? I know I could use an advanced filter but I would need it to be a formula (or macro) as I need it automated as much as possible.

Paul

[martindwilson]

try a helper column insert a new column E on velco sheet
in e2 put
=IF(B2="","",IF(COUNTIF($B$2:B2,B2)=1,MAX($E$1:E1)+1,"")) fill down
then on data sheet in n7 put
=INDEX(Veco!$B$2:$B$2262,MATCH(ROWS($1:1),Veco!$E$2:$E$2262,0)) fill down (you can put iferror around that)
also
for example
=INDEX(Veco!$A:$AH,MATCH(Data!$N7,Veco!$B:$B,0),MATCH(Data!$E$6,Veco!$1:$1,0))
try not to use whole column refs use A2:$AH5000 or something that is the max youll need
oh btw this bit in E7 should be
=INDEX(Veco!$A:$AH,MATCH(Data!$N7,Veco!$B:$B,0),MATCH(Data!E$6,Veco!$1:$1,0)) filled across then down

[samba_ravi]

see the attached file, created one supporting column

[pauldaddyadams]

Thank you!! This method made the model much faster.

Thanks for the heads up on the "$"

[martindwilson]

deleted duplicate post