Return multiple rows from a single formula?

[morgens]

I have attached a dummy file DummyWorkbook.xlsx with a worksheet for data and a worksheet for desired results. I want to be able to enter a date and description on one sheet and then on my second sheet, enter a date range and have the rows below populate with any dates and their corresponding descriptions from the 1st sheet. I do not want there to be any empty rows and would ideally enter a formula in a single cell (not using vba) to achieve this.

[morgens]

Anyone? Even if it can't be done without VBA, I would appreciate ANY info you can give me. Thank you!

[JBeaucaire]

B1: =MIN(Data!A:A)
D1: =MAX(Data!A:A)

B5: =IF($B$1-1+ROW(A1)>$D$1,"",VLOOKUP($B$1-1+ROW(A1),Data!$A:$B,2,0))
...copy B5 down as far as you'd like.

[morgens]

thanks for the response. I appreciate you taking the time. It's great that the list begins with the start date, regardless of what start date is on the data sheet. Unfortunately this doesn't quite solve my problem. I still have two issues here:

1. If there are multiple descriptions for the same date, it will only return the first occurrence.

2. Also, it is leaving a slot for each date, so I added a description for 1/12 and it listed it in the 12th spot, but left all cells between that description and the previous date as #N/A. Ideally, there would be no blank or n/a cells.

any idea on how to solve one or both?

thanks again.

[mahju]

Hi

In data sheet in Column A i have enetered the formula to increase by one if the date fallss between the range date given in desired results sheet.

=IF(AND(B2>='Desired Results'!$B$1,B2<='Desired Results'!$D$1),A1+1,A1)

Then in "Desired Result"s sheet i use vlookup to extrect the rows useing row function.

=IF(ISERROR(VLOOKUP(ROW(A1),Data!$A$2:$C$7,3,FALSE)),"",VLOOKUP(ROW(A1),Data!$A$2:$C$7,3,FALSE))

See the attached


Regards

[JBeaucaire]

You're saying your sample sheet originally doesn't actually show your entire scenario? Redo that and be sure to fully demonstrate what you're doing, not just parts of it. Thanks.

[morgens]

mahju,
thank you for your response. I am afraid those formulas do not achieve the desired results. The 1st Description of the desired results does return correctly, but not necessarily the rest or the complete desired date range. Thank you very much for the effort.

JBeaucaire,
I apologize for not including enough data to start. I have added a few rows to the data set as well as a second possibility for desired results. Hopefully this will give a more complete picture of what I am looking for.

You can see that there will be multiple descriptions for a given date.

The Start and End dates will be manually entered so that I can choose a date range to summarize.

I will need to print out this summary on a weekly basis. The data set will likely have over 1,000 entries by years end, but I would still like to be able to pull descriptions within a given date range and print it out without spaces between the descriptions.

I guess I am basically looking to replace the manual action of sorting by date on the data sheet, copying the descriptions within the desired range and pasting on a summary sheet. There are other calculations for the given date range that are included on that summary as well that I have already figured out.

thanks again for your help and please let me know if you need any additional information.
Attachment 141086DummyWorkbook_updated.xlsx

[mahju]

mahju,
thank you for your response. I am afraid those formulas do not achieve the desired results. The 1st Description of the desired results does return correctly, but not necessarily the rest or the complete desired date range. Thank you very much for the effort.

Hi

I checked if you set the formulae as in sheet it will give all description
any way thanks fo your coments
Regards

[Haseeb Avarakkan]

Try this

In B5, with CTRL+SHIFT+ENTER, rather than just ENTER

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX(Data!B:B,SMALL(IF(Data!A$2:A$40>=B$1,IF(Data!A$2:A$40<=D$1,ROW(Data!A$2:A$40))),ROWS(B$5:B5)))))

Then copy down.

Assume you are on XL 2003

[morgens]

Haseeb A,
This appears to work! thank you. Now I just need to take some time to understand why. Thank you so very much for your help.