Transpose and group data

问题:

I need to transpose two column in rows, and group by first column; here is an example. From this:

    A       B
    IP1     21
    IP1     22
    IP1     23
    IP2     80
    IP2     443
    IP3     21
    IP3     22
    IP3     23
    IP3     80
    IP3     443

To this:

A       B   C   D   E   F
IP1     21  22  23
IP2     80  443
IP3     21  22  23  80  443

How can I do this? Can I avoid the use of macro and VBA?

回答1:

You better use VBA, but if you really need formula solution:

First, you need to create Unique list: D2=IFERROR(INDEX($A$1:$A$19, MATCH(0, COUNTIF($D$1:D1, $A$1:$A$19), 0)),0) And drag it down to copy.

Then, we need to lookup for 1st, 2nd, 3rd, etc match: E2=IFERROR(INDEX($B$1:$B$19, SMALL(IF($D2=$A$1:$A$19, ROW($A$1:$A$19)-MIN(ROW($A$1:$A$19))+1, ""), E$1)),0) Again drag it down, drag it to the right to fill in all cells on E2:N12

Note: Rememeber to enter both formulas with CTRL+SHIFT+ENTER as they are both array formulas. Update ranges according to your data ranges.