Loop Problem/ Code Problem

[schwettmannkoro]

Hello dear community,
I have the following problem and hope to find help here:
I have two excel files. Number 1 contains an 8 digit number and number 2 contains a code with two to eight digits. In list 1, the numbers (eight digits) need in a tax rate, which should be output in a new column. For this purpose, the column with the code must be searched in list 2. If the number is not found, delete a digit from the right and search again. The goal is that each eight-digit number is assigned to a code and thus the code-related tax rate can be output.

[Daniel_Weikert]

Hi,
have you already checked out recursive loops?
They might be a starting point for you
br

[takbb]

Hi @schwettmannkoro , as @Daniel_Weikert says, recursive loops are generally the way to go where there is a need to recursively process a changing dataset.

The recursive loop would involve repeatedly attempt to match rows. Any matches would go to the top output of the loop end, and any non-matches would be truncated by 1 digit, and sent to the lower output port of the loop end for perceive in the next iteration. A maximum number of iterations can be set to 8, as this is the max length of a reference number being matched

It is also possible to do this without loops

Here every reference is joined to every code with a cross joiner, and then a comparison done to see if the reference starts with the code that it has been joined to. If it does, the row is kept. If not it is discarded. Finally all these “potential” matches are sorted into descending code order, thus for each reference, the one with the longest code matched appears earlier. A duplicate row filter can then be used to just keep the first match for each reference, and after some tidying up, you have the list.

The attached gives examples of both of the above

Find code within numbers with or without loops.knwf (55.9 KB)