hey all,
i have 2 columns
categories and products

home,furniture
home,household
car,fuel
car,service

is there a way to put above in following format in a new column?

home
furniture
household
car
fuel
service

thanks,
rodchar

Hi !! I can give you the algorithm. I'm sure you'll be able to write the
macro.

a) Set up a 1-dim array that is large enough to hold all the
categories.

b) Set up a For...Next loop to move the cursor down the
categories column starting from the first data element (just below the header)

c)If the activecell.value is not found in the array 1.make it
the next element in the array. 2.Enter this value in a third column. 3.
Scan the Products column with an inner (Nested) For...Next, and enter the
products associated with this category below it.

You will have to work out the details of the macro very
carefully. But it will work.

I hope this will help your








"rodchar" wrote:

> hey all,
> i have 2 columns
> categories and products
>
> home,furniture
> home,household
> car,fuel
> car,service
>
> is there a way to put above in following format in a new column?
>
> home
> furniture
> household
> car
> fuel
> service
>
> thanks,
> rodchar

This puts the output in a new column on a new worksheet:

Option Explicit
Sub testme01()

Dim wks As Worksheet
Dim RptWks As Worksheet
Dim iRow As Long
Dim oRow As Long

Set wks = Worksheets("Sheet1")
Set RptWks = Worksheets.Add

With wks
RptWks.Cells(1, "A").Value = .Cells(1, "A").Value
RptWks.Cells(2, "A").Value = .Cells(1, "B").Value
oRow = 2
For iRow = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
If .Cells(iRow, "A").Value <> .Cells(iRow - 1, "A").Value Then
'add the group indicator
oRow = oRow + 1
RptWks.Cells(oRow, "A").Value = .Cells(iRow, "A")
End If
oRow = oRow + 1
RptWks.Cells(oRow, "A").Value = .Cells(iRow, "B").Value
Next iRow
End With

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

rodchar wrote:
>
> hey all,
> i have 2 columns
> categories and products
>
> home,furniture
> home,household
> car,fuel
> car,service
>
> is there a way to put above in following format in a new column?
>
> home
> furniture
> household
> car
> fuel
> service
>
> thanks,
> rodchar

--

Dave Peterson