Create New Sheet , multiple rows per original sheet row

[Excelconfused1]

I am only just starting to learn how to do VBA Excel macrosand I would like to create a macro to do the following:


If I have rows of data as follows (simplified), in a sheet called "Dependencies":

Column: A B C D E F G

Headings: Service Dep1 Cat1 Dep2 Cat2 Dep3 Cat3.

Row 1 A B U C U E D

Row 2 B A U G D H D



I would like to copy the data to new rows in a new sheet inthe workbook (sheet name "Expanded" as follows:


Service Dep Cat
A B U
A C U
A E D
B A U
B G D
B H D

In other words, separate out the Cat / Dep occurrences ineach row from "Dependencies" so each Cat/ Dep occurrence appears in its own row in the "Expanded" sheet.

I have 50 occurrences for each row. I only want to createentries in the new sheet where they are not blank in the original sheet.

There are about 200 rows I need to convert in this way.

How would I code this?

Thanks in advance.

 

[ValuationMan]

I don't follow your description of the data. Is ABUCUED all in a single cell? Is that the only data you want separated into a new worksheet? Do all of the departments have the same number of letters?

 

[Excelconfused1]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Service
[/TD]
[TD]Dep1
[/TD]
[TD]Cat1
[/TD]
[TD]Dep2
[/TD]
[TD]Cat2
[/TD]
[TD]Dep3
[/TD]
[TD]Cat3
[/TD]
[/TR]
[TR]
[TD]AAA
[/TD]
[TD]BBB
[/TD]
[TD]D
[/TD]
[TD]CCC
[/TD]
[TD]U
[/TD]
[TD]EEE
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]BBB
[/TD]
[TD]EEE
[/TD]
[TD]U
[/TD]
[TD]FFF
[/TD]
[TD]D
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Apologies.

Formatting didn't work in my original post....

All tabs and extra spaces were removed....

Basically, I need a macro to convert the sheet above to the following:

 

[Excelconfused1]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Service
[/TD]
[TD]Dep
[/TD]
[TD]Cat
[/TD]
[/TR]
[TR]
[TD]AAA
[/TD]
[TD]BBB
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]AAA
[/TD]
[TD]CCC
[/TD]
[TD]U
[/TD]
[/TR]
[TR]
[TD]AAA
[/TD]
[TD]EEE
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]BBB
[/TD]
[TD]EEE
[/TD]
[TD]U
[/TD]
[/TR]
[TR]
[TD]BBB
[/TD]
[TD]FFF
[/TD]
[TD]D
[/TD]
[/TR]
</tbody>[/TABLE]

There are about 200 rows in the source table with up to 50 occurences of Dep / Cat per row,
Text is variable length in Service and Dep.

Thanks in advance.