I think this is what you want:
Sub settupp()
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
s1.Activate
n = Cells(Rows.Count, 1).End(xlUp).Row
k = 1
For i = 1 To n
fst = Cells(i, 1).Value
For j = 1 To 5
snd = Cells(i, j).Value
s2.Cells(k, 1) = fst
s2.Cells(k, 2) = snd
k = k + 1
Next
Next
End Sub

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"JBG" wrote:

Hi,
Please, I need some help with the following formula/macro. I want the data
in columns Cat1, Cat2 and Cat3 to be transposed into one single column
("List" column on the left), but the lenght of the array being transposed
should vary based on the Cat Count column. I am putting an example below in
case it helps.
Thanks very much.

List Cat count Cat 1 Cat 2 Cat 3
A 1 A
B 2 B A
A 0
0
B 3 B A C
A 0
C 0