• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to pull data into a summary when the date cell is blank??

O

Oxidised

Member
Hi
Please see attached spread sheet where I have pulled an exert of a long list of data. This data is kinda messy in that it has blank rows and not all the fields are always filled in.

I want to pull a summary for the next X days - defined by a start and end date. The issue is that there is only a date next to the first line of data, and any subsequent entries for that day do not have a date.
Is it possible to search and find this data, or do I need to go back and modify the source data to every record has a date field??
On the right hand side is an example of what I want the result to look like, but it needs to update each day.
Thanks
 
Last edited:
You would be so much better off if you added dates to each record and remove the blank lines as well
 
I thought you might say that... I have cleaned up the example file and will re-post. And I'll have to convince those that work on the file to not leave gaps!
 

Attachments

  • summary data2.xlsx
    10.6 KB · Views: 26
Why not add a helper column, next to H insert a column that fills the date and enter a formula like

=IF(H6<>"",H6,I5)

Edit: First post although I've been visiting this forum for over 2 years!! It's about time!
 
Hi Oxidised,

Please have a look of attached file. The summay table consists of following formula.
For Date in H6: Copy down to as per your data.
=IFERROR(IF(COUNTIF($H$5:H5,SMALL(IF($D$6:$D$27<>"",IF($A$6:$A$27>=$I$3,IF($A$6:$A$27<=$I$4,$A$6:$A$27))),ROWS($H$6:H6))),"",SMALL(IF($D$6:$D$27<>"",IF($A$6:$A$27>=$I$3,IF($A$6:$A$27<=$I$4,$A$6:$A$27))),ROWS($H$6:H6))),"")

For Customer in I6:
=IFERROR(INDEX(B$6:B$27,SMALL(IF($D$6:$D$27<>"",IF(SMALL(INDEX($H$5:$H6,MATCH(9E+99+300,$H$5:$H6)):$H6,1)=$A$6:$A$27,ROW($A$6:$A$27)-ROW($A$6)+1)),COUNTA(INDEX($H$5:$H6,MATCH(9E+99+300,$H$5:$H6)):$H6))),"")

For Product in J6:
=IFERROR(INDEX(C$6:C$27,SMALL(IF($D$6:$D$27<>"",IF(SMALL(INDEX($H$5:$H6,MATCH(9E+99+300,$H$5:$H6)):$H6,1)=$A$6:$A$27,ROW($A$6:$A$27)-ROW($A$6)+1)),COUNTA(INDEX($H$5:$H6,MATCH(9E+99+300,$H$5:$H6)):$H6))),"")

For Amount in K6:
=IFERROR(INDEX(D$6:D$27,SMALL(IF($D$6:$D$27<>"",IF(SMALL(INDEX($H$5:$H6,MATCH(9E+99+300,$H$5:$H6)):$H6,1)=$A$6:$A$27,ROW($A$6:$A$27)-ROW($A$6)+1)),COUNTA(INDEX($H$5:$H6,MATCH(9E+99+300,$H$5:$H6)):$H6))),"")

Note all these are array formulas so dont just press Enter after entering them, press Ctrl+Shift+Enter.

Advise if I misunderstood your problem.
Regards,
 

Attachments

  • summary_data2(1).xlsx
    12.2 KB · Views: 22
Thanks Somendra, that is great! Seems to work well.

Question, if I made dynamic named ranges of the columns on the left for customer/product/etc, I could then reference them in the formulas you provided to get the summaries on the right? This would avoid me having to increase the ranges, or start with massive ranges to allow for new data in the future.
I'll try to incorporate that into my bigger spreadsheet this afternoon!

Thanks again, Cheers, Oxi
 
I ended up dropping the
IF($D$6:$D$27<>""
Click to expand...
check you built in as I wanted all the results, but knowing where to put this check could be useful for other things later. Working like a charm now!
 
You must log in or register to reply here.
Back
Top