Transpose and group data
excel, grouping, transpose
Solution
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.
Problem
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?