mole999
Well-known Member
- Joined
- Oct 23, 2004
- Messages
- 10,524
- Office Version
- 2019
- 2016
- 2013
- Platform
- Windows
I'm after some guidance. I have a file that is currently 30,000 rows with lots of formulas, I'm wondering if a UDF or 3 would assist in the speed and data return
example
=IF(O29637="","",IFERROR(IFERROR(VLOOKUP(O29637&"",NAMES,2,0),VLOOKUP(--O29637,NAMES,2,0)),""))
or
=TRIM(SUBSTITUTE(SUBSTITUTE(IF(S29610<>"","("&S29610&") "," ")&IF(T29610="",U29610,"["&T29610&"] "&U29610)&IF(Z29610<>""," "&Z29610,"")&IF(V29610=""," "&W29610," J/W " &" ["&V29610&"] "& W29610)&IF(AA29610<>"",", @ "&AA29610,"")&IF(AB29610<>"",", Nr. "&AB29610,"")&IF(AD29610<>"",", Twds. "&AD29610,"")&IF(AC29610<>"",", Btwn. "&AC29610,"")&IF(X29610<>"",", "&X29610,"")&IF(AE29610<>"",", "&AE29610,"")&" "&IF(Y29610<>"",", "&Y29610,"")," "," ")," , ",", "))
I can't see how to start either of these apart from Sub Function
The first would be to utilise a Vlookup, the second concatenates values from a range of cells and options.
guidance appreciated
example
=IF(O29637="","",IFERROR(IFERROR(VLOOKUP(O29637&"",NAMES,2,0),VLOOKUP(--O29637,NAMES,2,0)),""))
or
=TRIM(SUBSTITUTE(SUBSTITUTE(IF(S29610<>"","("&S29610&") "," ")&IF(T29610="",U29610,"["&T29610&"] "&U29610)&IF(Z29610<>""," "&Z29610,"")&IF(V29610=""," "&W29610," J/W " &" ["&V29610&"] "& W29610)&IF(AA29610<>"",", @ "&AA29610,"")&IF(AB29610<>"",", Nr. "&AB29610,"")&IF(AD29610<>"",", Twds. "&AD29610,"")&IF(AC29610<>"",", Btwn. "&AC29610,"")&IF(X29610<>"",", "&X29610,"")&IF(AE29610<>"",", "&AE29610,"")&" "&IF(Y29610<>"",", "&Y29610,"")," "," ")," , ",", "))
I can't see how to start either of these apart from Sub Function
The first would be to utilise a Vlookup, the second concatenates values from a range of cells and options.
guidance appreciated
