Transpose at every change in name

F

feef

New Member
Joined
Apr 1, 2005
Messages
14
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
Adam Alabama
Adam Alaska
Adam Arizona
Adam Arkansas
Adam California
Adam Colorado
Adam Connecticut
Adam Delaware
Adam District of Columbia
Bob Alabama
Bob Alaska
Bob Arizona
Bob Arkansas
Bob California
Bob Colorado
Bob Connecticut
Bob Delaware
Christie Alabama
Christie Alaska
Christie Arizona
Christie Arkansas
Christie California
Christie Colorado
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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
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)
                    .add a(i,1), Array(n, 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
 
Upvote 0
thanks for your attempt

Hi Jindon,

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

Before:
Col A | Col B
Adam Alabama
Adam Alaska
Adam Arizona
Adam Arkansas
Adam California
Adam Colorado
Adam Connecticut
Adam Delaware
Adam District of Columbia
Adam Florida
Adam Georgia
Adam Hawaii
Adam Idaho
Adam Illinois
Adam Indiana
Adam Iowa
Adam Kansas
Adam Kentucky
Adam Louisiana
Adam Maine
Adam Maryland
Adam Massachusetts
Adam Michigan
Adam Minnesota
Adam Mississippi
Adam Missouri
Adam Montana
Adam
Adam
Adam Nebraska
Adam Nevada
Adam New Hampshire
Adam New Jersey
Adam New Mexico
Bob Nebraska
Bob Nevada
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 Alaska
Christine Arizona
Christine Arkansas
Christine California
Christine Colorado
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 Alaska
Christine Arizona
Christine Arkansas
Christine California
Christine Colorado
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 Nebraska
Christine Nevada
Christine New Hampshire
Christine New Jersey
Christine New Mexico
Christine New York
Christine North Carolina

After:
Col A ColB ColC ColD
(blank) Adam Bob Christine
(blank) Adam Bob Christine
(blank) Adam Bob Christine
(blank) Adam Bob Christine
(blank) Adam Bob Christine
(blank) Adam Bob Christine
(blank) Adam Bob Christine
(blank) Adam Bob Christine
(blank) Adam Bob Christine
(blank) Adam Bob Christine
(blank) Adam Bob Christine
(blank) Adam Bob Christine
(blank) Adam Bob Christine
(blank) Adam Bob Christine
(blank) Adam Bob Christine
(blank) Adam Bob Christine
(blank) Adam Bob Christine
(blank) Adam Bob Christine
(blank) Adam Bob Christine
(blank) Adam Bob Christine
(blank) Adam Bob Christine
(blank) Adam Bob Christine
(blank) Adam Bob Christine
(blank) Adam Bob Christine
(blank) Adam Bob Christine
(blank) Adam Bob Christine
(blank) Adam Bob Christine
(blank) Adam Bob Christine
(blank) Adam (blank) Christine
(blank) Adam (blank) Christine
(blank) Adam (blank) Christine
(blank) Adam (blank) Christine
(blank) Adam (blank) Christine
(blank) Adam (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
(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 Alaska (blank) (blank)
Christine Arizona (blank) (blank)
Christine Arkansas (blank) (blank)
Christine California (blank) (blank)
Christine Colorado (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 Alaska (blank) (blank)
Christine Arizona (blank) (blank)
Christine Arkansas (blank) (blank)
Christine California (blank) (blank)
Christine Colorado (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 Nebraska (blank) (blank)
Christine Nevada (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)
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Random value from table with qualifier
Replies
1
Views
163
DRSteele
DRSteele
rjplante
  • Solved
Change from Set as Default to Set as Active printer
Replies
1
Views
195
NdNoviceHlp
NdNoviceHlp
E
  • Question
Name Change that does not change class
2 3
Replies
21
Views
847
Anthony47
A
L
  • Question
Help with VBA code
Replies
0
Views
329
LordSnow548
L
M
  • Solved
Proportionately increase percentages so that they add up to 100%
Replies
2
Views
267
marshak
M

Forum statistics

Threads
1,214,552
Messages
6,120,172
Members
448,948
Latest member
spamiki

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top