Complicated Data analysis

[idkman]

I have data sets that I need to make into groups based on a certain set parameters. So first I'd like to have the VBA scan the data and group together entries that are similar by one data point. Then do that again and group those groups by another data point. Then of the ones that are grouped with that similarity I want to optimize the data to be in another group based on another set of data in the array and these groups have a set maximum value that it can not be more than. after that, I need it to take those groupings and optimize it again to pair the groupings based on a data value but this time with a maximum value as well that the groups need to add to.

So For example: If i was talking about books, I have 2,000 books and I wanted to group books together that are all the same author, then group by the same genre, Then once they are grouped by author and genre I need it to add to being a specific length I have for my book shelf but I also need to know what height to build my shelves at and I have a max shelf height. So I need it to calculate what height the shelf will need to be at and which books can fit on one shelf together based on the length of my bookcase, and Ideally also include how many shelves total I will need and how many bookcases.

I've tried a lot of different things and nothing has really been seamless and automated the way I want it to. I would like to run the vba program and have it to all of that mathematically and seamlessly, I've also tried using solver in conjunction with VBA to get the optimization but it hasn't worked how I need it to. Please let me know if you have an suggestions of how to get this to run seamlessly the way I need it to, I'm open to any and all suggestions. Thanks.

 

[awoohaw]

Welcome to the Mr. Excel forum. Mr. Excel has a great tool that helps the you help the forum. It is called xl2bb add in (link below). It allows you to share a mini worksheet with formulas, formatting, etc. to the forum so the forum doesn't have to guess how to build your scenario. Please share a portion of your data (in your example columns seem to be more important that rows, so maybe 15-20 rows of all your populated columns).

Thanks in advance.

But, to address your question, why are you seeking a VBA solution? A power query or power pivot solution may provide you with a solution. Even a simple pivot table could work.

Regarding your bookshelf? Do you already have a width and height of the shelving unit? If you have multiple shelving units, do you want to wrap from shelf to shelf within the unit or across combined width of all units?

 

[idkman]

Thank you for your response. I'm open to any way to get too the solution I need but I have the most experience in VBA and haven't used anything else. With the shelving, the height of the shelves are variable but the length and width are fixed. The total height of the book case would be fixed as well but were the shelves go would change. Also Any data longer than half the shelf length, and deeper than the shelf needs to be disregarded. And once things are placed with a location, It can be disregarded as well.

I'm unable to use the xl2bb but attached is an image. So with this specifically it would be first grouped by location which there are a lot of locations, then by height which ranges from 119-6, then we would want to take those and find combinations of tools that the of lengths add up to no more than 140in but no less than 130in, so one tool can't be more than 70in long and it can't be more than 38 deep. And then once we have combinations that will fit on a single shelf we would want it to make a rack that has 3 shelves but is no more than 168in tall and has 12in of empty space between the height of the tool and the shelf above.

 

[awoohaw]

if you can't use the xl2bb then please post the data as a table. Images while helping to visualize really do not save the forum any time. As they have to completely enter your data manually. and that transcribing can have typos/omissions/etc and lead to errors.

 

[idkman]

Item #	tool #	Location	Length	Depth	Height	New Location
603-0698_00__2BK3	TL004331_BK3_	AA-20	78	242	57
58B-54-04580_01__2BG1	TL002342	GG-17	126	149	77
58B-80-50260_00__2BG1	TL006422	GG-17	107	148	60
EM8436_01_XC	TL002012	GG-17	107	148	60
58F-54-31300_00__2BG1	TL002349	GG-17	61	112	79
333-0646_00_BK3	TL001083	FF-14	77	102	83
58D-54-04890_00__2BG1	TL003605	EE-16	103	97	96
612-7837-G_00__BK3	TL001796	OT-15	50	97	55
EG0632_00__YEPK	TL001051	FF-13	119	95	86
XB3057_002_XC	TL001168	GG-10	79	93	90
58F-54-01822_01__2BG1	TL001175	GG-12	79	93	90
58F-54-20280_00__2BG1	TL004048	GG-12	79	93	90
EK1208_000_XC	TL001173	GG-11	98	81	59
EM5037_000_XC	TL001139	GG-5	79	81	51
58B-54-01052_00__2BG1	TL001052	FF-10	109	80	90
212-0020_00_YE1P	TL000726	CC-12	48	80	82
310-1567-G_01_BK3	TL000920	EE-7	70	76	90
582-7718_01_A__BK3	TL003045	OT-18	86	74	90
EJ7468_001_XC	TL001171	GG-11	143	74	82
212-0023_01__YEP	TL000721	CC-11	40	74	80
572-7590_00__2BK3	TL005108	BB-6	78	74	20
269-8688_08	TL001182	GG-15	73	73	71
58B-54-70470_01__2BG1	TL006683	OT-23	97	70	83
571-5657-G_01__BK3	TL004351	DD-8	56	68	98
444-8758_00_BK3	FX002085	EE-17	97	67	94
AFH210291_J__GN1	FIXF-AF06116	EE-7	62	67	46
58E-54-22041_00__2BG1	TL003231	EE-16	60	65	83
EK6627_000_XC	TL001148	GG-7	65	65	81
474-4562_00__BK3	FX001527	FF-19	64	64	50

 

[idkman]

Item #	tool #	Location	Length	Depth	Height	New Location
333-9678_00_YE1P	TL000728	CC-12	55	38	95
350-7161_01__BK3	TL000538	BB-6	35	38	83
205-4106_01_BK3	TL000506	BB-1	65	38	71
301-0964-1_01	TL001821	OT-21	66	38	69
368-4425-G_01__BK3	TL000602	BB-16	48	38	56
234-0385_05__2BK3	TL000621	BB-18	29	38	55
357-4843_04__2YE1	TL001778	AA-1A	31	37	75	AA-1A
435-3943_00_BK3	TL001186	GG-16	58	37	50
486-0499_00__BK3	TL001766	OT-11	54	37	50
EM4797_000_XC	TL001114	GG-2	42	37	46
276-3113_05_BK3	TL000559	BB-9	60	37	45
418-3682_03__BK3	TL001785	OT-13	51	37	44
243-4248_00_BK4	TL000676	CC-5	37	37	25
299-3957_02_BK3	TL000803	DD-8	48	36	82
256-1370_00__YEP	TL000809	DD-9	48	36	82
367-2568_04__2BK3	FX002668	OT-17	44	36	76
540-0865-G_02__2BK3	TL005157	OT-23	50	36	76
XB3780_02__2BG1	TL000923	FF-1	52	36	75
456-0270_01_BK3	TL001181	GG-14	56	36	74
AA108048_A__2GN1	TL006668_BK3_	EE-14	57	36	66
AA108049_A__2GN1	TL006670_BK3_	FF-9	63	36	66
487-0512_00__BK3	TL001805	OT-10	39	36	64
511-0868-G_00__BK3	TL001446	BB-4	36	36	57
333-9675_00_YE1P	TL000582	BB-13	44	36	55
465-5920_00_2BK3	FX001250	FF-17	45	36	54
365-0742_00_BK3	TL000457	AA-12	37	36	52
EL3192_003_XC	TL000998	FF-3	55	36	52
EL6117_000_XC	TL001142	GG-5	49	36	52
347-9752_02_BK3	TL000472	AA-13	65	36	51
215-9191_00_YE1P	TL000902	EE-4	48	36	51
569-5245-G_00__2BK3	FX002578	EE-9	60	36	50
58E5410000_00_XC	TL001117	GG-2	58	36	50
365-0729_00_BK3	TL000465	AA-12	49	36	48