Complex Report Help

dinokathy

New Member
Joined
Nov 15, 2018
Messages
23
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.


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.
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,585
Office Version
  1. 365
  2. 2010
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

New Member
Joined
Nov 15, 2018
Messages
23
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

Well-known Member
Joined
May 8, 2018
Messages
1,585
Office Version
  1. 365
  2. 2010
Would this work for Covid G4 and fill down and across?

Code:
=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

New Member
Joined
Nov 15, 2018
Messages
23
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
May 8, 2018
Messages
1,585
Office Version
  1. 365
  2. 2010
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

New Member
Joined
Nov 15, 2018
Messages
23
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
May 8, 2018
Messages
1,585
Office Version
  1. 365
  2. 2010
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

New Member
Joined
Nov 15, 2018
Messages
23
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.
 
Last edited:

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,585
Office Version
  1. 365
  2. 2010
Would you give me a few "answers" to the G-X columns using your sample data?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,665
Messages
5,626,173
Members
416,166
Latest member
Archimed

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top