Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 25th 06, 03:19 PM posted to microsoft.public.excel.misc
ConfusedNHouston
 
Posts: n/a
Default Group Repeating Text Values in a Column?

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   Report Post  
Old April 25th 06, 03:59 PM posted to microsoft.public.excel.misc
Jim May
 
Posts: n/a
Default Group Repeating Text Values in a Column?

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to chart a single column of values similar to a GROUP BY Paddy Charts and Charting in Excel 3 December 2nd 05 05:29 AM
Sum column values that contains text &/or dates mikeburg Excel Discussion (Misc queries) 5 December 2nd 05 01:27 AM
Pivot Table (vlookup 2 column text values, return 1 value) Al Excel Discussion (Misc queries) 1 November 30th 05 02:15 AM
Sort or Filter option? Mcobra41 Excel Worksheet Functions 3 February 23rd 05 08:22 PM
Subtotal of Subtotal displays Grand Total in wrong row Thomas Born Excel Worksheet Functions 5 January 6th 05 02:46 PM


All times are GMT +1. The time now is 08:43 PM.

Powered by vBulletin® Copyright ©2000 - 2022, Jelsoft Enterprises Ltd.
Copyright 2004-2022 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017