Replace an empty exel cell for a number of conditions-CodePudding

Help me please. There is a large table (> 3000 lines), you need to check a certain column (F) for the presence of empty cells. If such (F3) is (F3), it is necessary to check the value in another cell (A3) (articles), to coincide with the upper or lower line (A2, A4) (duplicate of the line), depending on the coincidence (for example, the duplicate turned out to be from above A2), pull the value from the cell in this line (F2) and insert into the empty (F3). The lines are duplicated (from which they took the value (line 2)) to remove.

now

need

try

= IF (F3 = "";
IF (A3 = A2; F2;
IF(A3 = A4; F4)
); ""
)

err511

CodePudding user response：

Assume now is a Table named now

A11: =UNIQUE(now[Column1])
B11: =LET(f, FILTER(now,now[Column1]=A11), t,BYCOL(f,LAMBDA(f, IFERROR(LOOKUP(2, 1/LEN(f),f),""))), INDEX(t,1,SEQUENCE(,COLUMNS(t)-1,2,1)))

Select B11 and fill down.

Note: A11 and B11 can be any adjacent pair of cells

CodePudding user response：

There's a typo in your formula, this is better:

= IF(F3 = ""; IF(A3 = A2; F2; IF(A3 = A4; F4;"")))

Which means:

= IF(F3 = "";
     IF(A3 = A2; F2; 
        IF(A3 = A4; F4;"")))

(You had used two "else"-cases for the second "if".)