Grouping of similar data by Macros possible??

[raje]

helo all,
can any one help regarding grouping of data..


i have attached raw file and sample grouping answer values. please find and help me.

Thankyou....

[Richard Buttrey]

Please give examples of the detailed calculations that produce the results in columns T:X.

For instance why does U4 result in an area of 900.45 when the highest area in the 230_600 size is the 3174 in O8

[Winon]

@ Richard Buttrey,

I was about to ask the same question. Tolerances should be consistent even if variations are applied.

Kind Regards.

[raje]

Helo Richard Buttery ...i have attached detailed calculations to produce results..

1)My Grouping is basis of "Size" and Max "Area" Values. so first stage of internal grouping by "Size"

2)Based upon my example sheet data maximum "Size" is 1)300 600 2)230 600 and 3)230 450

3)now i am Sub-divided Group no.1) as a) & b) by Maximum "Area" Values.

4)For example in my Group No.1) contains Area values like 3546, 3258, 3096, 2898, 2790.
here highest "Area" Value is 3546. so cutoff up to -13% value. here -13% value for 3546 is 3546-(33546*0.13) = 3085.02.

5) so my group a) up to 3085.02 Area values. then group a) 3546, 3258, 3096.

6)now we will form Group b), next highest value excluding group a) is 2898...so up to -13% cutoff is
2898-(2898*0.13)=2521.26. then group as b) 2898, 2790.

7) Next size is 230 600. and highest "Area" Value is 3174 from Group 2)3174 & 900.45.
up to -13% cutoff value is 2761.38. but here no items other than 3174. so group individually as c)3174.

8)Remaining Area value is 900.45, then group individually as d)900.45.

9)then same rules for Grops No.3) too and sub divide as e). and finally these values produce at table format.

have a look at my sample format file. need at least upto 2nd stage of grouping

Please help me for producing results..

Thank you!!!

[raje]

@ Richard Buttrey,

I was about to ask the same question. Tolerances should be consistent even if variations are applied.

Kind Regards.

Helo winon...explained calculations and attached sample sheet. Please find.

Thank you.

[Kaper]

Here is formulas - manual sort based solution, which could be used as a startpoint for macro:

1) sort your data on D, E, O descending
2) in P2 and copy down helper formula (first stage):

Please Login or Register  to view this content.

3) in Q2 and copy down Array formula

Please Login or Register  to view this content.

0.87 is of course 100%-13%
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

And there is also macro which implements it:

Please Login or Register  to view this content.

See attached file for the macro. But I do strongly recommend you go through formulas version first to find how it works.

[jindon]

Try

Please Login or Register  to view this content.

[raje]

Here is formulas - handwork based solution, which could be used as a startpoint for macro:

Hi sir,
Thank you for your kind response. i have tried but not getting results. can you share the sheet with formulas?

for the convince and reducing process i have modified sample format. please find modified sample file.

Thank you for helping..

[Kaper]

As for formulas (just to emphasize - second one is an ARRAY formula) - see attachment to this post (based on your data from post #8).
As for macro - check attachment from post above - https://www.excelforum.com/excel-pro...ml#post4790916

[raje]

Here is formulas - manual sort based solution, which could be used as a startpoint for macro:

See attached file for the macro. But I do strongly recommend you go through formulas version first to find how it works.

Thank You Kaper..nice idea for quick analysis.

Thank you for helping

[raje]

Hi Jindon,
Working great!!! Really nice...Thank you for your kind help...

[Richard Buttrey]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

In addition would you change your loacation to something meaningful. It often helps if we need to consider regional settings or date formats. Thanks.

[raje]

Ok Richard sir..

[raje]

Helo jindon,
i entered some data and regarding group 2, highest value is 4482 and 0.87 factor cut off is 3899.34. but C12 Area value is 3546, it is less than 3899
so it might be grouped other than group 2. but results table showing at group 2 sorting. may be you restricted total No. of groups 5...Actually no group restriction sir.. and final request can you arrange group no.s at P column (beside O column)? please see the sample file.

Thank you sir..

[jindon]

CutOff all the way...

Please Login or Register  to view this content.

[raje]

sir Total no. of items are 30. but after grouping showing 51. some items are repeated at groups. before and after grouping items total should be same.

Thank you..

[jindon]

Replace "CutOff" sub routine with below.

Please Login or Register  to view this content.

[raje]

sir, excellent... sir can i ask the reason why it was not working when already other macro present in my excel file or paste it to other file showing error like this image?

Thank you..

[jindon]

No idea.....

[jindon]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[raje]

Thank you Sir..

[raje]

Sir, small error observed at arranging grouping. that is not a major...by size after 300 600, next bigger item is 230 600 after this 230 450.
this is only the correction..everything is perfect.

Thank you..

[Richard Buttrey]

Hi,

As requested earlier would you mind changing your location to something more meaningful please.

[jindon]

Replace "test" sub procedure with the below.

Please Login or Register  to view this content.

[Kaper]

Hi raje,
But ... have you tried my proposition of code from one of first posts on page 1 (the one from 11-24-2017, 02:30 PM)?

It returns exactly the same results as you expect. See screenshot. So why not use solution you've got already last week?

[raje]

Hi Kaper sir,
sorry for re open this thread...may i ask for final file.. i have 2nd type of sheet. comparing to 1st type no of loops are very less. in this Grouping
by Maxium of "PComb" values. this is at "F" column in my sample file. so sort out group by Maximum "Pcomb Value" with upto 0.87 factor
cutoff. for example my maximum PComb value is "1728.83". so cut off up to 1728.83*0.87 = 1504.08. so search value
from 1728.83 to 1504.08 and group them as "1". remaining procedure is same for groups 1,2,3,4,5.

please edit as per this file which is you prepared at post #6.

Thank you..

[Kaper]

1) My strong advice: avoid merged cells.
2) Modified code (note starting from row 4 - because of these merged cells
3) sample file attached - your manual assesment was wrong - note for instance C10 and C11. Moreover, in Sheet 1 you had no data for C17 while on results sheet it was listed