Hello, I am trying to do the following, but I can't seem to come up with a
forumla.

Let say I have a table below

Group
Name A B C
Pete N Y N
Jane Y N Y
John N Y Y

is it possible to create a new sheet to to show the "Y" values?

Group Name
A Jane
B Pete
B John
C Jane
C John

thanks,

PJS

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long, j As Long
Dim LastRow As Long
Dim LastCol As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = LastRow To 1 Step -1

LastCol = .Cells(i, .Columns.Count).End(xlToLeft).Column
For j = LastCol To 2 Step -1

If .Cells(i, j).Value = "Y" Then

.Rows(i + 1).Insert
.Cells(i + 1, "A").Value = .Cells(1, j).Value
.Cells(i + 1, "B").Value = .Cells(i, "A").Value
End If
Next j
.Rows(i).Delete
Next i

.Range("A1").Value = "Group"
.Range("B1").Value = "Name"
.Range("C1").Resize(, 100).Value = ""
.Columns("A:B").Sort key1:=.Range("A2"), order1:=xlAscending, _
key1:=.Range("B2"), order1:=xlAscending, _
header:=xlYes
End With

End Sub

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"PJS" wrote in message
...
Hello, I am trying to do the following, but I can't seem to come up with a
forumla.

Let say I have a table below

Group
Name A B C
Pete N Y N
Jane Y N Y
John N Y Y

is it possible to create a new sheet to to show the "Y" values?

Group Name
A Jane
B Pete
B John
C Jane
C John

thanks,

PJS