Matching, Counting Extracting

L

LearnExcl

Board Regular
Joined
Mar 17, 2010
Messages
245
Office Version
  1. 2016
Platform
  1. Windows
Hi there!

Okay, I hope this thread does not slip through the cracks like the other ones I started recently.

1. In Sheet1, columns A2:A1000 houses alpha numeric codes (the whole range may/may not be completely filled), some with a preceding 0s
2. In Sheet2, columns G2:G1000 houses alpha numeric codes but they have one number/character preceding the codes (for instance, and "x", "-" or "?")
3. In Sheet2, columns C2:C1000 houses dates in DD-MMM-YYYY format.

What I need to accomplish is, in Sheet1, columns B2:B1000, count the number of occurrences of the every single code housed in Sheet1 (A2:A1000) in Sheet2 (G2:G1000) if the dates housed in C2:C1000 in Sheet2 are between today and 3 months out. Of course there is the challenge of ignoring the preceding 0s attached to the codes in Sheet1 and the preceding characters attached to the codes in Sheet2 while doing the comparison and counting. I need to repeat the same action and find the number of occurrences for 6 months out (between now and 6 months out) and report them under Sheet1, column range C2:C1000.

I very much appreciate your time and effort in helping me out.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
LearnExcl said:
I hope this thread does not slip through the cracks like the other ones I started recently.
Click to expand...
That is often a function of the clarity (or lack of) in the question.

What about posting 15-20 rows of sample data from each sheet and include the expected results from that sample with any further explanation?
Make sure that helpers can copy the sample data to test with. My signature block below gives you a starting point for doing that.
 
