Help with IF? MATCH? INDEX?
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
|