How to review data set, disregard "no" values to keep searching and return value only if "yes" value found...

[LDNeedsHelp]

I have a large excel table listing a number of duplicated project names on the vertical axis (depicting different milestone dates for each project), represented against a number of impact types in the horizontal axis, calling out for each line item if there's an impact or not, and what type of impact.

I want to present the data by project name, rolling up the impacts to show where there is a "yes" value recorded against any project milestone under that project name.

Project	Impact1	Impact2	Impact3
A	NO	NO	NO
A	YES	NO	NO
A	NO	NO	YES
B	NO	YES	NO
B	NO	NO	YES
C	NO	NO	NO
C	NO	NO	NO
C	NO	YES	NO

<tbody>
</tbody>

I need to represent where for any row for each project, an impact column is "Yes". For the above for example, I want to show:
Project A: Impact1, Impact3
Project B: Impact2, Impact3
Project C: Impact2

I think this could be achieved by a VLoookup or IndexMatch and an IF statement - i.e. IF VLookup for Project "B" in Column 1 (Impact1) = "No" then keep searching for "Yes". If Yes exists, return "Impact1". I don't know how to achieve the "keep searching until you find a yes" bit...

Appreciate any guidance! Thanks in advance!

 

[hiker95]

LDNeedsHelp,

You have not received a formula solution yet.

With your raw data grouped/sorted by Project in column A, then here is a macro solution for you to consider, that will adjust to the number of raw data rows, and, columns.

You can change the raw data worksheet name in the macro.

Sample raw data:

Excel 2007
	A	B	C	D	E	F	G
1	Project	Impact1	Impact2	Impact3
2	A	NO	NO	NO
3	A	YES	NO	NO
4	A	NO	NO	YES
5	B	NO	YES	NO
6	B	NO	NO	YES
7	C	NO	NO	NO
8	C	NO	NO	NO
9	C	NO	YES	NO
10
Sheet1

And, after the macro in the same worksheet, beginning in row 1, in the third column to the right of the last used raw data column:

Excel 2007
	A	B	C	D	E	F	G
1	Project	Impact1	Impact2	Impact3			Project A: Impact1, Impact3
2	A	NO	NO	NO			Project B: Impact2, Impact3
3	A	YES	NO	NO			Project C: Impact2
4	A	NO	NO	YES
5	B	NO	YES	NO
6	B	NO	NO	YES
7	C	NO	NO	NO
8	C	NO	NO	NO
9	C	NO	YES	NO
10
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub Find_YES_Impacts()
' hiker95, 11/01/2016, ME973664
Dim lr As Long, lc As Long, luc As Long, r As Long, nr As Long, n As Long
Dim rng As Range, c As Range, y As Long, prj As String
Application.ScreenUpdating = False
With Sheets("Sheet1")   '<-- you can change the sheet name here
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  lc = .Cells(1, 1).End(xlToRight).Column
  luc = .Cells(1, Columns.Count).End(xlToLeft).Column
  If luc > lc Then
    .Columns(lc + 1).Resize(, luc).ClearContents
  End If
  nr = 0
  For r = 2 To lr
    n = Application.CountIf(.Columns(1), .Cells(r, 1).Value)
    If n > 0 Then
      Set rng = .Range(.Cells(r, 2), .Cells(r + n - 1, lc))
      y = Application.CountIf(rng, "YES")
      If y > 0 Then
        prj = prj & "Project " & .Cells(r, 1).Value & ": "
        For Each c In rng
          If c = "YES" Then
            prj = prj & .Cells(1, c.Column) & ", "
          End If
        Next c
      End If
      If Right(prj, 2) = ", " Then prj = Left(prj, Len(prj) - 2)
        nr = nr + 1
        .Cells(nr, lc + 3).Value = prj
        prj = ""
      End If
    r = r + n - 1
  Next r
  .Columns(lc + 3).AutoFit
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the Find_YES_Impacts macro.

 

[LDNeedsHelp]

Thank you hiker95!

 

[hiker95]

LDNeedsHelp,

You are very welcome. Glad I could help.

And, come back anytime.