Design a matrix from two way look up and ranges.

[kuldeepjainesl]

Need a matrix which can populate the count of reported product from the supply data as shown below. Hope to get the solution from experts.

[pre]

"A"        "B"        "C"       "D"     "E"     "F"     "G"     "H"   "I"        "J"
Start 	   End 	      2003	2004	2005	2006	2007	2008  Reported   Year
25001000   25002000   (1)	(0)	(1)	(0)	(0)     (0)   25001025   2003
35001000   35002000   (0)	(0)	(0)	(2)	(0)     (0)   25001096   2005
MXP01000   MXP02000   (0)	(0)	(0)	(0)	(0)     (1)   35001065   2006
A0B10100   A0B10500   (0)	(0)	(0)	(0)	(1)     (0)   35001638   2006
MXP01500   2008
A0B10115   2007

[/pre]
Now "I" and "J" are reported for any issue and may have hundreds of values. These values will be in between range mentioned in "A" and "B" supplied anytime in past. Now the desired output is.


1) Pick the number from "I" Coloum and find out the row number in "A&B "coloum range.

2) Pick the year from "J" Coloum and findout the year in Row "C1" to "H1"

3) In the cell found at the intercetion of above points incrememnt the value by one

4) Sum of the "C"D"E"F"G"H" should be always equal to the values in "I" Coloum.


Details to the problem : Data range :"A" and "B" may have type of values:


25001000-25010000 (Fully Numeric)

MXP00431-MXP04310 (Staring 3 are alphabet while remaining are numbers)

A0B11000-A0B51000 (Starting with Alphabet or number but will always have numeric range in last 3-4 digit)


Thanks in advance to Chandoo and other experts....For any details please ask me at kuldeep.jain@live.com and i will send you the excel file.


Regards,

Kuldeep

 

[Luke M]

In C2:

=SUMPRODUCT(($I$2:$I$30>=$A2)*($I$2:$I$30<=$B2)*($J$2:$J$30=C$1))

Copy formula to the right and down as needed. May need to expand the array call-outs past row 30, if necessary.


Excellent post by the way, showing what you have, what you want, and criteria. =)

 

[herveleger]

e-bookasme b31.8 are certainly going to turn out to be the best buy that you could have ever made. this sort of hatsred bull hats is extremely appreciated by women worldwide.

 

[kuldeepjainesl]

Hi Luke M,


Your formula worked like a charm….You guys are truly awesome or I would say you are a team of logical magicians


I have tested this for a small set of data. I will give it a try on actual database and will update you the results.


Cheers

Kuldeep

 

[Luke M]

Glad to help. Be sure to let us know if the formula doesn't quite work or you learn a cool trick to make it even better. =)

 

[kuldeepjainesl]

Hi Luke M.


I have given it a try to database with 712 entries. It worked well. There are two things I would like to know.


Question 1)


I observed that out of 712 value in "I" coloum, 700 was in range and came in the matrix ("B"-"H") populated but 12 entries could not come as they might are out of range. Is there a way to get these value as list in new coloum?


Question 1) How Sumproduct could work with alphanumeric string for logical comparison?


Regards,

Kuldeep

 

[Luke M]

Thankfully, you can still use the greater than and less than operators when comparing text. This works based on the same rules for how your sort text. The SUMPRODUCT function allows us to build 3 set of true/false arrays, and then it only adds to the total count where all 3 arrays have a true value in the same "spot", ie, text value is greater than A2, less than B2, and is the correct year.


For other readers, Kuldeep has moved Question #1 about how to get a list to this thread:

http://chandoo.org/forums/topic/extract-the-list-of-values-which-are-out-of-ranges-for-a-given-input-values