is there a formula that can return multiple matches to criteria and put each match in its own cell

[supermom28115]

I am trying to create a workbook where Sheet 1 would be printed out for each ID # to be distributed to that person as a hard copy. It would not be saved each time but used to gather the data into a formatted and organized for better understanding
Sheet 1 C2 is to input todays date, formula in E3 to calculate week #, F2 is to input ID # for participant. The rest of the page is where I need to data to be placed that would come from sheet #2
Sheet 2 is a table but isnt formatted as a table, column B beginning at row 7 is a list of items: shoe, hat, pencil, ruler, cup, taco, towel etc. Columns C - BB row 6 is week #'s 1 - 52. Array C7:BB20 are all '=RANDBETWEEN(1,3)
I need to print a sheet for ID# 1 that list each item in Column B of Sheet 2 that matches the week number and the ID # and there will be multiple matches. So if the date calculates to week 25 and the ID # is 2, I need a list of the items from the column for week 25 and the ID # 2 and whatever item is in column B on that matching row. If week 25 is column AA and rows 8, 10, 11, and 22 are all ID #2 then I need on Sheet 1 in whatever starting cell is designated; B8, next cell down B10, next cell down B11, next cell down B22. Then I print and enter the next ID# to generate a new list to print.
I will also need to be able to continue adding to Sheet 2 more items in the future.
Thank you guys for any advice or assistance you can offer.

 

[duggie33]

Hi supermom28115,
I am not able to process your data layout in my mind. If you have the FILTER function available, you should look into using it to return all the data with multiple criteria (ID and Week#).

Exceljet.net is a great reference: ExcelJet - Filter Function

If you need to return data from columns not adjacent to each other, use the CHOOSE function in the first argument. An example can be found at myonlinetraininghub.com, another great reference: myonlinetraininghub - Filter Function

I hope that helps,

Doug

 

[supermom28115]

Hi supermom28115,
I am not able to process your data layout in my mind. If you have the FILTER function available, you should look into using it to return all the data with multiple criteria (ID and Week#).

Exceljet.net is a great reference: ExcelJet - Filter Function

If you need to return data from columns not adjacent to each other, use the CHOOSE function in the first argument. An example can be found at myonlinetraininghub.com, another great reference: myonlinetraininghub - Filter Function

I hope that helps,

Doug

I guess I didn't explain it as well as I thought I had. I have been trying to put the data into a table and get the output I need but now have a headache and power pivot is just frustrating me. I thought my problem might be that I had given people a numeric ID because excel just wants to sum all those numbers.
I am trying to make a household cleaning table with each job listed on the left and the week number across the top of the table. Then I was trying to randomly generate a number for each job for each week (the number being assigned to a certain household member) . The table and randomly generated job assignments each week was the easy part. How do I get the data to be filtered by week for each different person in a printable list without having to go through all the filter, hide rows, highlight, print, change filter...
I will keep working on it. Thank you for looking at it and responding. This particular table is the simple one with daily tasks I can't wait to start working with the tasks that only need to be done once a week or once a month, every 6 months.
And in case anyone was wondering this is to streamline or simplify the assigning and notification of chores or tasks for special needs kids. Things have to be very specific and if I have to manage this by hand it will be very time consuming.