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.
 

dinokathy

New Member
Joined
Nov 15, 2018
Messages
23
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
AH. So sorry. Thank you again.

G to I
OctNovDec
958
110
344

P to R
OctNovDec
454309583
49670
160249299
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

kweaver

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

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

Well-known Member
Joined
May 8, 2018
Messages
1,583
Office Version
  1. 365
  2. 2010
I think I understand now. But, doesn't my formula handle the second block of cells?
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,583
Office Version
  1. 365
  2. 2010

ADVERTISEMENT

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

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

Well-known Member
Joined
May 8, 2018
Messages
1,583
Office Version
  1. 365
  2. 2010

ADVERTISEMENT

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

dinokathy

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

Well-known Member
Joined
May 8, 2018
Messages
1,583
Office Version
  1. 365
  2. 2010
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:
Code:
=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:
Code:
=MONTH(DATEVALUE(G$3&" 1, 2020"))

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

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

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

Watch MrExcel Video

Forum statistics

Threads
1,127,600
Messages
5,625,744
Members
416,132
Latest member
Chandan Choubey

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