4b3ec3c6-c3e9-4932-9e67-20afe452a7c5 94 6 9 decimal

&[DATE] &[TIME] - &[FILENAME]

Data Table Parsing Function - Intelligent VLOOKUP()

]]>

checks for a set of conditions in a format mat(ColumnID,BooleanOperator,2,1)

, and compiles a matrix of rows that pass the check.

]]>

in order to check whether value in a column contains a specific string value use function:

]]>

#C# string like#

Where #C# is a designated placeholder which represents an element within the column and "string" is a string sought for

]]>

data# conditionsMATRIX# vlookup VLOOKUPcode# :

VLOOKUPcode# © 2016 Alexander Melnik data# data# eval : rows# data# rows : conditionsNumber# conditionsMATRIX# cols : A collection of boolean functions used to perform a check: 1. like#() function that checks a variable for a presence of a string var# string# like# varSTR# var# num2str : varSTR# string# findstr 1 - ≡ 01 if 21 line : 31 line m 0 : matches 01 matrix : i# 1 rows# range set boolean flag to true bool# 1 : row# data# i# row : For() loop boolean check - matrix of conditions; loop is terminated when bool is 0 i## 1 conditionsNumber# range bool# 0 ≡ break if columnID# conditionsMATRIX# 1 i## el : In the Matrix of Conditions, an undefined varible #C# is used; let's assign it a value #C# row# columnID# el : Now the boolean expression from the condition matrix can be evaluated operand# conditionsMATRIX# 2 i## el : Let's update the cumulative boolean check bool# bool# operand# * : 81 line for if all boolean checks were passed pull the row into a new matrix bool# m m 1 + : matches m el i# : 21 line if 71 line for m 0 > resultrng# data# matches 1 data# cols range el : no matches error if 91 line :

Lets input 10000 x 13 matrix from EXCEL - 1.15 seconds:

]]>

Lets input 10000 x 13 matrix from EXCEL - 1.15 seconds:

]]>

DATA DATA.xlsx Element Forces - Frames A4:M10000 excel_OUT :

DATA rows 9997

Lets run vlookup() without assembling result matrix (condition is not matched) - 3 seconds:

]]>

Lets run vlookup() without assembling result matrix (condition is not matched) - 3 seconds:

]]>

conditions 6 #C# 100000 - (≤ 21 mat :

res1 DATA conditions vlookup eval : #

res1 rows #

Lets run vlookup() to assemble 400 row matrix - 15 seconds:

]]>

Lets run vlookup() to assemble 400 row matrix - 15 seconds:

]]>

conditions 6 #C# 10000 - (≤ 21 mat :

res1 DATA conditions vlookup eval : 10 TEST1 Combination Max 10000 - 11111 1-1 021 TEST2 Combination Max 10000 - 11111 1-2 132 TEST3 Combination Max 10000 - 11111 1-3 243 TEST4 Combination Max 10000 - 11111 1-4 354 TEST5 Combination Max 10000 - 11111 1-5 465 TEST6 Combination Max 10000 - 11111 1-6 576 TEST7 Combination Max 10000 - 11111 1-7 687 TEST8 Combination Max 10000 - 11111 1-8 798 TEST9 Combination Max 10000 - 11111 1-9 8109 TEST10 Combination Max 10000 - 11111 1-10 91110 TEST11 Combination Max 10000 - 11111 1-11 101211 TEST12 Combination Max 10000 - 11111 1-12 111312 TEST13 Combination Max 10000 - 11111 1-13 121413 TEST14 Combination Max 10000 - 11111 1-14 131514 TEST15 Combination Max 10000 - 11111 1-15 141615 TEST16 Combination Max 10000 - 11111 1-16 151716 TEST17 Combination Max 10000 - 11111 1-17 161817 TEST18 Combination Max 10000 - 11111 1-18 171918 TEST19 Combination Max 10000 - 11111 1-19 182019 TEST20 Combination Max 10000 - 11111 1-20 192120 TEST21 Combination Max 10000 - 11111 1-21 202221 TEST22 Combination Max 10000 - 11111 1-22 212322 TEST23 Combination Max 10000 - 11111 1-23 222423 TEST24 Combination Max 10000 - 11111 1-24 232524 TEST25 Combination Max 10000 - 11111 1-25 242513 mat

res1 rows 400