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

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?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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?
M_PattonAuthor Commented:
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.
M_PattonAuthor Commented:
The solution doesn't have to be in formula language; a VBA macro script would work, too.
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.