Transposing data from row to column.(Solved)

Hi All,

I am new to the forum and I had tried to find out the resolution but was not successful.
This related to excel I have two columns assuming Col A and Colb mentioned below.I want col b data to be in horizontal against the col A and Col A should be unique.
Col A Col B
A 1
A 2
A 3
B 2
B 1
B 4
C 1
C 4
C 4
D 6
E 5
E 1
E 5

Thanks
Jags

Welcome to Excel Forums. When you have a particular problem it is good to upload a sample workbook showing how the data appears before and after the code is run. However if I understand you correctly this should do what you want

Sub arrangeFnc()
With Range("A1")
a = .CurrentRegion
.CurrentRegion.Clear
End With
x = 1
Dim d As Object
Set d = CreateObject("Scripting.Dictionary")
Dim i As Long
For i = LBound(a) To UBound(a)
    d(a(i, 1)) = 1
Next i
Dim v As Variant
For Each v In d.Keys()
Cells(x, 1) = v
For i = LBound(a) To UBound(a)
If a(i, 1) = v Then
Cells(x, Cells(x, Columns.Count).End(xlToLeft).Column + 1) = a(i, 2)
End If
Next
x = x + 1
Next v
End Sub

See attached workbook and run the macro arrangeFnc

jagspundir,

Welcome to the Excel Forum.


Detach/open workbook ReorgData colA Unique to colD colB Transpose to EFG - jagspundir - EF843402 - SDG10.xls and run the ReorgData macro.

The macro will reorganize your sorted/grouped raw data in columns A and B into columns DEFG.


If you want to use the macro on another workbook:


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReorgData()
' stanleydgromjr, 07/06/2012
' http://www.excelforum.com/excel-programming/843402-transposing-data-from-row-to-column.html
Dim r As Long, lr As Long, nr As Long, sc As Long, n As Long
Application.ScreenUpdating = False
nr = 1: sc = 4
lr = Cells(Rows.Count, 1).End(xlUp).Row
For r = 1 To lr
  n = Application.CountIf(Columns(1), Cells(r, 1).Value)
  Cells(nr, sc) = Cells(r, 1)
  Cells(nr, sc + 1).Resize(, n).Value = Application.Transpose(Cells(r, 2).Resize(n).Value)
  r = r + n - 1
  nr = nr + 1
Next r
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the ReorgData macro.

No problem - glad it worked to your specs