Solved How to use transpose macro?

April 27, 2016 at 11:11:56
Specs: Windows 7
@derbydad03

Hi,

I was able to replicate your example perfectly. Thanks for the explanation.
I am trying to transpose the below using the your example but i keeping hitting a wall and not getting any results returned.
I only have two columns and i think that's the problem - i'm just not modifying the code correctly.

item compmfgpn
_0003L88C _0003L88C
_0003L88C 0003L88C
_0004C88C _0004C88C
_0004C88C 0004C88C
_0004E88C 0004E88C
_0005E88CC 0005E88CC
_000601 601
_000601 _000601
_000614 _000614
_000614C _000614C
_000614C 000614C
_000615 615
_000615 _000615
_000617 617
_000617 _000617
_000801 801
_000801 _000801
_000807 807
_000807 _000807
_000814 814
_000814 _000814
_000815 815
_000815 _000815
_000817 _000817

Your help is really appreciated.


See More: How to use transpose macro?

Report •

✔ Best Answer
April 27, 2016 at 13:32:01
After placing the Item numbers in D2:D6 using the Advanced Filter feature, run this code:

Sub TransposeDataV_1()
 For Each myItem In Range("D2:D6")
  nxtCol = 5
  With Range("A1:A10")
   Set c = .Find(myItem, LookIn:=xlValues)
     If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            Cells(myItem.Row, nxtCol) = c.Offset(0, 1)
            nxtCol = nxtCol + 1
          Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
     End If
  End With
 Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
April 27, 2016 at 11:33:12
re: "I was able to replicate your example perfectly. "

I am approaching 5000 posts in this forum. If you think that I can remember every "example" that I have posted, you are giving me way more credit than I deserve. ;-)

re: "Thanks for the explanation."

I do like to explain things!

I think it would help if you could post the "example" of which you speak or perhaps a link to the thread in which I posted the "example". Perhaps that would jog this befuddle memory.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
April 27, 2016 at 11:57:07
My apologies! Here is the link.

http://www.computing.net/answers/of...


Report •

#3
April 27, 2016 at 12:07:29
OK, so now that I reviewed the other thread, I'm slightly less befuddled, but only slightly.

You say that you are "trying to transpose the below".

I don't understand what you are "trying to transpose".

I have no idea what output you are trying to obtain.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
April 27, 2016 at 12:17:10
So from the above data (I have 127,885 rows like that).
Column A contains item numbers, there are duplicates. And Column B contains manufacturer numbers that link to the item numbers.

I am trying to have all the duplicates removed from column A and display the manufacturer numbers horizontally. Much like my quick copy and paste that i have done below.

ITEM compmfgpn 1 compmfgpn 2
_0003L88C _0003L88C 0003L88C
_0004C88C _0004C88C 0004C88C
_0004E88C 0004E88C
_0005E88CC 0005E88CC
_000601 601 _000601
_000614 _000614
_000614C _000614C 000614C
_000615 615 _000615

I apologize for not being able to explain as well as you. :)


Report •

#5
April 27, 2016 at 12:34:26
Still lost...

As far as I can tell, you've added a third column.

Please click on the blue line at the bottom of this post and read the instructions on how to post example data in this forum. You will be using the pre tags to align your data. Please make sure that you repost your example data using the following criteria:

1 - Post a brief example of your input data, enough to show duplicates, anomalies, etc.
2 - Post the desired output based on the example input.

(Basically a "before and after" example)

3 - Make sure you include column letters and row numbers in both examples.

If you'll refer to Response #1 of the thread that you linked to, you will see an example of how your data should look once you apply the pre tags.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
April 27, 2016 at 12:55:56
Hopefully this should clear things up.
INPUT DATA

    A                 B  
1  item	          compmfgpn
2 _0003L88C	  _0003L88C
3 _0003L88C	   0003L88C
4 _0004C88C	  _0004C88C
5 _0004C88C	   0004C88C
6 _0004E88C	   0004E88C
7 _0005E88CC	  0005E88CC
8 _0101-002008    9736-005
9 _0101-002008    _0101-002008
10 _0101-002008   0101-002008

OUTPUT DATA

    D               E                 F                G
1  item	        compmfgpn 1	 compmfgpn 2       compmfgpn 3
2 _0003L88C     _0003L88C	  0003L88C
3 _0004C88C     _0004C88C	  0004C88C
4 _0004E88C     0004E88C	
5 _0005E88CC    0005E88CC	
6 _0101-002008  9736-005         _0101-002008     0101-002008






Report •

#7
April 27, 2016 at 13:32:01
✔ Best Answer
After placing the Item numbers in D2:D6 using the Advanced Filter feature, run this code:

Sub TransposeDataV_1()
 For Each myItem In Range("D2:D6")
  nxtCol = 5
  With Range("A1:A10")
   Set c = .Find(myItem, LookIn:=xlValues)
     If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            Cells(myItem.Row, nxtCol) = c.Offset(0, 1)
            nxtCol = nxtCol + 1
          Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
     End If
  End With
 Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
April 27, 2016 at 13:49:34
You are a rock star! Thank you so much for your help (and patience).

It worked perfectly on the above and is now crunching through my many thousands of lines.

Thanks again!


Report •

#9
April 27, 2016 at 14:33:32
I should have noticed the large number of rows.

The code would run much quicker if it wasn't updating the sheet each time it places a value in a cell.

This one extra instruction right at the beginning would probably have helped a lot.

Application.ScreenUpdating = False

The code won't look like it's doing anything and then when it's done, all of the values would magically appear in the cells.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question