Pivot in Excel without aggregation, to show text, not numbers?

Question

Let's say I have a table like this:

Country Region  Mytext
USA     North   a
USA     South   b
Brasil  North   c
Brasil  South   d

How can I obtain a pivot like this in Excel?

Country  North         South
USA      a             b
Brasil   c             d

If 'mytext' were a number, I could do a pivot table: since there is only one row per combination of country and region, min = max = sum = avg and any of these aggregate functions would, in fact, show me the result I want.

But what when the field I want is non-numeric? With Python's pandas library, I can use the dataframe.pivot() method even on non-numerical fields, but I haven't found a way to do the same in Excel. I could associate a number to each row, do the pivot in Excel to show that number, and then do a lookup to get the associated text, but it seems a rather convoluted process for something which should really be easier!

Answer 1

you can use the MS Power Query Add-in*. Pivoting tables is without vba right easy. It's free and very effective for data Transformation.

the M-Code

 let
    Source = Excel.CurrentWorkbook(){[Name="table1"]}[Content],
    #"Pivot column" = Table.Pivot(Source, List.Distinct(Source[Region]), "Region", "Mytext")
in
    #"Pivot column"

your Output

´* from MS Office 2016, it's fully integrated in Excel as Get & Transform function.