Move cell data from columns to rows

steallan

Active Member
Joined
Oct 20, 2004
Messages
304
Hi

Im after some help with a macro as im not the best when it comes to VBA.

I have values in one column and corresponding values in the next column. I wnat to move the corresponding values to a new row and have them in the row instead of the column.

hear is an example,
Workpackage 020305.csv
BCDEFGHIJ
1MHB4200AAH-A-0-12200
2SWD6900AAH-A-0-12201MHB6000MHB7000MHB4300MHB4200MHB3100MHB2200
3MHB6000AAH-A-0-12201
4MHB7000AAH-A-0-12201
5MHB4300AAH-A-0-12201
6MHB4200AAH-A-0-12201
7MHB3100AAH-A-0-12201
8MHB2200AAH-A-0-12201
9MHB6000AAH-A-0-12201scanMHB6000MHB7000SWD6500SWD6900
10MHB7000AAH-A-0-12201scan
11SWD6500AAH-A-0-12201scan
12SWD6900AAH-A-0-12201scan
13MHB6000AAH-A-0-SK0015
14MHB4200AAH-A-0-SK0015
15MHB7000AAH-A-0-SK0015
16MHB6000AAH-A-0-SK0016
17MHB4200AAH-A-0-SK0016
18MHB7000AAH-A-0-SK0016
19MHB6000AAH-A-0-SK0017
20MHB4200AAH-A-0-SK0017
21MHB7000AAH-A-0-SK0017
22MHB6000AAH-A-0-SK0018
Workpackage 020305
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

steallan

Active Member
Joined
Oct 20, 2004
Messages
304
Thx
Is there a way to use this to move the data for each value in column 2?
Do you know what i mean, can you see above all the values next to a AAH-A-0-12201 were transposed next to one AAH-A-0-12201 value, then all the values next to a AAH-A-0-12201scan were transposed next to AAH-A-0-12201scan

sorry about the image above its not turned out very clear
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Can you explain further what you are trying to do?

It really isn't clear.

By the way check out the link at the top of the forum about the &nbsp problem when posting.
 

steallan

Active Member
Joined
Oct 20, 2004
Messages
304
Workpackage.csv
ABCDEFGHI
104A088-002U1600U1600
2AAH-A-0-12200MHB4200MHB4200
3AAH-A-0-12201MHB2200MHB2200MHB3100MHB4200MHB4300MHB6000MHB7000SWD6900
4AAH-A-0-12201MHB3100
5AAH-A-0-12201MHB4200
6AAH-A-0-12201MHB4300
7AAH-A-0-12201MHB6000
8AAH-A-0-12201MHB7000
9AAH-A-0-12201SWD6900
10AAH-A-0-12201scanMHB6000MHB6000MHB7000SWD6500SWD6900
11AAH-A-0-12201scanMHB7000
12AAH-A-0-12201scanSWD6500
13AAH-A-0-12201scanSWD6900
14AAH-A-0-SK0015MHB4200MHB4200MHB6000MHB7000
15AAH-A-0-SK0015MHB6000
16AAH-A-0-SK0015MHB7000
17AAH-A-0-SK0016MHB4200MHB4200MHB6000MHB7000
18AAH-A-0-SK0016MHB6000
19AAH-A-0-SK0016MHB7000
20AAH-A-0-SK0017MHB4200
21AAH-A-0-SK0017MHB6000
22AAH-A-0-SK0017MHB7000
23AAH-A-0-SK0018MHB4200
24AAH-A-0-SK0018MHB6000
25AAH-A-0-SK0018MHB7000
26AAH-A-0-SK0019MHB4200
Workpackage


Hopefully thats clearer. can you see that for each file name (column A) there are values next to it in column B. these are assigned values to that file name. there can be any amount of none.

i need these values in a row instead of columned so i can cut them to another sheet.

but this list is 10000 + long and gets added to every day.

this is actually a different approach to another problem that noone could help me with

http://www.mrexcel.com/board2/viewtopic.php?t=133874&highlight=

dont no if that helps make it clearer.

thx for any ideas you've got
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

Hi,
try
Code:
Sub test()
Dim dic As Object, i As Long, r As Range, txt
Set dic = CreateObject("Scripting.Dictionary")
With ActiveSheet
    For Each r In .Range("a1", .Range("a65536").End(xlUp))
        If Not dic.Exists(r.Value) Then
            dic.Add r.Value, r.Offset(, 1).Value & ","
        Else
            dic.Item(r.Value) = dic.Item(r.Value) & r.Offset(, 1).Value & ","
        End If
    Next
    x = dic.keys
    For i = LBound(x) To UBound(x)
        For Each r In .Range("a1", .Range("a65536").End(xlUp))
            If r.Value = x(i) Then
                txt = Left(dic.Item(r.Value), Len(dic.Item(r.Value)) - 1): a = Split(txt, ",")
                r.Offset(, 1).Resize(, UBound(a) + 1).Value = a
                Exit For
            End If
        Next
    Next
End With
End Sub
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
scrupul0us,

Thanks.

That is one the most delilghtfull comment.

rgds,
jindon
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,328
Members
414,054
Latest member
Sameer50

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
Top