Complex Report Help

[dinokathy]

Hello,

I am hoping someone on here can help me put this together. I have been working on this for a few hours and just cannot figure out how to make it work. Here's what I need help with doing.

COVID Planning Spreadsheet - Help.xlsx

drive.google.com

The tabs are as follows
Imported Report - the data that will populate the COVID Report tab will get pulled from here. I want to be able to cut and paste the data on the Imported Report tab and have it calculate and update the COVID Report tab.
Market List - This breaks down the markets by Market Name and then the zip codes associated with those individual markets.
Covid Report - What I need to know here is # of drives scheduled in each market (columns G-O), how many units are booked (columns P-X).

What I want the spreadsheet to do is look at the data in Imported Report, compare the zip code (column U) against the Market List and tally up on the COVID Report tab the number of drives in said Market Name and the total units booked (from Imported Report, column AS).

I have tried VLOOKUP and MATCH and LOOKUP and just cannot get it to work the way I need it to and it is driving me nuts!

The data I want to be able to review is how many units are booked in each month in each market segment, which is made up of several zip codes.

Your help is so greatly appreciated. Thank you in advance.

 

[dinokathy]

AH. So sorry. Thank you again.

G to I

Oct	Nov	Dec
9	5	8
1	1	0
3	4	4

P to R

Oct	Nov	Dec
454	309	583
49	67	0
160	249	299

 

[kweaver]

This Array Formula (CTRL+Shift+Enter) in P4 and filled across and down seems to handle the P-X columns. Yes?

RE: G -- would you (re?) explain how to get the 9, 1, 3, etc.?

 

[dinokathy]

For G, that is a total count of the number of blood drives in the zip codes in that market, listed in the Market List tab.

The formula needs to hit the Market List tab to match up the zip codes for the total collections in each month listed in the Imported Report tab. The initial formula you provided doesn't hit the Market List tab to group the zip codes in each market together by month

Would it help if I uploaded a full report with all the zips? I would need to take a bit of time to remove the proprietary information and enter dummy data. Confidentiality and all...

Thank you again so much!

 

[kweaver]

I think I understand now. But, doesn't my formula handle the second block of cells?

 

[kweaver]

Guess I don't quite have it. Looking at October: since the Market List has a zip of 30120, but that's not in the Imported Report in the zips for Oct for RSMO, that would NOT be counted, right?

 

[dinokathy]

That's correct. The real imported report has thousands of lines of data, so I was trying to keep it short for the sake of saneness.

 

[kweaver]

My confusion, then, is why you still said there were 9 for that location.

 

[dinokathy]

Here's how there are 9 drives tallied up for October for column G for the Acworth GA RSMO:

On the Market List, the Acworth GA RSMO has the following zip codes in it (coincidentally, there are 9 zip codes in the list):

30101
30102
30120
30121
30123
30132
30137
30152
30189

On the Imported Report tab, there are the following drives listed that are contained in the zip code list for Acworth GA RSMO:

Drive Date	Site Zip Code	Total Proj Prod
10/7/20​	30102	45​
10/8/20​	30101	46​
10/8/20​	30102	47​
10/12/20​	30101	48​
10/14/20​	30101	50​
10/15/20​	30102	52​
10/20/20​	30101	53​
10/28/20​	30101	56​
10/29/20​	30101	57​

Not every zip code will have a blood drive in it every month since the Market is a conglomeration of the zip codes on the Market List. I need to be able to tally up how many drives there are and how many Total Proj Prod (the goal of the blood drive) for all the zip codes in each of the individual markets on the Market List.

Does that make sense?

 

[kweaver]

I had something else to work on, but didn't forget this issue. I created a helper column starting in BD2 on the Imported Report tab.
Filled this down:

=INDEX('Market List'!$A$2:$A$35,MATCH(U2,'Market List'!$B$2:$B$35,0))

Then, I also created the month numbers on the COVID tab in G1 to O1:

=MONTH(DATEVALUE(G$3&" 1, 2020"))

Then, on the COVID tab, G4, filled down and across, I used:

=SUMPRODUCT(('Imported Report'!$BD$2:$BD$36='COVID Report'!$B4)*(G$1=MONTH('Imported Report'!$B$2:$B$36)))

And this seems to get your numbers for that range and my other formula gets the values for the 2nd block.

Is this correct? Phew!

 

[dinokathy]

That looks like it is getting the number of drives correctly! Now let me see if I can figure out how to get the total units collected from each month using the roadmap you provided. Thank you thank you thank you so so much! I'll work on it this weekend and report back.