Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I have data that is returned from an AS400 query that I can transfer into
Excel. The A column lists all products that are found in the return from my query. A typical array would be like: A1 = Product 1 / A2 = Product 2 / A3 = Product 2 / A4 = Product 2 / A5 = Product 3 / A6 = Product 3 / A7 = Product 4 / A8 = Product 5. The reason for the differing number of occurances might be store related. Product 1 is in a single store, therefore requiring only 1 row of data. Product 2 is found in 3 stores and therefore I need all 3 rows of data. etc. In the above example, I'd like to use and Excel function, argument, and/or macro thas has the effect of inserting 2 blank lines between Row 1 and Row 2, moving the Row 2 contents to Row 4. Then I'd like the functionality to spot the next product name-change and insert 2 rows there, etc. all the way to the bottom of the data. I'd wind up with; Product 1 Product 2 Product 2 Product 2 Product 3 Product 3 Product 4 My sincere thanks if you can show me how to do this. |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hey Confused/Houston !! Former CorpusChristi Guy here !!
Paste the below code in a standard module Sub InsertRow_A_Chg() Dim Lrow As Long, vcurrent As String, i As Long '// find last used cell in Column A Lrow = Cells(Rows.Count, "A").End(xlUp).Row '// get the value of that cell in Column A (column 1) vcurrent = Cells(Lrow, 1).Value '// rows are inserted by looping from bottom up For i = Lrow To 2 Step -1 '<<< goes up to row 2 - change if needed If Cells(i, 1).Value < vcurrent Then vcurrent = Cells(i, 1).Value Rows(i + 1).Resize(2).Insert '<< the 2 represent number of rows to insert End If Next i End Sub Hope this helps... Jim May "ConfusedNHouston" wrote: I have data that is returned from an AS400 query that I can transfer into Excel. The A column lists all products that are found in the return from my query. A typical array would be like: A1 = Product 1 / A2 = Product 2 / A3 = Product 2 / A4 = Product 2 / A5 = Product 3 / A6 = Product 3 / A7 = Product 4 / A8 = Product 5. The reason for the differing number of occurances might be store related. Product 1 is in a single store, therefore requiring only 1 row of data. Product 2 is found in 3 stores and therefore I need all 3 rows of data. etc. In the above example, I'd like to use and Excel function, argument, and/or macro thas has the effect of inserting 2 blank lines between Row 1 and Row 2, moving the Row 2 contents to Row 4. Then I'd like the functionality to spot the next product name-change and insert 2 rows there, etc. all the way to the bottom of the data. I'd wind up with; Product 1 Product 2 Product 2 Product 2 Product 3 Product 3 Product 4 My sincere thanks if you can show me how to do this. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
How to chart a single column of values similar to a GROUP BY | Charts and Charting in Excel | |||
Sum column values that contains text &/or dates | Excel Discussion (Misc queries) | |||
Pivot Table (vlookup 2 column text values, return 1 value) | Excel Discussion (Misc queries) | |||
Sort or Filter option? | Excel Worksheet Functions | |||
Subtotal of Subtotal displays Grand Total in wrong row | Excel Worksheet Functions |