Summarizing of text data in pivot tables.

[DavidBridges]

My task involves manipulation of gene sequencing data.  The description of the task is simplified to focus on key issues.

I have text data relating to a single gene that is spread over multiple rows.  The test strings are quite long and of variable length.  In the example I abbreviate the strings to a, b,c etc

I need to transpose the text data relating to a gene so that there is one row per gene and the text data is stores as separate columns ie

Gene Row Data

GN1   1     a

GN1   2     c

GN1   3     d

GN2   1     a

GN2   2     b

GN2   3     e

GN2   4     f

GN3   1     x

Desired outcome :

Gene Text1 Text2 Text3 Text4

GN1   a       c        d

GN2   a       b        e       f

GN3   x

The transpose function doesn't work for me as it transposed the entire array.

I had high hopes for the Pivot Table, but it doesn't support presentation of text data within the body of the Pivot Table.  The functions COUNT, SUM, MIN, MEAN etc only work for numeric data.  I had hoped the MIN or MAX function would work for test data but it returns a value of 0.  See attached screen shot.

Can anyone help me achieve my goal, using Pivot Table or other method?

[#2]

@DavidBridges This becomes easy with Get & Transfor Data (a.k.a. Power Query). See attached workbook based on you example. This will work particularly well on large data sets.

[#3 · Accepted solution]

@DavidBridges 

To do that with PivotTable you need to add data to data model creating it and add measure like

Measure:=CONCATENATEX(Table1,Table1[Data])

and use it as

[#4]

Thanks for your help. Not what I would call intuitive but it works.

[#5]

@DavidBridges , you are welcome