Help!

florin1

New Member
Joined
May 24, 2014
Messages
6
Hello! I need to transpose some data from columns to rows like in the example below using VBA. It seems simple but for me, a beginner, it is a little bit difficult.

A 3
B 2
B 8
A 6
B 1

to:

A 3 6
B 2 8 1

I really hope that someone can help me!
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
florin1,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


Sample raw data (before, and, after the macro):


Excel 2007
ABCDEFG
1A3
2B2
3B8
4A6
5B1
6
Sheet1


After the macro:


Excel 2007
ABCDEFG
1A3A36
2B2B281
3B8
4A6
5B1
6
Sheet1


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
2. Open your NEW 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
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:
Sub ReorgData()
' hiker95, 05/24/2014, ME780017
Dim oa As Variant
Dim r As Long, lr As Long, nr As Long, nc As Long, n As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
oa = Range("A1:B" & lr)
Range("A1:B" & lr).Sort key1:=Range("A1"), order1:=1
nr = 0
For r = 1 To lr
  n = Application.CountIf(Columns(1), Cells(r, 1).Value)
  If n = 1 Then
    nr = nr + 1
    Cells(nr, 4).Resize(, 2).Value = Cells(r, 1).Resize(, 2).Value
  ElseIf n > 1 Then
    nr = nr + 1
    Cells(nr, 4).Value = Cells(r, 1).Value
    Cells(nr, 5).Resize(, n).Value = Application.Transpose(Range("B" & r & ":B" & r + n - 1).Value)
  End If
  r = r + n - 1
Next r
Range("A1:B" & lr) = oa
Columns.AutoFit
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.
 
Upvote 0
ace19852,

Anyway I saw this solution you produced and tweaked my demo file to include the VBA solution you produced here.

Thanks for the update.

Your attached workbook only contained formulae, and, it did not contain any macros.


So just a courtesy call to let you know and I hope it was ok to use your work

That is OK with me, as long as you keep the beginning macro text, ' hiker95, 05/24/2014, ME780017.
 
Upvote 0
Hi that great and yes I kept yr heading and I guess you looked on page 1 because the link at the end of page 2 has the macro in it !

I guess if I have any feedback re doesn't work like I hoped etc I can refer it back to the author :). Product liability is a bugger and the formula based solution was a bugger also lol

Cheers
 
Last edited:
Upvote 0
ace19852,

Hi that great and yes I kept yr heading and I guess you looked on page 1 because the link at the end of page 2 has the macro in it !

I just checked it out again. Thanks for using the macro to solve another request.


the formula based solution was a bugger also lol

Practice, practice, and, more of the same. (y)
 
Upvote 0
Thank you very much for your help.
I used the code and it works if the source data is written by hand in sheet1 or if it is pasted by hand from another location. When I try to use a code to brig the exact same data a error appears <Run-time error "13": Type mismatch> and debug underlines row:
<Cells(nr, 5).Resize(, n).Value = Application.transpose(Range("B" & r & ":B" & r + n - 1).Value)>

I don't understand why!

1.I use windows 8 and office 2013
2.I work from PC (personal laptop at home and desktop at the office)

Thank you very much.
 
Upvote 0
florin1,

Thank you very much for your help.

You are very welcome. Glad I could help.


When I try to use a code to brig the exact same data a error appears

Then there must be something in the raw data that is causing the problem.


I will have to see the present workbook where the macro failed.

You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.
 
Last edited:
Upvote 0
Thank you very much for your help.
I used the code and it works if the source data is written by hand in sheet1 or if it is pasted by hand from another location. When I try to use a code to brig the exact same data an error appears and debug underlines row:
Run_time error "13": Type mismatch
Underlined code: Cells(nr, 5).Resize(, n).Value = Application.transpose(Range("B" & r & ":B" & r + n - 1).Value)

I don't understand why!

1.I use windows 8 and office 2013
2.I work from PC (personal laptop at home and desktop at the office)

Thank you very much.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

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