Hi All,
I have found some solutions here that are very similar to what I need, but can't quite get them to work.
The issue is that I have sets of data arranged in a matrix style that I need to convert to stacked rows while keeping certain row values with each resulting new row. Tough to explain, but apparently a fairly common issue. If you can, please help me.
Attachment 1648
Code:Sub M_snb()
sn = Cells(1).CurrentRegion
sp = Cells(1).CurrentRegion.Resize(, 10)
c00 = 1
For j = 2 To UBound(sn)
c00 = c00 & Replace(String(17, "|"), "|", "|" & j)
Next
sq = Application.Index(sp, Application.Transpose(Split(c00, "|")), [transpose(row(1:10))])
For j = 2 To UBound(sq)
sq(j, 9) = "Brand " & (j - 2) Mod (17) + 1
sq(j, 10) = sn((j + 15) \ 17 + 1, 8 + (j - 2) Mod (17) + 1)
Next
Cells(40, 1).Resize(UBound(sq), UBound(sq, 2)) = sq
End Sub
Thank you so much snb. This is exactly what I needed. I can't tell you how much I appreciate your help!!!!!! And on a Friday afternoon no less!! %O
If you happen to see this again, I have a few follow up Q's.
1. Can you recommend some reading material or tutorials that I might use to educate my self on the writing of macros?
2. Can you easily modify this code to exclude rows that result in no value?
3. Can the code be modified to include an if/than type statement such that entries marked "Do Not Re-Accrue" are excluded from the result (thereby eliminating the step I made before posting that dataset, which was to filter on "Accrue/Re-Accrue" and then copy/paste values into another sheet?
Again, thank you so much. You just made my life a little easier (and month end close!!).
One other thing....when this dataset occupies many more rows, will this same code still work or will I need to modify a part of it to define the last row of the dataset?
Thank you.
ad 1. Green, Bullen, Bovey, Alexander Excel 2007 VBA; Programmer's Reference ISBN 978-0-470-04643-2
ad 2 & ad 3
ad 4. No need to adapt for larger datasets as long as no empty row/column makes part of it.Code:Sub M_snb()
sn = Cells(1).CurrentRegion
sp = Cells(1).CurrentRegion.Resize(, 10)
c00 = 1
For j = 2 To UBound(sn)
if(lcase(sn(j,1))="accrue" then c00 = c00 & Replace(String(17, "|"), "|", "|" & j)
Next
sq = Application.Index(sp, Application.Transpose(Split(c00, "|")), [transpose(row(1:10))])
For j = 2 To UBound(sq)
sq(j, 9) = "Brand " & (j - 2) Mod (17) + 1
sq(j, 10) = sn((j + 15) \ 17 + 1, 8 + (j - 2) Mod (17) + 1)
Next
Cells(40, 1).Resize(UBound(sq), UBound(sq, 2)) = sq
End Sub.
Again, thank you snb, you're great. I appreciate your time and help very much.
I have one more dilemma that you may be able to address.
It seems you have the word "Brand" hardcoded in the code to add sequential numerals thus leading to the headings in this example..... "Brand 1..Brand 2...Brand 3..) but I actually need the code to use the exact name that is populated across the column headers because in practice those place holders will actually be named with real product brands.
.
Can you give me one more revamp with references to the actual names populating the columns?
Thank you Sir/Ma'am. Again, much appreciate your time and effort.
From where do those 'actual names' originate ?
Quote:
Originally Posted by snb
Hi snb....the 'actual names' are populated across the column headers. In this example I just typed in Brand1, Brand2, etc...but in practice these will be actual products names like Gorilla, BigOne, Masher...etc
The code will need to obtain the names from the text in the first row along the columns.
God I hope you can help me bro. I ran out of time and need to do this work tomorrow.
Thank you so much.
Did you try:
Code:Sub M_snb()
sn = Cells(1).CurrentRegion
sp = Cells(1).CurrentRegion.Resize(, 10)
c00 = 1
For j = 2 To UBound(sn)
If (LCase(sn(j, 1))) = "accrue" Then c00 = c00 & Replace(String(17, "|"), "|", "|" & j)
Next
sq = Application.Index(sp, Application.Transpose(Split(c00, "|")), [transpose(row(1:10))])
For j = 2 To UBound(sq)
sq(j, 9) = sn(1, 8 + (j - 2) Mod 17 + 1)
sq(j, 10) = sn((j + 15) \ 17 + 1, 8 + (j - 2) Mod (17) + 1)
Next
Cells(40, 1).Resize(UBound(sq), UBound(sq, 2)) = sq
End Sub