How to put a function/formula for Lookup , return , repeat ,Logical test

[SUDARSAN]

Dear Forum Friend,

Based on Crop the implement should match the crop and repeat how many times crop is repeating (howmany times Crop is repeating that many times surrten or matching implement(2) and implementusage(3) usage should repeat )
Based on Implement the HP segment should match the Implement and repeat according to the implement how many times repeating (howmany times implement is repeating that many times surrten or matching Hp usage (4) HPsegment(5) should repeat )
Based on Hp segment the District should match with Hp segment and repeat according to surrten HP segment how many times repeating (howmany times HP segment is repeating that many times surrten or matching District (6) should repeat )
Based on District the State should match with with district and repeat according to surrten District how many times repeating (howmany times District is repeating that many times surrten or matching State (7) should repeat )
Based on State the Country should match with with State and repeat according to surrten State how many times repeating (howmany times Statet is repeating that many times surrten or matching Country(8) should repeat )
Based on weight (CW,SW,DW,HW,IW,CRW) it should return Major and All if value is1000 and above if it below 1000 it should return All only and match

Sheet attached


Thank you
Regards
Sudarsan

 

[pecoflyer]

Some suggestions you may find useful:

1. Our answerers are looking to "help" more often than they are looking to "do an entire project for you for free".
2. Jump into this project yourself, when you get stuck on ONE specific hurdle, post that ONE item as a forum question.
3. Show us what you've tried in an attached sample workbook and how it's not working for you. When you're doing the work yourself, just writing out a complete example question in a forum often points you to solution before you even finish posting.

 

[SUDARSAN]

Some suggestions you may find useful:

1. Our answerers are looking to "help" more often than they are looking to "do an entire project for you for free".
2. Jump into this project yourself, when you get stuck on ONE specific hurdle, post that ONE item as a forum question.
3. Show us what you've tried in an attached sample workbook and how it's not working for you. When you're doing the work yourself, just writing out a complete example question in a forum often points you to solution before you even finish posting.

Thank you

I have attached sheet below which I have tried and confused my self what to do

really sorry for the inconvenience caused

if possible go through my sheet and please help

regards
Sudarsan

 

[pecoflyer]

So you are using XL365?
A pity this forum does not allow to add this info to the public profile..

 

[Peter Bartholomew]

It's beginning to take shape. You are working with a curious mix of 'old-school' techniques and dynamic array techniques.
@pecoflyer Do you have Excel 365?

 

[SUDARSAN]

It's beginning to take shape. You are working with a curious mix of 'old-school' techniques and dynamic array techniques.
@pecoflyer Do you have Excel 365?

Thank you
yes I Have Office 365 Mac version

 

[SUDARSAN]

It's beginning to take shape. You are working with a curious mix of 'old-school' techniques and dynamic array techniques.
@pecoflyer Do you have Excel 365?

Mr Peter,

Thanks a lot you saved my lot of time and clear my confusion , you really have amazing skills ,

thanks a lot

I have small doubt

can we make that

if value is 1000 then it should return
Major
All
if value is < 1000 it should return
All
I tried with =if ( A1 = 1000,"Major","All")

if possible please help

Thanking you once again

Regards
SUDARSAN

 

[SUDARSAN]

It's beginning to take shape. You are working with a curious mix of 'old-school' techniques and dynamic array techniques.
@pecoflyer Do you have Excel 365?

Sorry for the disturbance Mr Peter ,

the sheet working fine but the Region only changing according to given value not districts and HP can you please have look on this

I am trying my level best still need your support

regards
SUDARSAN

 

[SUDARSAN]

It's beginning to take shape. You are working with a curious mix of 'old-school' techniques and dynamic array techniques.
@pecoflyer Do you have Excel 365?

Hi Mr Peter Bartholomew,
you helped me lot ,
can you please explain the formula so that I will try to learn and try to modify according my need

I need to control District, Region, Hp, M/All independently as required

Thanking you

Regards
SUDARSAN

 

[Peter Bartholomew]

I had noticed that Hp and M/All generated 8 values for each district and built that into the formula to make it simpler. Since that is no longer the case (you also mentions some value somewhere that influences what is to be listed), it is now necessary to perform the accumulation of counts at the more detailed level. The coarser cumulative totals corresponding to State can then be calculated from the detailed version.
Any cumulative total is a computationally intensive calculation relative to others within the workbook (as an array calculation) but allows you to look up the attributes associated with any given member of the full list (as shown in the figure).

 

[SUDARSAN]

I had noticed that Hp and M/All generated 8 values for each district and built that into the formula to make it simpler. Since that is no longer the case (you also mentions some value somewhere that influences what is to be listed), it is now necessary to perform the accumulation of counts at the more detailed level. The coarser cumulative totals corresponding to State can then be calculated from the detailed version.
Any cumulative total is a computationally intensive calculation relative to others within the workbook (as an array calculation) but allows you to look up the attributes associated with any given member of the full list (as shown in the figure).
View attachment 74157

Thank you Mr Peter
I will go through
my heart full gratitude

regards
SUDARSAN