Upvote 0
Code: 
Sheet1 (how columns B through E should be populated based on the data in Sheet2 and Sheet3
[TABLE="width: 679"]
<tbody>[TR]
[TD]Code[/TD]
[TD]Increase - 3 Months out
(End of Jun 17)[/TD]
[TD]Decrease - 3 Months out
(End of Jun 17)[/TD]
[TD]Increase 6 Months out
(End of Sep 17)[/TD]
[TD]Decrease - 6 Months out
(End of Sep 17)[/TD]
[/TR]
[TR]
[TD]033C0D[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]3X990[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]44401[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]047G3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]3X992[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]032C0D[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]44421[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]044F3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]041A3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]4N051[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]044T3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]046S3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]3X990[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3X991[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3X992[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3X993[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3X994[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3X995[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3X996[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3X997[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3X998[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3X999[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3X1000[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3X1001[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3X1002[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3X1003[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3X1004[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3X1005[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3X1006[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col></colgroup>[/TABLE]

Sheet2
Code: 
[TABLE="width: 152"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Code[/TD]
[/TR]
[TR]
[TD]10-MAY-2017[/TD]
[TD]-44401[/TD]
[/TR]
[TR]
[TD]10-JUN-2017[/TD]
[TD]-44421[/TD]
[/TR]
[TR]
[TD]10-MAY-2017[/TD]
[TD]-44332[/TD]
[/TR]
[TR]
[TD]10-MAY-2017[/TD]
[TD]-43721[/TD]
[/TR]
[TR]
[TD]10-MAY-2017[/TD]
[TD]-42112[/TD]
[/TR]
[TR]
[TD]31-AUG-2017[/TD]
[TD]-44433[/TD]
[/TR]
[TR]
[TD]10-MAY-2017[/TD]
[TD]X33C0D[/TD]
[/TR]
[TR]
[TD]10-MAY-2017[/TD]
[TD]-44F3[/TD]
[/TR]
[TR]
[TD]10-JUL-2017[/TD]
[TD]-4E051[/TD]
[/TR]
[TR]
[TD]10-MAY-2017[/TD]
[TD]-47G3[/TD]
[/TR]
[TR]
[TD]10-MAY-2017[/TD]
[TD]-4N051[/TD]
[/TR]
[TR]
[TD]10-AUG-2017[/TD]
[TD]X4N051[/TD]
[/TR]
[TR]
[TD]10-JUN-2017[/TD]
[TD]-44401[/TD]
[/TR]
[TR]
[TD]10-JUN-2017[/TD]
[TD]-44401[/TD]
[/TR]
[TR]
[TD]10-MAY-2017[/TD]
[TD]-47G3[/TD]
[/TR]
[TR]
[TD]10-JUL-2017[/TD]
[TD]-47G3[/TD]
[/TR]
[TR]
[TD]10-MAY-2017[/TD]
[TD]X33C0D[/TD]
[/TR]
[TR]
[TD]10-AUG-2017[/TD]
[TD]-44F3[/TD]
[/TR]
[TR]
[TD]10-MAY-2017[/TD]
[TD]-44F3[/TD]
[/TR]
[TR]
[TD]10-MAY-2017[/TD]
[TD]-44F3[/TD]
[/TR]
[TR]
[TD]10-MAY-2017[/TD]
[TD]-44F3[/TD]
[/TR]
[TR]
[TD]10-APR-2017[/TD]
[TD]-4N051[/TD]
[/TR]
[TR]
[TD]10-JUL-2017[/TD]
[TD]-4N051[/TD]
[/TR]
[TR]
[TD]10-JUN-2017[/TD]
[TD]-4N051[/TD]
[/TR]
[TR]
[TD]10-SEP-2017[/TD]
[TD]-4N051[/TD]
[/TR]
[TR]
[TD]10-APR-2017[/TD]
[TD]-4N051[/TD]
[/TR]
[TR]
[TD]10-APR-2017[/TD]
[TD]-46S3[/TD]
[/TR]
[TR]
[TD]10-MAY-2017[/TD]
[TD]-46S3[/TD]
[/TR]
[TR]
[TD]10-MAY-2017[/TD]
[TD]-46S3[/TD]
[/TR]
[TR]
[TD]10-MAY-2017[/TD]
[TD]-41A3[/TD]
[/TR]
[TR]
[TD]10-MAY-2017[/TD]
[TD]-41A3[/TD]
[/TR]
[TR]
[TD]10-MAY-2017[/TD]
[TD]-41A3[/TD]
[/TR]
[TR]
[TD]10-APR-2017[/TD]
[TD]-44401[/TD]
[/TR]
[TR]
[TD]10-APR-2017[/TD]
[TD]-44401[/TD]
[/TR]
[TR]
[TD]10-JUL-2017[/TD]
[TD]-44401[/TD]
[/TR]
[TR]
[TD]10-APR-2017[/TD]
[TD]-44401[/TD]
[/TR]
[TR]
[TD]10-APR-2017[/TD]
[TD]-44401[/TD]
[/TR]
[TR]
[TD]10-APR-2017[/TD]
[TD]-44401[/TD]
[/TR]
[TR]
[TD]10-SEP-2017[/TD]
[TD]-44401[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]

Sheet3
Code: 
[TABLE="width: 152"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Code[/TD]
[/TR]
[TR]
[TD]10-MAY-2017[/TD]
[TD]-44424[/TD]
[/TR]
[TR]
[TD]10-SEP-2017[/TD]
[TD]-44424[/TD]
[/TR]
[TR]
[TD]10-APR-2017[/TD]
[TD]-44401[/TD]
[/TR]
[TR]
[TD]10-SEP-2017[/TD]
[TD]-44401[/TD]
[/TR]
[TR]
[TD]10-SEP-2017[/TD]
[TD]-44424[/TD]
[/TR]
[TR]
[TD]10-SEP-2017[/TD]
[TD]-44424[/TD]
[/TR]
[TR]
[TD]10-SEP-2017[/TD]
[TD]-44424[/TD]
[/TR]
[TR]
[TD]10-SEP-2017[/TD]
[TD]-44424[/TD]
[/TR]
[TR]
[TD]10-SEP-2017[/TD]
[TD]-44424[/TD]
[/TR]
[TR]
[TD]10-SEP-2017[/TD]
[TD]-44424[/TD]
[/TR]
[TR]
[TD]10-SEP-2017[/TD]
[TD]-44424[/TD]
[/TR]
[TR]
[TD]10-SEP-2017[/TD]
[TD]-44424[/TD]
[/TR]
[TR]
[TD]10-AUG-2017[/TD]
[TD]-4N051[/TD]
[/TR]
[TR]
[TD]10-SEP-2017[/TD]
[TD]X4N051[/TD]
[/TR]
[TR]
[TD]11-JUL-2017[/TD]
[TD]-44424[/TD]
[/TR]
[TR]
[TD]10-JUL-2017[/TD]
[TD]-44424[/TD]
[/TR]
[TR]
[TD]10-JUL-2017[/TD]
[TD]-42112[/TD]
[/TR]
[TR]
[TD]10-AUG-2017[/TD]
[TD]-44433[/TD]
[/TR]
[TR]
[TD]10-APR-2017[/TD]
[TD]X33C0D[/TD]
[/TR]
[TR]
[TD]10-SEP-2017[/TD]
[TD]-44F3[/TD]
[/TR]
[TR]
[TD]10-SEP-2017[/TD]
[TD]-4E051[/TD]
[/TR]
[TR]
[TD]10-APR-2017[/TD]
[TD]-47G3[/TD]
[/TR]
[TR]
[TD]10-JUL-2017[/TD]
[TD]-44424[/TD]
[/TR]
[TR]
[TD]10-JUN-2017[/TD]
[TD]-44424[/TD]
[/TR]
[TR]
[TD]10-SEP-2017[/TD]
[TD]-44332[/TD]
[/TR]
[TR]
[TD]10-APR-2017[/TD]
[TD]-43721[/TD]
[/TR]
[TR]
[TD]10-MAY-2017[/TD]
[TD]-44F3[/TD]
[/TR]
[TR]
[TD]10-SEP-2017[/TD]
[TD]-44471[/TD]
[/TR]
[TR]
[TD]10-SEP-2017[/TD]
[TD]-44471[/TD]
[/TR]
[TR]
[TD]10-SEP-2017[/TD]
[TD]-44471[/TD]
[/TR]
[TR]
[TD]10-MAR-2017[/TD]
[TD]-4N051[/TD]
[/TR]
[TR]
[TD]10-AUG-2017[/TD]
[TD]-4N051[/TD]
[/TR]
[TR]
[TD]10-APR-2017[/TD]
[TD]-4N051[/TD]
[/TR]
[TR]
[TD]10-SEP-2017[/TD]
[TD]-44471[/TD]
[/TR]
[TR]
[TD]10-SEP-2017[/TD]
[TD]-44471[/TD]
[/TR]
[TR]
[TD]10-SEP-2017[/TD]
[TD]-46S3[/TD]
[/TR]
[TR]
[TD]10-MAY-2017[/TD]
[TD]-41A3[/TD]
[/TR]
[TR]
[TD]10-APR-2017[/TD]
[TD]-44401[/TD]
[/TR]
[TR]
[TD]10-JUL-2017[/TD]
[TD]-44401[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]
 
Last edited by a moderator:
Upvote 0
So, basically if the code in Sheet1 is existent in Sheet2 and the date of the code is within now and end of Jun 17, then count the occurrences and report them under "Increase - 3 Months out (End of Jun 17)" column. If the code in Sheet1 is existent and the date of the code is within now and end of Sep 17, then report them under "Increase - 6 Months out (End of Sep 17)" column. Same comparison between Sheet1 and Sheet3 will be done but reported under Column C and E respectively.
One thing to note, however, is that the projections need to be dynamic. For instance, 3 month projections done now should be reflecting end of June. But next month (and the following months), it should reflect end of Jul and son on.

Very much appreciate your assistance and support.
 
Last edited:
Upvote 0
OK, thanks for the data & results, that certainly helps. Try these, copied down for the increases. If this is what you want, then you should be able to adapt for the other 2 columns or post back with more details.

Excel Workbook
ABCDE
1CodeIncrease - 3 Months out (End of Jun 17)Decrease - 3 Months out (End of Jun 17)Increase 6 Months out (End of Sep 17)Decrease - 6 Months out (End of Sep 17)
2033C0D20
33X99000
44440182
5047G321
63X99200
7032C0D00
84442110
9044F341
10041A330
114N05143
12044T300
13046S330
143X99000
153X99100
163X99200
173X99300
183X99400
193X99500
203X99600
213X99700
223X99800
233X99900
243X100000
253X100100
263X100200
273X100300
283X100400
293X100500
303X100600
Sheet1
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
VBA - add formula to cell when created
Replies
2
Views
293
Golaidron
G
T
  • Solved
Adding Another Condition to 'Countifs' Formula to not count duplicates
Replies
3
Views
274
Fluff
Fluff
L
  • Solved
VBA remove leading zeros
2
Replies
14
Views
965
Joe4
Joe4
S
  • Question
VBA to ignore certain MAC Address values but process all others
Replies
1
Views
192
myall_blues
myall_blues
B
  • Question
Matching Multiple Header Row Cell Values - Copy the data below to next available row.
Replies
2
Views
218
B-radK
B

Forum statistics

Threads
1,214,951
Messages
6,122,446
Members
449,083
Latest member
Ava19

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