# Transpose at every change in name

#### feef

##### New Member
Is there a function that exists where for each change in name, a transpose is made?

For example, for the Subtotal function, you can perform a sum for each name change. Instead of doing a sum, can we do a transpose?

Similar to the Subtotal function where for each change in name you can perform a sum?

For example, I have vertical list like this:

Name State
Bob Alabama
Bob Arizona
Bob Arkansas
Bob California
Bob Connecticut
Bob Delaware
Christie Alabama
Christie Arizona
Christie Arkansas
Christie California
Christie Connecticut

Imagine I have 100 of these names listed in the format above. Instead of me going in the list to manually "transpose" the states for each name, is there a function that exists where for for each name change in column A transposes cells (B1:B5)?

Thanks!

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### jindon

##### MrExcel MVP
try
Code:
``````Sub test()
Dim a, i As Long, n As Long, x As Long, w(), myMax As Long
a = Range("a1").CurrentRegion.Resize(,1).Value
ReDim b(1 To UBound(a,1), 1 To Columns.Count)
With CreateObject("Scripting.Dictionary")
.CompareMode = vbTextCompare
For i = 1 To UBound(a,1)
If Not .exists(a(i,1)) Then
n = n + 1
If n > Columns.Count Then
MsgBox "You have " & n & " different names" & vbLf & _
"This transposes 256 different names only"
Else
b(1, n) = a(i,1)
End If
Else
w = .item(a(i,1))
b(Val(w(1)) + 1, w(0)) = a(i,1)
w(1) = Val(w(1)) + 1
myMax = WorksheetFunction.Max(myMax, w(1))
.item(a(i,1)) = w
End If
Next
End With
Range("a1").Resize(myMax, n).Value = b``````

#### feef

##### New Member

Hi Jindon,

Thanks for your suggestions. I gave your code a try, here are the results:

Before:
Col A | Col B
Bob New Hampshire
Bob New Jersey
Bob New Mexico
Bob New York
Bob North Carolina
Bob North Dakota
Bob Ohio
Bob Oklahoma
Bob Oregon
Bob Pennsylvania
Bob Puerto Rico
Bob Rhode Island
Bob South Carolina
Bob South Dakota
Bob Tennessee
Bob Texas
Bob Utah
Bob Vermont
Bob Virginia
Bob Washington
Bob West Virginia
Bob Wisconsin
Bob Wyoming
Bob California
Bob
Bob
Christine Alabama
Christine Arizona
Christine Arkansas
Christine California
Christine Connecticut
Christine Delaware
Christine District of Columbia
Christine Florida
Christine Texas
Christine Utah
Christine Vermont
Christine Virginia
Christine Washington
Christine West Virginia
Christine Wisconsin
Christine Wyoming
Christine Alabama
Christine Arizona
Christine Arkansas
Christine California
Christine Connecticut
Christine Delaware
Christine District of Columbia
Christine Florida
Christine Georgia
Christine Hawaii
Christine Idaho
Christine Illinois
Christine Indiana
Christine Iowa
Christine Kansas
Christine Kentucky
Christine Louisiana
Christine Maine
Christine Maryland
Christine Massachusetts
Christine Michigan
Christine Minnesota
Christine Mississippi
Christine Missouri
Christine Montana
Christine
Christine
Christine New Hampshire
Christine New Jersey
Christine New Mexico
Christine New York
Christine North Carolina

After:
Col A ColB ColC ColD
(blank) (blank) (blank) Christine
(blank) (blank) (blank) Christine
(blank) (blank) (blank) Christine
(blank) (blank) (blank) Christine
(blank) (blank) (blank) Christine
(blank) (blank) (blank) Christine
(blank) (blank) (blank) Christine
(blank) (blank) (blank) Christine
(blank) (blank) (blank) Christine
(blank) (blank) (blank) Christine
(blank) (blank) (blank) Christine
(blank) (blank) (blank) Christine
(blank) (blank) (blank) Christine
(blank) (blank) (blank) Christine
(blank) (blank) (blank) Christine
(blank) (blank) (blank) Christine
(blank) (blank) (blank) Christine
(blank) (blank) (blank) Christine
(blank) (blank) (blank) Christine
(blank) (blank) (blank) Christine
Bob Vermont (blank) (blank)
Bob Virginia (blank) (blank)
Bob Washington (blank) (blank)
Bob West Virginia (blank) (blank)
Bob Wisconsin (blank) (blank)
Bob Wyoming (blank) (blank)
Bob California (blank) (blank)
Bob (blank) (blank)
Bob (blank) (blank)
Christine Alabama (blank) (blank)
Christine Arizona (blank) (blank)
Christine Arkansas (blank) (blank)
Christine California (blank) (blank)
Christine Connecticut (blank) (blank)
Christine Delaware (blank) (blank)
Christine District of Columbia (blank) (blank)
Christine Florida (blank) (blank)
Christine Texas (blank) (blank)
Christine Utah (blank) (blank)
Christine Vermont (blank) (blank)
Christine Virginia (blank) (blank)
Christine Washington (blank) (blank)
Christine West Virginia (blank) (blank)
Christine Wisconsin (blank) (blank)
Christine Wyoming (blank) (blank)
Christine Alabama (blank) (blank)
Christine Arizona (blank) (blank)
Christine Arkansas (blank) (blank)
Christine California (blank) (blank)
Christine Connecticut (blank) (blank)
Christine Delaware (blank) (blank)
Christine District of Columbia (blank) (blank)
Christine Florida (blank) (blank)
Christine Georgia (blank) (blank)
Christine Hawaii (blank) (blank)
Christine Idaho (blank) (blank)
Christine Illinois (blank) (blank)
Christine Indiana (blank) (blank)
Christine Iowa (blank) (blank)
Christine Kansas (blank) (blank)
Christine Kentucky (blank) (blank)
Christine Louisiana (blank) (blank)
Christine Maine (blank) (blank)
Christine Maryland (blank) (blank)
Christine Massachusetts (blank) (blank)
Christine Michigan (blank) (blank)
Christine Minnesota (blank) (blank)
Christine Mississippi (blank) (blank)
Christine Missouri (blank) (blank)
Christine Montana (blank) (blank)
Christine (blank) (blank)
Christine (blank) (blank)
Christine New Hampshire (blank) (blank)
Christine New Jersey (blank) (blank)
Christine New Mexico (blank) (blank)
Christine New York (blank) (blank)
Christine North Carolina (blank) (blank)

#### jindon

##### MrExcel MVP
I don't think I understand what you wanted.

Can you show me how you want the result using the sample of your 1st post?

#### West Man

##### Well-known Member
You posted virtually the same question earlier. Between the 2 posts, I believe that I understand what you desire. Assuming your data is in A2:B11, then in C2 try the following formula:
Code:
``=IF(\$A2=\$A1,"",IF(COUNTIF(\$A2:\$A11,\$A2)>COLUMN()-2,INDIRECT("\$b"&COLUMN()-2+ROW()),""))``
Drag across and down.

Replies
2
Views
141
Replies
0
Views
474
Replies
6
Views
259
Replies
0
Views
506
Replies
0
Views
458

1,181,420
Messages
5,929,801
Members
436,696
Latest member
Mr Rice

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back