how to transform text value in Pivot Table

[Sian1]

Hi, hope someone could assist how to transform the following table into Pivot in Text format

from the below table to this in Pivot perhaps or matrix? is it possible?

	Column Labels
Row Labels	0-25%	26%-50%	>50%
>1000			Great Britain, U.S.A
101-1000	Kuwait, Finland, Norway…
0-100

PO Vol Bucket	PO Country	PO %Rework Bucket
101-1000	KUWAIT	0-25%
>1000	GREAT BRITAIN	>50%
>1000	U.S.A.	26%-50%
101-1000	FINLAND	>50%
101-1000	NORWAY	26%-50%
>1000	ITALY	>50%
101-1000	BELGIUM	>50%
>1000	FRANCE	>50%
101-1000	CANADA	26%-50%
101-1000	IRELAND	>50%
101-1000	CHINA (P.R.C.)	>50%
>1000	NETHERLANDS	26%-50%
>1000	GERMANY	26%-50%
101-1000	AUSTRIA	26%-50%
101-1000	SWITZERLAND	>50%
101-1000	SPAIN	>50%
101-1000	SWEDEN	>50%
101-1000	DENMARK	>50%
101-1000	PUERTO RICO	>50%
101-1000	JAPAN	26%-50%
0-100	NEW ZEALAND	>50%
101-1000	AUSTRALIA	>50%
101-1000	SINGAPORE	>50%
101-1000	UNITED ARAB EMIRATES	26%-50%
0-100	MALAYSIA	>50%
0-100	TAIWAN - CHINA	26%-50%
101-1000	BERMUDA	>50%
101-1000	INDIA	>50%
101-1000	SOUTH AFRICA	26%-50%
101-1000	RUSSIA	0-25%
101-1000	PORTUGAL	26%-50%
101-1000	HONG KONG	>50%
0-100	GREECE	>50%
0-100	BRAZIL	>50%
101-1000	MEXICO	>50%
0-100	QATAR	0-25%
0-100	ISRAEL	26%-50%
0-100	LEBANON	0-25%
0-100	PHILIPPINES	26%-50%
0-100	THAILAND	0-25%
0-100	EGYPT	0-25%
0-100	INDONESIA	0-25%
0-100	MALTA	0-25%
0-100	SOUTH KOREA	26%-50%
0-100	ECUADOR	>50%
0-100	VIETNAM	0-25%
0-100	KENYA	0-25%

 

[Peter_SSs]

Not a Pivot Table but see if this would do.

I have assumed that the bottom table is in the active sheet starting in A1 and that the results can go in columns E:H starting in row 1.

Sub Rearrange()
  Dim a As Variant, b(1 To 3, 1 To 3) As Variant
  Dim i As Long, r As Long, c As Long
  
  Const rString As String = "1>10002101-100030-100"
  Const cString As String = "10-25%226%-50%3>50%"
  
  a = Range("A2", Range("C" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    r = Mid(rString, InStr(rString, a(i, 1)) - 1, 1)
    c = Mid(cString, InStr(cString, a(i, 3)) - 1, 1)
    b(r, c) = b(r, c) & IIf(Len(b(r, c)) = 0, "", ", ") & a(i, 2)
  Next i
  With Range("F2:H4")
    .ColumnWidth = 60
    .WrapText = True
    .Value = b
    .Rows(0).Value = Array("0-25%", "26%-50%", ">50%")
    .Columns(0).Value = Application.Transpose(Array(">1000", "101-1000", "0-100"))
  End With
End Sub

For your sample data, this is my result:

Sian1.xlsm
	E	F	G	H
1		0-25%	26%-50%	>50%
2	>1000		U.S.A., NETHERLANDS, GERMANY	GREAT BRITAIN, ITALY, FRANCE
3	101-1000	KUWAIT, RUSSIA	NORWAY, CANADA, AUSTRIA, JAPAN, UNITED ARAB EMIRATES, SOUTH AFRICA, PORTUGAL	FINLAND, BELGIUM, IRELAND, CHINA (P.R.C.), SWITZERLAND, SPAIN, SWEDEN, DENMARK, PUERTO RICO, AUSTRALIA, SINGAPORE, BERMUDA, INDIA, HONG KONG, MEXICO
4	0-100	QATAR, LEBANON, THAILAND, EGYPT, INDONESIA, MALTA, VIETNAM, KENYA	TAIWAN - CHINA, ISRAEL, PHILIPPINES, SOUTH KOREA	NEW ZEALAND, MALAYSIA, GREECE, BRAZIL, ECUADOR
Sheet1

 

[Sian1]

Not a Pivot Table but see if this would do.

I have assumed that the bottom table is in the active sheet starting in A1 and that the results can go in columns E:H starting in row 1.

Sub Rearrange()
  Dim a As Variant, b(1 To 3, 1 To 3) As Variant
  Dim i As Long, r As Long, c As Long
 
  Const rString As String = "1>10002101-100030-100"
  Const cString As String = "10-25%226%-50%3>50%"
 
  a = Range("A2", Range("C" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    r = Mid(rString, InStr(rString, a(i, 1)) - 1, 1)
    c = Mid(cString, InStr(cString, a(i, 3)) - 1, 1)
    b(r, c) = b(r, c) & IIf(Len(b(r, c)) = 0, "", ", ") & a(i, 2)
  Next i
  With Range("F2:H4")
    .ColumnWidth = 60
    .WrapText = True
    .Value = b
    .Rows(0).Value = Array("0-25%", "26%-50%", ">50%")
    .Columns(0).Value = Application.Transpose(Array(">1000", "101-1000", "0-100"))
  End With
End Sub

For your sample data, this is my result:

Sian1.xlsm
	E	F	G	H
1		0-25%	26%-50%	>50%
2	>1000		U.S.A., NETHERLANDS, GERMANY	GREAT BRITAIN, ITALY, FRANCE
3	101-1000	KUWAIT, RUSSIA	NORWAY, CANADA, AUSTRIA, JAPAN, UNITED ARAB EMIRATES, SOUTH AFRICA, PORTUGAL	FINLAND, BELGIUM, IRELAND, CHINA (P.R.C.), SWITZERLAND, SPAIN, SWEDEN, DENMARK, PUERTO RICO, AUSTRALIA, SINGAPORE, BERMUDA, INDIA, HONG KONG, MEXICO
4	0-100	QATAR, LEBANON, THAILAND, EGYPT, INDONESIA, MALTA, VIETNAM, KENYA	TAIWAN - CHINA, ISRAEL, PHILIPPINES, SOUTH KOREA	NEW ZEALAND, MALAYSIA, GREECE, BRAZIL, ECUADOR
Sheet1

thanks so much Peter !!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.