We help IT Professionals succeed at work.

How do I generate a conditioned single column list from an Excel array?

M_Patton asked
We have a master project number generator in Excel for all of our businesses.  We would like to add an extension to the master number for the individual business for each project if that business is involved.  Not all businesses are involved in all projects.  We would like to end up with a single list at the end (I have attached a greatly simplified Excel example (ignore the formulas I am using, they were for quick example generation).  How do I get from Sheet1 to Sheet2?
Watch Question

SimonPrincipal Analyst

I think you may have over-simplified your question and example. Experts will be better able to help if you can provide some more detail and context. I assume that A-E represent the names of your companies, but which items on sheet 1 would be manually populated? Is it just the 'x' to indicate that a subsidiary is to be assigned a project number for a pre-existing project?


A-E in the top row do represent company names and, yes, the X in a row-column junction indicates that the subsidiary has been assigned to a project.  The "assigner" will be presented with the project name/numbers and company names pre-populated.  They will then "X" the appropriate row-column junctions.

We then are looking for a formula(s) we can use to create a single column list which concatenates the project number and subsidiary ID based on the Xs in the array on Sheet 1.  This single column list will be used in other spreadsheets for accounting purposes.


The solution doesn't have to be in formula language; a VBA macro script would work, too.
Principal Analyst
OK, please try this. Paste into a new module and run it from the macro menu with your sheet1 is the activesheet.

Option Explicit

Sub GenerateMasterProjectNumbers()
Dim sourceSht As Worksheet, outputSht As Worksheet
Dim sourceRange As Range
Dim projStem As String, projSize As String, projCode As String, pad As String
Dim outputRow As Integer
Dim r As Variant, x As Integer

Set sourceSht = ActiveSheet
Set outputSht = ActiveWorkbook.Sheets.Add
outputSht.Cells(1, 1) = "ProjNo"
outputSht.Cells(1, 2) = "Name"
outputRow = 2

With sourceSht
    Set sourceRange = .Cells(1, 1).CurrentRegion
    Set sourceRange = sourceRange.Offset(1, 0).Resize(sourceRange.Rows.Count - 1)
    Debug.Print sourceRange.Address
    'Use range size to set the number of leading zeros
    If sourceRange.Columns.Count > 99 Then
    pad = "000"
    pad = "00"
    End If
    For Each r In sourceRange.Rows
        projStem = .Cells(r.Row, 1)
        projSize = .Cells(r.Row, 2)
        For x = 3 To sourceRange.Columns.Count
          If .Cells(r.Row, x).Value = "X" Then
            projCode = projStem & "-" & Right(pad & .Cells(r.Row, x).Column - 2, Len(pad))
            Debug.Print projCode
            outputSht.Cells(outputRow, 1) = projCode
            outputSht.Cells(outputRow, 2) = projSize
            outputRow = outputRow + 1
          End If
        Next x
    Next r
End With
End Sub

Open in new window