Need to Pull Column Headers Into Each Row Based on Yes/No Value in the Column

[Broziglieri]

I have a list of 1,800 clients (column A) with sales commissions values (column B), and then columns C through Y are the salespersons with a Yes/No value in each of the 1800 rows indicating which salesperson (up to 3 per client row) has earned that commission.

I would like to create a formula that would pull the header row 1 (salesperson names) into a cell to the right of the data sheet (column Z) when there is a Yes in that salespersons column for that client, and for additional salespeople that were associated with that client.

So for example for client1 if we have salesperson1, salesperson3, and salesperson9 with a YES in the row associated with that client (ignoring all the columns with a "NO" value. The column Z value would then hopefully be "salesperson1, salesperson3, salesperson9". That way i could then use a pivot table to quickly sum all the of unique combinations and individual salesperson's commissions.

I attached an image of a portion of the spreadsheet I'm working with, as well as the actual spreadsheet. The sheet has some VBA code in it that a friend was trying to help me out with but it seems to be returning all of the header values and all of the client row values, regardless of the Yes/No. If this can be done with a simpler formula instead that would be great, but any help or solutions would be greatly appreciated.

Thank you!

[Maudibe]

Place the following code in a standard module

Please Login or Register  to view this content.

In cell Z2, enter the following formula then copy down
=SalesReps(E2:Y2) where Columns E through Y and the Sales Rep Column (according to your image)

HTH,
Maud

[Broziglieri]

Wow this worked beautifully. Thank you so much!