Macro to help me transpose this information?

suri tamna

New Member
Joined
Oct 8, 2019
Messages
1
I searched for a similar situation to mine and didn't see one (apologies if one exists and I missed it!)

I have to format a long list of managers and their employees for mass emails, and the lists I get from our database are:


Manager 1Employee A
Manager 2Employee B
Manager 2Employee C
Manager 2Employee D
Manager 3Employee E
Manager 3Employee F
Manager 4Employee G
Manager 4Employee H

<tbody>
</tbody>

But I need the data to be formatted like this:

Manager 1Employee A
Manager 2Employee BEmployee CEmployee D
Manager 3Employee EEmployee F
Manager 4Employee GEmployee H

<tbody>
</tbody>


So, I'd like to sort all employees who report to the same manager so that they appear on the row next to that manager rather than in a column with the manager's name listed as many times as there are employees reporting to that manager. I sometimes have situations where there are 20 people reporting to a manager, and I have thousands of records to transpose, so doing this manually is a real bummer.

Thank you in advance to any solutions, and let me know if I can clarify anything.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Welcome to the MrExcel board!

If the list is in columns A:B starting at row 2 and results can go in columns D, E, F, ... then give this a try in a copy of your workbook.

Rich (BB code):
Sub Rearrange()
  Dim d As Object
  Dim a As Variant
  Dim i As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  a = Range("A2", Range("B" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    d(a(i, 1)) = d(a(i, 1)) & "," & a(i, 2)
  Next i
  With Range("D2:E2").Resize(d.Count)
    .Value = Application.Transpose(Array(d.Keys, d.Items))
    .Columns(2).TextToColumns DataType:=xlDelimited, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 9))
    .CurrentRegion.Columns.AutoFit
  End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

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