Printable View
Hi Team - I've the customer request count given date wise. There are days in which customer will not have any request and the value will be blank or zero for those dates. I would like to get the output date wise wherever customer had at least 1 request. Please find attached the data format, can it be done through formula or macros? Appreciate the help. Thanks.
Do you have a version with FILTER? If so, clear the output table of dates, then enter:
in B11 and copy down.Code:=FILTER($B$2:$AE$2,B3:AE3>0)
This formula works only for the first cell in each row. Rest of the cells in the row suppose to show different date for which the value is not blank or not zero. Please see the attached.
@tsr_80, could you address the question I asked here: http://www.vbaexpress.com/forum/show...l=1#post425073
Knowing that would help in answering this question.
Responded for the thread mentioned. In brief, TOCOL function is available in my excel.
In that case, Aflatoon's solution will work but it needs to be put into a single cell (no CSE (array-entering) required) and copied down. See cells B16:B19 in Sheet1 of the attached.
Separately, sheet Sheet1 PQ contains a Power Query solution, but it's ugly and I would NOT recommend using it (it loses the date values in the header row).
The excel formula works. Thanks and Great but i still miss the understanding. Can you please help explain the logic? How entering the formula in one cell does fill the dates for the cells in the row?
FILTER is a dynamic array function, which is relatively new functionality allowing formula results to spill into other cells as needed.