K
Kevin Lyons
Hello,
I need some assistance counting matches from one workbook to another based
upon date ranges, type, and concatenated first, last, and company names.
For example, consider these two worksheets and their data sets:
Flow.xls
A B
C D
1 Date Type
Name Reference
2 4/2/2004 Purchasing BillGatesMSFT
3 3/25/2004 Support LarryEllisonORCL
4 4/4/2004 Purchasing TomSmithNSCP 4-2
5 4/8/2004 Purchasing BobBrownAAPL
6 4/9/2004 Support JerryAdamsHWP 4-9
7 3/21/2004 Purchasing SusanMurphyINTC 3-19
8 4/4/2004 Support JosephJordanAAPL
9 3/29/2004 Support PatRogersSUNW 3-26
10 4/9/2004 Purchasing KeithMaddoxHWP 4-9
11 3/24/2004 Purchasing SarahOlsenORCL 3-19
12
13 Week
Range Count
14 3-19 thru
3-25 2
15 3-26 thru
4-1 1
16 4-2 thru
4-8 1
17 4-9 thru
4-15 2
Auto.xls
A B C
1 Date Type Name
2 4/2/2004 Purchasing TomSmithNSCP
3 3/26/2004 Support LarryEllisonORCL
4 4/9/2004 Support JerryAdamsHWP
5 3/26/2004 Purchasing BobBrownSUNW
6 3/19/2004 Purchasing SusanMurphyINTC
7 4/2/2004 Support BillChandlerMSFT
8 3/19/2004 Purchasing SarahOlsenORCL
9 4/9/2004 Purchasing KeithMaddoxHWP
10 3/26/2004 Support PatRogersSUNW
11 4/2/2004 Support JoeJordanAAPL
What I need to do is match names and types in the Flow.xls against those in
the Auto.xls file while making certain that the date in the Flow.xls file
occurs on or up to six days after the Auto.xls date for it to be counted in
the above week range section.
I have thousands of rows to consider in both spreadsheets but included the
small subset above. Some of the types differ for the same names so of
course those are not counted. Likewise, a name or two has been changed so
they also do not count. Some of the dates occur before the other data's
dates so they also shouldn't be counted.
In the Reference column I have indicated where the names should reside if
indeed they do get counted. Lastly, I have tabulated manually what the
results would be in the Week Range Count section (D14
17 in the Flow.xls
spreadsheet).
In the range, D1417, I need to use what I am guessing is a combination of
index, match, vlookup, countif, sumproduct, and/or offset to arrive at the
correct totals.
All help is much appreciated!
Thanks much,
Kevin
I need some assistance counting matches from one workbook to another based
upon date ranges, type, and concatenated first, last, and company names.
For example, consider these two worksheets and their data sets:
Flow.xls
A B
C D
1 Date Type
Name Reference
2 4/2/2004 Purchasing BillGatesMSFT
3 3/25/2004 Support LarryEllisonORCL
4 4/4/2004 Purchasing TomSmithNSCP 4-2
5 4/8/2004 Purchasing BobBrownAAPL
6 4/9/2004 Support JerryAdamsHWP 4-9
7 3/21/2004 Purchasing SusanMurphyINTC 3-19
8 4/4/2004 Support JosephJordanAAPL
9 3/29/2004 Support PatRogersSUNW 3-26
10 4/9/2004 Purchasing KeithMaddoxHWP 4-9
11 3/24/2004 Purchasing SarahOlsenORCL 3-19
12
13 Week
Range Count
14 3-19 thru
3-25 2
15 3-26 thru
4-1 1
16 4-2 thru
4-8 1
17 4-9 thru
4-15 2
Auto.xls
A B C
1 Date Type Name
2 4/2/2004 Purchasing TomSmithNSCP
3 3/26/2004 Support LarryEllisonORCL
4 4/9/2004 Support JerryAdamsHWP
5 3/26/2004 Purchasing BobBrownSUNW
6 3/19/2004 Purchasing SusanMurphyINTC
7 4/2/2004 Support BillChandlerMSFT
8 3/19/2004 Purchasing SarahOlsenORCL
9 4/9/2004 Purchasing KeithMaddoxHWP
10 3/26/2004 Support PatRogersSUNW
11 4/2/2004 Support JoeJordanAAPL
What I need to do is match names and types in the Flow.xls against those in
the Auto.xls file while making certain that the date in the Flow.xls file
occurs on or up to six days after the Auto.xls date for it to be counted in
the above week range section.
I have thousands of rows to consider in both spreadsheets but included the
small subset above. Some of the types differ for the same names so of
course those are not counted. Likewise, a name or two has been changed so
they also do not count. Some of the dates occur before the other data's
dates so they also shouldn't be counted.
In the Reference column I have indicated where the names should reside if
indeed they do get counted. Lastly, I have tabulated manually what the
results would be in the Week Range Count section (D14
17 in the Flow.xlsspreadsheet).
In the range, D14
17, I need to use what I am guessing is a combination ofindex, match, vlookup, countif, sumproduct, and/or offset to arrive at the
correct totals.
All help is much appreciated!
Thanks much,
Kevin