Copy a combination of data out of a long list

[Michael Doleschal]

Hello experts!

I have a problem with text file based data list which I need to copy into a certain format.
I am not a VBA expert but perhaps somebody can help me.

I do have a list which has following format.
Column 1 has the ID inside - which is a single value.
Then have column 2 to column 282 which could be filled but must not.
For sure the first column (column 2) is filled.
So there could be a combination between ID + 1 value up t ID + 281 values.

My problem is now that I need to create an overview which I have a 1:1 relation:

ID + 1 value

My text looks like:

ID column - 1st Value column - 2nd - Value column - 3rd Value column - .....
Possible values are:
AB - M31 - M43 - M44
CD - M21
DD - M12 - M99

What I would like to get out into another table and new columns is:

AB - M31
AB - M43
AB - M44
CD - M21
DD - M12
DD - M99

Thanks for any support or any idea I can handle this issue.

Thanks
Kind Regards
Michael D.

 

[Abdul Matheen]

Hi Michael,

See the below VBA code will fix your issue. Copy the below code into your workbook vba module and change the references as per your workbook references.

Code:

Sub Matheen()

Dim i As Integer
Dim j As Integer
Dim ctr As Integer
Dim id As String
Dim val As String
ctr = 2

Sheets("sheet1").Select
i = 1
Do While Cells(i, 1) <> ""
Sheets("sheet1").Select
id = Cells(i, 1).Value
For j = 2 To 282
Sheets("sheet1").Select
If Cells(i, j).Value = "" Then
Exit For
Else
val = Cells(i, j).Value
Sheets("sheet2").Select
Cells(ctr, 1).Value = id
Cells(ctr, 2).Value = val
ctr = ctr + 1
End If
Next j
i = i + 1
Loop

MsgBox "Task Completed!!!"
End Sub

 

[Michael Doleschal]

Hello!

Thanks a lot

It solved my problem - fantastic help!

Thanks a lot
Kind Regards
Michael