copying a vertical list to a horizontal (not transposing)

[milo1984]

Hi

I have a list of ID's (a sample below) which have languages attached to that ID listed next to them. Most ID's have multiple languages.

I need to basically have these ID's and their languages on one line in another sheet. I'm sure a macro is the way forward but I'm not sure how to do it.

ID TYPE_NAME
516284 Audio Polish English Mix
516284 Audio Polish
516666 Audio Russian
516667 Audio Russian
516668 Audio Polish
516668 Audio Polish English Mix
516669 Audio Polish
516669 Audio Polish English Mix
517286 Audio Czech
517286 Audio Hungarian
517306 Audio Polish
517306 Audio Polish English Mix
517307 Audio Polish
517307 Audio Polish English Mix


Any help would be greatly appreciated.

Many thanks in advance.

[rylo]

Hi

Make sure you have a blank sheet in your workbook called sheet2, select the data sheet and run

Sub aaa()
  Dim OutSH As Worksheet
  Set OutSH = Sheets("Sheet2")
  OutSH.Range("A1:B1").Value = Range("A1:B1").Value
  
  For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
    Set findit = OutSH.Range("A:A").Find(what:=Cells(i, 1))
    If findit Is Nothing Then
      OutSH.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = Cells(i, 1)
      Set findit = OutSH.Range("A:A").Find(what:=Cells(i, 1))
    End If
    OutSH.Cells(findit.Row, Columns.Count).End(xlToLeft).Offset(0, 1).Value = Cells(i, 2).Value
  
  Next i
End Sub

HTH

rylo

[milo1984]

Thank you so much for your help.

This was exactly what I was looking for.

All the best.

[milo1984]

Hi Rylo.

I have one more question, which, if you would prefer I can post as a new question, but I thought I would ask it to you first seeing as it is connected to the above post.

If I named the headings in 'Sheet 2' to state the various different languages, is it possible to copy the various different languages so that they appear in a cell based on their header? I’ve tried to show an example below in the attachment.


MATERIAL__MAT_ID TRACK_TYPE__TYPE_NAME
516284 Audio Polish English Mix
516284 Audio Polish
516666 Audio Russian
516667 Audio Russian
516668 Audio Polish
516668 Audio Polish English Mix
516669 Audio Polish
516669 Audio Polish English Mix
517286 Audio Czech
517286 Audio Hungarian

Example.xlsx

Many thanks in advance.

[rylo]

Hi

You could do something like that if the column headings were exactly the same as the language. Using your example in #4 that would work except for the Audio Czech which is in the AUDIO RUSSIAN column. If you really do have multiple languages in a single column, then you would need some form of translation table to work out which column to put them in. So was the Audio Czech a mistake, or do you need the translation table?

rylo

[milo1984]

Hi Rylo

The Audio Czech was a mistake and should have its own column. Sorry, I prepared it in a hurry. There would be no multiple languages in a single column.

I can't thank you enough for this.

[rylo]

Hi

Again, have a blank sheet called Sheet2, and run this on the raw data. It will build the relevant headings, and put the data in the right place (famous last words!)

Sub aaa()
  Dim OutSH As Worksheet
  Set OutSH = Sheets("Sheet2")
  OutSH.Range("A1").Value = "MATERIAL__MAT_ID"
  For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
    Set findit = OutSH.Range("A:A").Find(what:=Cells(i, 1))
    Set findcol = OutSH.Rows("1:1").Find(what:=Cells(i, 2), lookat:=xlWhole)
    If findit Is Nothing Then
      OutSH.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = Cells(i, 1)
      Set findit = OutSH.Range("A:A").Find(what:=Cells(i, 1))
    End If
    If findcol Is Nothing Then
      OutSH.Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1).Value = Cells(i, 2)
      Set findcol = OutSH.Rows("1:1").Find(what:=Cells(i, 2), lookat:=xlWhole)
    End If
    
    OutSH.Cells(findit.Row, findcol.Column).Value = Cells(i, 2).Value
  Next i
End Sub

rylo

[milo1984]

Hi Rylo.

That worked perfectly. Thank you so much.

One last question. I have been informed that the columns that the data will actually be in is Column B and Column F (instead of A and B). Is it easy enough to adapt the formula to cope with this or do I need to run a macro to rearrange the columns into A and B? I tried to play around with it but could not get it to pick up the source data in B and F. If it is too much hassle, then please don't worry. You have helped me more than enough as it is.

Many thanks in advance.

[rylo]

Hi

You should be able to adapt the code to do this. Can you set up an example file (saves me the hastle and makes sure that the format is really right) with both the raw data and the expected output and I'll have a look.

rylo

[milo1984]

Hi Rylo


I have attached a workbook below. I hope that this is clear. Tab 1 (Data), Tab 2 (Output after macro).



Test1.xlsx

Many thanks, once again for all your help. I really appreciate it.

[rylo]

Hi

Set up for you example file with the output going to sheet3

Sub aaa()
  Dim OutSH As Worksheet
  Set OutSH = Sheets("Sheet3")
  OutSH.Range("A1").Value = "MATERIAL__MAT_ID"
  For i = 2 To Cells(Rows.Count, 2).End(xlUp).Row
    Set findit = OutSH.Range("A:A").Find(what:=Cells(i, 2))
    Set findcol = OutSH.Rows("1:1").Find(what:=Cells(i, 6), lookat:=xlWhole)
    If findit Is Nothing Then
      OutSH.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = Cells(i, 2)
      Set findit = OutSH.Range("A:A").Find(what:=Cells(i, 2))
    End If
    If findcol Is Nothing Then
      OutSH.Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1).Value = Cells(i, 6)
      Set findcol = OutSH.Rows("1:1").Find(what:=Cells(i, 6), lookat:=xlWhole)
    End If
    
    OutSH.Cells(findit.Row, findcol.Column).Value = Cells(i, 6).Value
  Next i
End Sub

rylo