将多列中的值合并为一列

I have the following data structure:



As you see in column J, I am trying to merge data into one column from columns A & C & E & G.

我正在使用这个公式：

=IF(ROW()<=COUNTA($A:$A);INDEX($A:$C;ROW();COLUMN(A1));INDEX($A:$C;ROW()-COUNTA($A:$A)+1;COLUMN(C1)))

我得到列中的值K如你所见。目前该公式仅合并两列。如何修改它以合并所有四列？

以及如何仅从以下位置获取这些值row 5?
列高会不断变化：有时 A 列中有 10 个值，有时有 2 个值。

任何 Excel 公式或任何 VBA 代码都可以接受。

有一个相当标准的方法可以从列中检索唯一值，但不能从多个列中检索唯一值。要实现从多列检索，您需要将多个公式堆叠在一起，并将处理传递到较早的公式出错的连续列。

The 数组公式1 在 J5 中是，

=IFERROR(INDEX($A$5:$A$99, MATCH(0, IF(LEN($A$5:$A$99), COUNTIF(J$4:J4, $A$5:$A$99), 1), 0)),
 IFERROR(INDEX($C$5:$C$99, MATCH(0, IF(LEN($C$5:$C$99), COUNTIF(J$4:J4, $C$5:$C$99), 1), 0)),
 IFERROR(INDEX($E$5:$E$99, MATCH(0, IF(LEN($E$5:$E$99), COUNTIF(J$4:J4, $E$5:$E$99), 1), 0)),
 IFERROR(INDEX($G$5:$G$99, MATCH(0, IF(LEN($G$5:$G$99), COUNTIF(J$4:J4, $G$5:$G$99), 1), 0)),
 ""))))

我只包含 A、C、E 和 G 列，因为您的示例数据仅显示 B、D、F 和 H 列中的重复项。

¹ _{Array formulas need to be finalized with Ctrl+Shift+Enter↵. If entered correctly, Excel with wrap the formula in braces (e.g. { and }). You do not type the braces in yourself. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula. Try and reduce your full-column references to ranges more closely representing the extents of your actual data. Array formulas chew up calculation cycles logarithmically so it is good practise to narrow the referenced ranges to a minimum. See Guidelines and examples of array formulas https://support.office.com/en-ca/article/guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7 for more information.}