Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transposing data into new format
Hi,
Am trying to create a macro in Excel that will take different datasets and transpose data into a different format. I have species names and counts in sets across columns. A and B are a set (Location A), C and D (Location B) a set, etc. Would like to have all the information in A and B only, with row C location data. How do I take the data out of C and D or E and F and paste it below A and B? The data varies across several sheets and while the columns A and B have the same number of entries in one wheet, they maybe different in another sheet. Example (Have) A B C D E F 1 a 1 f 6 h 8 2 b 2 g 7 3 c 3 4 d 4 5 e 5 Want A B C 1 a 1 Location A 2 b 2 Location A 3 c 3 Location A 4 d 4 Location A 5 e 5 Location A 6 f 6 Location B 7 g 7 Location B 8 h 8 Location C |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transposing data into new format
On Thu, 31 Jul 2008 13:10:07 -0700, EmmieLou
wrote: A B C D E F 1 a 1 f 6 h 8 2 b 2 g 7 3 c 3 4 d 4 5 e 5 Sub TransData() Dim i As Long, j As Long Dim vaData As Variant Dim rOutput As Range Dim lCount As Long vaData = Sheet1.UsedRange.Value2 Set rOutput = Sheet1.Cells(Sheet1.UsedRange.Rows.Count + 10, 1) For i = LBound(vaData, 2) To UBound(vaData, 2) Step 2 For j = LBound(vaData, 1) To UBound(vaData, 1) If Len(vaData(j, i)) 0 Then rOutput.Offset(lCount, 0).Value = vaData(j, i) rOutput.Offset(lCount, 1).Value = vaData(j, i + 1) rOutput.Offset(lCount, 2).Value = "Location " & Chr$((i \ 2) + 65) lCount = lCount + 1 End If Next j Next i End Sub Watch for line wrap. -- Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transposing data? | Excel Worksheet Functions | |||
Transposing data | Excel Worksheet Functions | |||
transposing vertical data to horizontal with varying amount of data | Excel Discussion (Misc queries) | |||
Transposing data | Excel Discussion (Misc queries) | |||
Transposing Data | Excel Programming |