Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

adjusting code transpose data

0

hello  

i  need  adjusting    this  code  works  for  3  column a,b,c   but   i  have  a,b,c,d    it   transposes   data  from vertical  to horizonal   i  try  to  adjust  but  i  failed  because  i  have  no  experience  in  vba    this is  the  code 

Sub TransposeColumns()
  Dim a As Variant, b() As Variant
  Dim dic As Object, i As Long, lin As Long, col As Long, n As Long

  Set dic = CreateObject("Scripting.Dictionary")
  a = Range("A2:e" & Range("A" & Rows.Count).End(xlUp).Row).Value2

  For i = 1 To UBound(a)
    dic(a(i, 1)) = dic(a(i, 1)) + 1
    If dic(a(i, 1)) > n Then n = dic(a(i, 1))
  Next
  n = (n * 2) + 1
  ReDim b(1 To dic.Count, 1 To n)
  dic.RemoveAll
  For i = 1 To UBound(a)
    If Not dic.exists(a(i, 1)) Then
      lin = lin + 1
      col = 1
      b(lin, col) = a(i, 1)
    Else
      lin = Split(dic(a(i, 1)), "|")(0)
      col = Split(dic(a(i, 1)), "|")(1) + 2
    End If
    dic(a(i, 1)) = lin & "|" & col
    b(lin, col + 1) = a(i, 2)
    b(lin, col + 2) = a(i, 3)
  Next
  Application.ScreenUpdating = False
  Range("g2").Resize(dic.Count, n).Value = b
  Application.ScreenUpdating = True
End Sub
Answer
Discuss

Discussion

The task appears to be different from conventional transposing. It's more like a horizontal presentation of data from a table. Unless you describe the result you want it won't be possible to help you. I suggest you add a manually created sample of how you want the data to be transposed to the workbook you attach to your question.
Variatus (rep: 4889) May 26, '20 at 12:34 am
hi, Variatus      thanks  for  your  replying  i  will  take   this  seriously in  the  future about describe   the  result  to be  the  matter  clear    indeed  done  answerd my  question    i  upolad  a  new  file    and  put  the  result  from g1 to z3 if  you   still interest
leopard (rep: 88) May 26, '20 at 8:04 am
Add to Discussion

Answers

0
Selected Answer

There are A LOT of ways to do this and the person who made this macro did not have to make it like this, so you should not be happy with them if they just gave you this and told you to change it!

This is probably want you want - I added just a few comments where changes were made:

Sub TransposeColumns()
  Dim a As Variant, b() As Variant
  Dim dic As Object, i As Long, lin As Long, col As Long, n As Long

  Set dic = CreateObject("Scripting.Dictionary")
  a = Range("A2:e" & Range("A" & Rows.Count).End(xlUp).Row).Value2

  For i = 1 To UBound(a)
    ' Add 1
    dic(a(i, 1)) = dic(a(i, 1)) + 2
    If dic(a(i, 1)) > n Then n = dic(a(i, 1))
  Next
  ' Add 1
  n = (n * 2) + 1 + 1

  ReDim b(1 To dic.Count, 1 To n)

  dic.RemoveAll
  For i = 1 To UBound(a)
    If Not dic.exists(a(i, 1)) Then
      lin = lin + 1
      col = 1
      b(lin, col) = a(i, 1)
    Else
      lin = Split(dic(a(i, 1)), "|")(0)
      ' Add 1
      col = Split(dic(a(i, 1)), "|")(1) + 3

    End If
    dic(a(i, 1)) = lin & "|" & col
    b(lin, col + 1) = a(i, 2)
    b(lin, col + 2) = a(i, 3)
    ' Add this for the new column
    b(lin, col + 3) = a(i, 4)

  Next
  Application.ScreenUpdating = False
  Range("g2").Resize(dic.Count, n).Value = b
  Application.ScreenUpdating = True
End Sub
Discuss

Discussion

hi, don   , thanks  for  your  adjusting   this  is   exactly  what  i'm looking  for but  it  missis  one  thing   , i  would  also  copy  topic  headers    if  you  don't  mind 
leopard (rep: 88) May 26, '20 at 7:17 am
It looks like you already put headers in the new file you uploaded. I'd just keep it like that and trim it after you do the macro because this kind of macro is a real pain to manage. Also, this is almost always a horrible way to organize your data, and headers is just one reason for this. Maybe you should just put this data table into a PivotTable and sort and filter through the data that way.
don (rep: 1989) May 26, '20 at 7:45 am
hi,  don    yes  ,  i  upload  the  a  new  file   and  put  the  result   as  ask  Variatus to understand  what  i  want  then  i  don't  notie  for  your  post     i  no  know  why you  don't  like  this  code   for  me  i  prefer  the  codes    because  dynamic  and  quick     thanks  for  your   time
leopard (rep: 88) May 26, '20 at 7:59 am
The reason that I mentioned the PivotTable is because it is very easy to slice and dice your data in that and make different views and analysis and all without any code. I'm thinking that this might be easier. How are you trying to analyze the data, as in, what is the purpose?
don (rep: 1989) May 26, '20 at 8:22 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login