Need help trying to list multiple non-blank items from a row

[tanithscout]

Hi All,

I am stumped when trying to create a list of the non-blank items in a row.

I have a table in a spreadsheet, the rows represent a list of clients, the columns represent months of the year. The table is being used to track client visits (if there is a visit in a particular month, the date is filled in the table). The cells left blank are truly blank, text is only entered upon a client visit.

At the end of the year I want to be able to capture a summary that only shows the non-blank entries for each client. There is only ever one visit possible per month but some clients will have multiple visits throughout the year.

My goal is to have a separate row for each client followed by the visit dates. For example:
Client A Feb 5th Sep 15th Dec 5th
Client B Mar 10th Oct 15th

My thought is to have a formula in the first cell of the row to identify the first non-blank item in the row... a formula in the second cell to identify the second blank item... a formula in the third cell to identify the third non-blank... and so on.

I have been able to find various ways to identify the first non-blank but I am yet to find a way to capture the nth non-blank for subsequent matches.

Is there a formula that can be used to do this?

I appreciate any help!

​

 

[JLGWhiz]

Hi tanithscout, welcome to the board. I don't know if there is a formula that can do what you want, but it can be done with VBA macro.

This procedure assumes that the client names and visit dates begin in row 2. It also assumes that the data is on sheet 1 and that sheet 2 is unused and available for receiving data from sheet 1. If any of the assumtions are untrue, then the code will need to be modified to accommodate reality. Copy this procedure to your standard code module1 in the VBA editor. To access the editor, Alt+F11. If the large pane is dark, click Insert>module.

Code:
Sub summaryVisit()
Dim sh1 As Worksheet, sh2 As Worksheet, lr1 As Long, lr2 As Long, rng As Range, c As Range
Set sh1 = Sheets(1) 'Edit sheet name
Set sh2 = Sheets(2) 'Edit sheet name
lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh1.Range("A2:A" & lr)
For Each c In rng
c.EntireRow.SpecialCells(xlCellTypeConstants).Copy sh2.Range("A" & sh2.Cells(Rows.Count, 1).End(xlUp).Row + 1)
Next
End Sub
Code: