Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
transpose variable array
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
transpose variable array
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transpose a variable length list | Excel Programming | |||
Transpose array sum | Excel Worksheet Functions | |||
Using Transpose from within VBA with 256 array | Excel Programming | |||
Transpose Array | Excel Discussion (Misc queries) | |||
variable transpose | Excel Programming |