1

Topic: To collect the formula of substitution of values

Greetings
There is a table
1) the internal code of the goods; 2) the article of the vendor; 3) articles of analogs; 4) the found internal codes
1; 1; 2, 3; 2, 3
2; 2; 1; 1
3; 3; 2, 1; 2, 1
3 the column articles of analogs is a listing of suitable values from the second column
The task - to make substitution of the codes instead of articles
It is necessary to replace values of the third column with suitable values from the first and to interpose all it into the fourth.
It is possible to collect such formula, or only a script?

2

Re: To collect the formula of substitution of values

File in studio. On one sheet (same Excel?) the initial data, on the second - desirable result on this data.

Anton Klyauzov wrote:

it is possible to collect such formula, or only a script?

If in 3rd column strictly 2 parameters - it is possible also formulas (and with . Columns - is much easier). Even if, say, "no more than 4" - all the same it is possible. If it is not restricted - alas, only VBA.

3

Re: To collect the formula of substitution of values

Akina;
It is not restricted, alas.
I wrote in general:

Sub replacer ()
Dim ReplaceValue, SearchValue, LineNum
For LineNum = 1 To Rows. Count
SearchValue = Cells (LineNum, 4).Value
ReplaceValue = Cells (LineNum, 1).Value
Cells. Replace What: = SearchValue, Replacement: = ReplaceValue, LookAt: = xlPart, _
SearchOrder: = xlByRows, MatchCase: = False, SearchFormat: = False, _
ReplaceFormat: = False
Next
End Sub