microsoft excel index match row formula

Question

How to make this table to this result with formula ? sorry for my bad english. thank's

Does this answer your question? [How to combine or merge cells with the same values vertically and horizontally , Excel VBA?](https://stackoverflow.com/questions/69105746/how-to-combine-or-merge-cells-with-the-same-values-vertically-and-horizontally) — Yuthan, Nov 27 '21 at 06:33

score 0 · Answer 1 · answered Nov 27 '21 at 06:07

0

You cannot achieve this with the help of formulas alone.

You can achieve this through VBA, please refer to a similar VBA code answered by FaneDuru here, https://stackoverflow.com/a/69107658/15446521.

answered Nov 27 '21 at 06:07

Yuthan

41
5

score 0 · Answer 2 · answered Nov 27 '21 at 12:50

Actually it can be done by formula in Office 365. Preferably using LET:

=LET(data,A2:E13,
        rows,ROWS(data),
        a,SORTBY(data,INDEX(data,,1),,INDEX(data,,2),),
        b,INDEX(a,SEQUENCE(rows),SEQUENCE(1,2)),
        c,INDEX(b,,2),
        d,INDEX(a,,1),
        x,IF(SEQUENCE(rows)=1,d,IF(d=INDEX(d,SEQUENCE(rows,,0)),"",d)),
        y,IF(x<>"",c,IF(c=INDEX(c,SEQUENCE(rows,,0)),"",c)),
        z,INDEX(a,SEQUENCE(rows),SEQUENCE(1,5)),
        seq,SEQUENCE(1,columns(data)),
        IFERROR(CHOOSE({1,2,3},x,y,z),z))

But this is a whole lot of work for what can be accomplished by a simple Pivot Table.

So advice 1: use Pivot Table

advice 2: use the VBA solution already answered

advice 3: use the formula solution above. Please note that not showing duplicate values only works for the first 2 columns

microsoft excel index match row formula

2 Answers2