Enhanced Excel formulas to be used

Question

Products    PD1 PD2 PD3 PD4 PD5 PD6 Summary of Purchases
Customer A      2   2   3   5   6   PD2, PD3, PD4, PD5, PD6
Customer B      2   2       1       PD2, PD3, PD5, 

How do I summarise the purchases such that it looks in the row to see if it is not a blank and gets the corresponding product list.

Answer 1

Try the following UDF():

Public Function qwerty(rIN As Range) As String
    Dim r As Range, s As String

    s = ""
    For Each r In rIN
        If r.Value <> "" Then
            s = s & "," & r.EntireColumn.Cells(1).Value
        End If
    Next r
    qwerty = Mid(s, 2)
End Function

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=qwerty(A1:Z1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

Answer 2

Another way that does not use vba, instead it usesnames and formula to check if PD1 has a entry. This can be used anywhere on the row. to check for customer A go to any blank cell to the right of the data on the 2nd row and enter

=if($b2="","",$b$1 & ",")

this will either be a blank or PD1, depending on the contents of b2 copy the formula and then use the name manager (formula ribbon) make a new name of PD_1 and paste the formula on the bottom line and the name manager will convert it slightly such as

=IF(Sheet1!$B2="","",Sheet1!$B$1 & ",")

Follow this same pattern for the remaining products using the correct column letter example $c2="",$c$1 for PD2, etc.

You could go straight to using the PD_# but I would make one more name

PD_Sum=trim(pd_1 & Pd_2 & PD_3 & PD_4 & PD_5 & PD_6)

Then in sum column you just put =PD_Sum

if you want it without the , at the end then it is

=iferror(left(PD_sum,len(PD_sum)-1),"")

The iferror is included in case there is nothing selected in any of the cells it remain blank and not generate an ugly error message.