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.

 

[kweaver]

Since there are no zip codes in the COVID sheet, do you want to compare by the market?
Also, what version of Excel do you run?

 

[dinokathy]

Yes. The idea being is that we are targeting X number of collections per month per market (column F on the COVID Report tab). So I want to see where we are falling short.

I am using Excel 2019 standalone.

Thank you so much for your help. I cannot say enough how much I appreciate it.

 

[kweaver]

Would this work for Covid G4 and fill down and across?

=SUMPRODUCT((MONTH(DATEVALUE(G$3&" 1, 2020"))=MONTH('Imported Report'!$B$2:$B$36))*('Imported Report'!$AS$2:$AS$36)*((LEFT($B4,FIND(" ",$B4)-1)='Imported Report'!$S$2:$S$36)))

I may have picked the wrong cell(s) in using G because I don't know where the Drives number is, etc. if this works, maybe you an adjust as needed?!?!

 

[dinokathy]

This looks like it will work!

Now, since I didn't want to throw a bunch of proprietary information out on the web, I heavily redacted the report example I put up. So, where in that beautiful piece of formula up there is it referencing the Market List sheet?

Thank you so much!

 

[kweaver]

I didn't create anything that consolidated the zip codes...I used the leading portion of the "market name"...Will that be an issue that will force a zip code evaluation?

 

[dinokathy]

Ah! Yes, unfortunately. The complete list of Market Names has Atlanta East, Atlanta West, Atlanta Northeast, etc. so making it hit the zip code in the Market List and then comparing that to the zip code on the Imported Report is unfortunately very necessary. Again, thank you so very very much. You have no idea how much this will help me and my team out.

 

[kweaver]

I think this is going to require a macro. Could you provide some answers to the values you expect from the sample data you provided?

 

[dinokathy]

Thank you so much again. A little background on this data. I work at a blood bank and since COVID hit, we have been significantly impacted with business and school closures. We reviewed all our data, broke it up into 'markets' by zip code and identified areas where our blood donors live. From there we need to make sure we are setting up blood drives in the areas where they live and in the frequency and numbers that our field reps felt were accurate and attainable. We've done the work to break it all up into markets but now I want to be able to track whether or not we are executing it by market.

Ideally, in the end, I would like to see on the COVID Report:

Column G - O - the number of blood drives held in the individual market in a given month - this isn't critical, but is nice information to have
Column P - X - the total of the projections of each blood drive in each market by month - this is the most important data I need.
Column Y - AG - the average per operation of those drives. This I know how to do.
I also need to be able to sort by District, which is contained in the Market List tab as well.

So what I need G - O to do is hit the Imported Report, get the data in columns U and AS (zip code and Total Proj Prod) and match that up with the Market name in Column B on the COVID Report with the Market List tab by zip and match up the zip and Market Name, add it all up and tally it in Columns G - O and P - X on the COVID Report.

The actual report that this will go in to 951 zip codes in the zip list, 141 Market Names and 2047 blood drives through December on the Imported Report.

Again, thank you so much for taking your time to help.

 

[kweaver]

Would you give me a few "answers" to the G-X columns using your sample data?