Comparing data in two columns against two data ranges

G

Galveston

New Member
Joined
Nov 17, 2011
Messages
2
I am using Excel 2007.
I have been handed the task of compiling 10 years of scheduling data into something meaningful. I have 120 spreadsheets of scheduling info, one for each month in the last 10 years across 4 departments.

I need to extract the data for one department, unfortunately it is the busiest one and uses 29 seperate job codes and works in 69 seperate countries.

A17:45
<TABLE style="WIDTH: 414pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=552 border=0><COLGROUP><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2304" width=81><COL style="WIDTH: 353pt; mso-width-source: userset; mso-width-alt: 13397" width=471><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 61pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=81 height=18>Code</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; WIDTH: 353pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=471>Job</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>ACP</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Alternate Compliance Program Inspection</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>ANN</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Annual Examination</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>COI</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Certificate of Inspection</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>COC</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Certificate of Compliance</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>REF</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">REFLAG Inspection</TD></TR></TBODY></TABLE>

Each month has two columns that lists a job code (columns AJ2:AJ90) and a country code (columns AK2:AK90).
AJ2:90 AK2:90
<TABLE style="WIDTH: 90pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=120 border=0><COLGROUP><COL style="WIDTH: 45pt; mso-width-source: userset; mso-width-alt: 1706" width=60><COL style="WIDTH: 45pt; mso-width-source: userset; mso-width-alt: 1706" width=60><TBODY><TR style="HEIGHT: 26.25pt; mso-height-source: userset" height=35><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 45pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 26.25pt; BACKGROUND-COLOR: transparent" width=60 height=35>Activity</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 45pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=60>Country</TD></TR><TR style="HEIGHT: 13.9pt; mso-height-source: userset" height=18><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.9pt; BACKGROUND-COLOR: transparent" height=18></TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 13.9pt; mso-height-source: userset" height=18><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.9pt; BACKGROUND-COLOR: transparent" height=18>MTG</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">NL</TD></TR><TR style="HEIGHT: 13.9pt; mso-height-source: userset" height=18><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.9pt; BACKGROUND-COLOR: transparent" height=18>LBP</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">NL</TD></TR><TR style="HEIGHT: 13.9pt; mso-height-source: userset" height=18><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.9pt; BACKGROUND-COLOR: transparent" height=18>LRF</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">AE</TD></TR><TR style="HEIGHT: 13.9pt; mso-height-source: userset" height=18><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.9pt; BACKGROUND-COLOR: transparent" height=18>SFP</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">FI</TD></TR><TR style="HEIGHT: 13.9pt; mso-height-source: userset" height=18><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.9pt; BACKGROUND-COLOR: transparent" height=18>LBP</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">UK</TD></TR></TBODY></TABLE>

I have made a seperate Country Code worksheet to count the country data, the country codes are in cells B3:71.

I used this formula for each month to calculate the countries: COUNTIF('JAN01'!AK$1:AK$90,$B8)+.... where B8 was the country code. I can use the countif function to calculate how many times we have gone to a specific country but that is for the entire office, and that is where I have a problem.

I am looking for a formula that checks columns AK3:AK90 and AJ3:AJ90 to see if we were in that country for one of the jobs in that specific department. Ideally I could string the calculation together to graph annual visits to check for increasing or declining visits to each country.

<TABLE style="WIDTH: 215pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=287 border=0><COLGROUP><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 3726" width=131><COL style="WIDTH: 39pt; mso-width-source: userset; mso-width-alt: 1479" span=3 width=52><TBODY><TR style="HEIGHT: 26.25pt; mso-height-source: userset" height=35><TD class=xl69 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: navy 1pt solid; WIDTH: 98pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 26.25pt; BACKGROUND-COLOR: white" width=131 height=35>CODE</TD><TD class=xl71 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 39pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=52>ACP</TD><TD class=xl71 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 39pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=52>COI / ANN</TD><TD class=xl71 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 39pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=52>COC / PSC</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #3366ff 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: navy 1pt solid; WIDTH: 98pt; BORDER-BOTTOM: #3366ff 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=131 height=17>AO</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #eeece1" align=right>0.0</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #eeece1" align=right>10.0</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #eeece1" align=right>12.0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-RIGHT: #3366ff 0.5pt solid; BORDER-TOP: #3366ff; BORDER-LEFT: navy 1pt solid; WIDTH: 98pt; BORDER-BOTTOM: #3366ff 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=131 height=17>BH</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #eeece1" align=right>0.0</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #eeece1" align=right>0.0</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #eeece1" align=right>0.0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-RIGHT: #3366ff 0.5pt solid; BORDER-TOP: #3366ff; BORDER-LEFT: navy 1pt solid; WIDTH: 98pt; BORDER-BOTTOM: #3366ff 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=131 height=17>BE</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #eeece1" align=right>2.0</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #eeece1" align=right>4.0</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #eeece1" align=right>14.0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-RIGHT: #3366ff 0.5pt solid; BORDER-TOP: #3366ff; BORDER-LEFT: navy 1pt solid; WIDTH: 98pt; BORDER-BOTTOM: #3366ff 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=131 height=17>BG</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #eeece1" align=right>1.0</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #eeece1" align=right>0.0</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #eeece1" align=right>0.0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-RIGHT: #3366ff 0.5pt solid; BORDER-TOP: #3366ff; BORDER-LEFT: navy 1pt solid; WIDTH: 98pt; BORDER-BOTTOM: #3366ff 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=131 height=17>CM</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #eeece1" align=right>14.0</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #eeece1" align=right>17.0</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #eeece1" align=right>21.0</TD></TR></TBODY></TABLE>

Any thoughts, hints, suggestions would be greatly appreciated.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
Forgive me if I'm not understanding fully, but could you use COUNTIFS? Does the same as a COUNTIF but with multiple criteria.

Rich
 
Upvote 0
I will check that option right now and report back. I am far from being a Excel'pert but apparently the top brass think otherwise.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
571
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
912
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

Share this page

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
Back
Top