Consolidating tables with under date

[Danny cool]

Hi Guys,



I have a sparsely populated table that consists of a date and then seven category sizes of feed (table 1)

I have a formula for a list without blanks which condense the table. (table 2)

Problem is that i want to dates to line up for each catagory so its easy to read. For example, if there is delivery on the 29/03/20, i want it to search the other size categories to see if there are any other sizes then moved to the next column for the next delivery date. The example of what i want it too look like is in Table 3.

I believe this is quite a complex problem, tried with an IF statement but i cant get it to work inside the array formula. Do i need a macro? i would prefer a formula

Ts difficult to explain in words, have a look at the attached.

I appreciate you guys!!

[Purfleet]

I think index and match is what you are looking for

=INDEX($D$17:$BOB$23,MATCH($C43,$C$17:$C$23,0),MAT CH(D$42,$D$16:$BOB$16,0))

Do you have something to work out the dates with a delivery? if so the above seems to work

I would also reccomend turning the data around so that the feed is along the top and the dates at the side, it will be much easier to manage and read.

[Danny cool]

Thanks for you reply.

Yes i think you are right. I will flip the tables around.

Its a little confusing as i may not have explained it right.

You see cells D27 to D38, there are three separate date (29/03 03/04 and 01/07) what i would need is:

column D all feed quantities arriving on the 29/03
column E all feed quantities arriving 03/04
column F all feed quantities arriving 01/07

I think i can do with two tables but i want to get it into one and i dont want to have to filter using the table drop down menu.

[Purfleet]

Okay, so my attempt in D43:k49 is not what you wanted?

[Danny cool]

It actually works well, thank you!

One last question. Is there anyway to get the dates in line 42 automatically?

[Purfleet]

Hello mate try

Helper row in 15 - =COUNTIF(D17:23,">"&0)

I have added this in row 41 and it seems to be okay
=SMALL(IF($D$15:$BOB$15>0,$D$16:$BOB$16,""),COLUMN S($D$41:41))

Noticed you have merged cells on this work sheet and it is interferring with some formulas - be very careful of Merged cells as nothing good will ever come of them and they will cause you issues at some point.

Also row 22 was hidden

[Danny cool]

Purfleet, you are a legend mate thank you so much!!