0

I have an excel file with many rows like:

category1 | keyword1 | type keyword1
category1 | keyword2 | type keyword1
category2 | keyword3 | type keyword2
category2 | keyword4 | type keyword1
category2 | keyword5 | type keyword2
category3 | keyword6 | type keyword3
category4 | keyword7 | type keyword2
category4 | keyword8 | type keyword3
category4 | keyword9 | type keyword1
category4 | keyword1 | type keyword1

I'd like to transform this to:

category1 | keyword1 | type keyword1 | keyword2 | type keyword1
category2 | keyword3 | type keyword2 | keyword4 | type keyword1 | keyword5 | type keyword2
category3 | keyword6 | type keyword3
category4 | keyword7 | type keyword2 | keyword8 | type keyword3 | keyword9 | type keyword1 | keyword1 | type keyword1

Problem is:

  • I have an inconsistent number of rows per category
JvdV
  • 70,606
  • 8
  • 39
  • 70
mh3982
  • 97
  • 6
  • And how do you like to approach this problem? VBA or Formula? And what have you tried yourself? – JvdV Oct 18 '19 at 08:03
  • I tried to load it into a database (SQL), but failed writing a query to do this.. And in excel i tried all kind of standard functions, but found nothing that worked. – mh3982 Oct 18 '19 at 08:07
  • You'd probably need to use `PIVOT` in sql ([something a bit like this](https://stackoverflow.com/questions/10428993/understanding-pivot-function-in-t-sql)), except in that case, SQL would put your keywords in the same column (all keyword1 in one column, keyword2 in another column, etc) – Jerry Oct 18 '19 at 08:45
  • FYI power query in Excel 2010 and later versions can handle this job too :) – Terry W Oct 21 '19 at 05:16

2 Answers2

2

Through worksheet functions:

enter image description here

Formula in E2 (and drag down):

=INDEX($A$2:$A$11,MATCH(0,COUNTIF($E$1:E1,$A$2:$A$11),0))

Note: It's an array formula that needs be entered through CtrlShiftEnter

Formula in F2 (and drag right and down):

=IFERROR(IF(MOD(COLUMN(),2)=0,INDEX($B$1:$B$11,SMALL(IF($A$2:$A$11=$E2,ROW($A$2:$A$11),""),ROUND(COLUMN(A1)/2,0))),INDEX($C$1:$C$11,SMALL(IF($A$2:$A$11=$E2,ROW($A$2:$A$11),""),ROUND(COLUMN(A1)/2,0)))),"")

Note: It's an array formula that needs be entered through CtrlShiftEnter

JvdV
  • 70,606
  • 8
  • 39
  • 70
1

Another option and in non-array formula

Assume "Input table" housed in A1:C11 with header, and "Output table" housed in E1:N5 with header (something like JvdV's table layout)

Then, in E2, copied down :

=IFERROR(INDEX($A$2:$A$11,MATCH(0,INDEX(COUNTIF(E$1:E1,$A$2:$A$11),0),0)),"")

In F2, copied across to N2 and all copied down :

=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,ROW($2:$11)/1%+{2,3}/($A$2:$A$11=$E2),COLUMN(A1)),"R0C00"),0),"")
bosco_yip
  • 3,762
  • 2
  • 5
  • 10
  • Both formulas are actually still array-formulas, just not entered as such. The second one is also volatile. Either way, + for the option to choose :) – JvdV Oct 18 '19 at 09:46