Macro to do multiple search/matches

[Frostbite]

Hello. I have used this site many times to answer questions, but I am unable to piece together anything to make this one work.

I have a spreadsheet that lists customers down Column A. Across a given row there is a value in at least one cell, but sometimes up to five cells, for each customer. My column headings are all products that each customer will be given. I have 49 different columns or products to choose from.

I want my macro to go through each row, look for a cell with a value and then return the column heading of the row with a value in it, which would be a product name. I need the macro to continue looking at the row until it goes through all 49 columns because there could be more values for each customer. After it finishes looking at the first row I need it to go down to the next row and do the same thing. I have around 150 customers to loop through in all.

I this on one sheet and would like the results to show up on another worksheet. The format of it would have all the customer names in column A, then the values that are found through the macro should find the first blank cell after the customer name and start placing the product values across the row.

I am not sure if there if there is an easy way of doing this, but I have been unsuccessful at making it happen. Any help would be appreciated!

Thanks

 

[texasalynn]

see if this does it

Sub FindProduct()
Dim LR As Long, LC As Long, i As Long
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row
Columns("A:A").Copy Destination:=Sheets("Sheet2").Range("A1")
LC = Sheets("Sheet2").Range("IV1").End(xlToLeft).Column + 1
For i = 2 To LR
    LC = Sheets("Sheet2").Range("IV" & i).End(xlToLeft).Column + 1
    For j = 2 To 49
        If Cells(i, j) <> "" Then
            Sheets("Sheet2").Cells(i, LC) = Cells(1, j)
            LC = Sheets("Sheet2").Range("IV" & i).End(xlToLeft).Column + 1
        End If
    Next j
Next i
MsgBox "Done"
Application.ScreenUpdating = True
End Sub

 

[Frostbite]

Texasalynn you are amazing. Worked like a charm! I'm going to have to go through and figure out why it worked so I can understand it better, but it was flawless! I appreciate your time!

 

[texasalynn]

Glad I could help. Thanks for the feedback