Automated Filtering Transpose Formula

[SimonZamasia]

I have been working on this worksheet for construction plant and equipment hired expenses, it contains Dates, Rates and Hours worked for each piece of equipment that has been used. 

The info in Bold below is referring to the attached excel file I have been working on.

I used transpose Formula to get the hours worked from the far top right table and multiply by hourly rate to get the amount for that day, in the table that is on the far left where it is indicating Contractor’s Payables under each equipment.

I Have tried to work around this problem using the video linked below to achieve the auto-removal of blank cells when presenting the transposed data but still not failing to solve my challenge.

Excel Magic Trick 1143: Extract Content From Non-Empty Cells: Filter or Array Formula?

3 Ways to Transpose Excel Data (Rotate data from Vertical to ...https://www.youtube.com › watch

Attached is the workbook I have been working on.

Regards, 

Simon

[#2]

@SimonZamasia 

Main question are you on Excel 365 or not, based on that you could have different results using the same formula.

And what exactly is wrong here?

[#3]

The picture above is what I want to have, but for me to have that I had to remove formulae from the cells and manually take out the blank cells. The columns that have been transposed and where found to have no value in them should not be present in the rows, thats where am failing to solve this. What in is a formula thats should take out (Filter) all the blank cells automatically. 

The Black crosses in the picture above indicates the cell(s) that should have been filtered or removed.

Hope i have cleared my question.

 

@Sergei Baklan 

[#4]

I forgot to say am not on Excel 365

[#5 · Accepted solution]

@SimonZamasia 

As variant that could be

=IFERROR(
 INDEX($S$6:$BA$6,1,
 AGGREGATE(15,6,
     1/($S$6:$BA$6<>"")*
     (COLUMN($S$6:$BA$6)-COLUMN($R$6)),
     ROW()-ROW($B$13))
)*$K$33,0)

drag it down till end of the range.

[#6]

My life with excel has never been better as it is now, my work is looking neat now. Many thanks Sergei Baklan for the help rendered.

[#7]

@SimonZamasia , you are welcome