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?
MasterExample.xlsx
MasterExample.xlsx
Simon
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?
ASKER
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.
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.
ASKER
The solution doesn't have to be in formula language; a VBA macro script would work, too.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.