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

Hi All,

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

The attached sample gives an idea of what I am trying to do. I have a series of clients (column B, B5:B9), then for each month (Row 4, C4:N4) I am recording client visits. 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. (see the 'desired result' on the lower half of the attachment).

My thought (using client A as an example) was to have the first cell (C16) containing a formula that finds the first non-blank cell in the range C5:N5. The next cell (D16) containing a formula that finds the second non-blank, the next cell 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.

I appreciate any help!

Hello
If you're happy to use Array formulas, then perhaps something as in the attached reply might do what you're looking for.

Hope this helps.
DBY

Hi,

Thanks for the reply!

Your sample gives exactly what I'm looking for. I'm happy using array formulas but I'm having a hard time getting it to work in my live document! I guess I need to try and understand how you came up with the formula to see where I'm going wrong.

If I'm understanding correctly the INDEX is looking at the data entry portion of the table as the array... the MATCH component is identifying which row to look at... and the SMALL component is identifying which column to take from?

If I am correct so far, I am then lost as to how the IF function is determining the array for the SMALL function?

Thanks again for any help you can provide.

Hello
Glad it was of some use. Sorry you're having difficulty with applying it to your real data. Array formulas can be tricky and resource hungry, that's why I'm never sure if anyone wants to use them.

The IF function checks the range B5:B9 to match the value of B16 down. The (*) operator acts as an AND logic and checks for non-blank cells in the range C5:N9, along the row matching B16 down. If a match of those two criteria is found resulting in a return value of 1, it then multiplies that by the corresponding Column number, returning an array of Column numbers and Blanks. As the COLUMN function returns the Column number of the sheet, I subtract 2 because I wish to Index from Column 1 of the range C5:N9, not from Column A of the sheet which would be two Columns too many.

Hope my attempts at an explanation are clear. Come back if you're still having problems and maybe other Forum members or myself might be able to help.

DBY