How to return data from multiple worksheets with different column dates

[maeyks]

Good morning.

Happy Holidays!

I have a question on how I can tabulate. Please see attached image below of UPTC and then Trinoma, 2 of sample sheets I am working on:

Each sheet came from different locations, for these examples, UPTC and Trinoma. I have on column A the item list, and columns H to N as the dates. Notice that not all are filled out with numbers. Each column represents delivery dates. Row 2 columns H to N are supposed to be dates. They are reflected as errors due to a formula.

Here is what I would like to do with these:

1) consolidate on a new sheet ALL of which is on column A (Boneless chicken, whole chicken, etc.); remove any duplicates across all sheets (as the method of collecting all of these are copy-paste method only)

2) there will be a new column for UPTC, Trinoma and to the rest of the stores on this new sheet

3) use index match function to extract numbers based on an item at the same time store and at the same time date

It took me a long time to collect all these data and arrived at this photo:

It was tedious. What I would love to do is just copy-paste and viola it will appear like this. Notice that the original format of this is more of an elongated version, thus, every time the day changes, I need to unhide the columns and choose the proper date, and then hide those which are not necessary.

Help, please.

Thanks a lot

 

[Todor]

Hi,

To avoid hiding/unhiding the columns all the time, you can create a helper cell outside of the range and then reference the row 3 to it. That way, you can only use 11 columns which are the minimum to cover each location. After that, you can use index/match formula with multiple criteria (array formula) to find values in other sheets (date, location, type, etc.). I would also recommend converting the data sheets into tables so you make the data dynamic, which will make the formula easier to write and make the whole process smooth.