Need help asap- i need to add items to the parent item using a formula

**chris.birlea** · 11-09-2016, 11:15 AM

Good afternoon guys,

Most of my products (Beds, Wardrobes etc) come in more than 1 box and i need to add up the complete items to the additional spare boxes in the inventory.

For example- a complete phoenix ottoman bed is 5 boxes. However, if i have 500 Complete beds (500 X BOX1, BOX2, BOX 3, BOX 4 + BOX 5) and an additional 43 BOX 1's and 62 BOX 4's, all together i have 543 BOX1's and 562 BOX 4's.

Please see attached spreadsheet. I have split the products into alphabetical order. I just need a formula which will add the spare boxes to the complete item to work out the overall amount of boxes as my software (NAV) doesnt do this.

I have tried explaining this the best i can i know its abit confusing.

Your help will be greatly appreciated!!

Kind Regards

Chris

**LeoTaxi** · 11-09-2016, 11:37 AM

i think i need to see the before and after

Kind regards
Leo

**chris.birlea** · 11-09-2016, 11:39 AM

Hi Leo,

I've attached the spreadsheet in question.

I just need to add the boxes adding to the complete product if that makes sense?

Cheers

**chris.birlea** · 11-09-2016, 11:56 AM

Hi Leo,

I've attached the spreadsheet in question.

I just need to add the boxes adding to the complete product if that makes sense?

Cheers

**LeoTaxi** · 11-09-2016, 12:25 PM

Like this ?

Before
PHOTB46OAK 135CM PHOENIX OTTOMAN BED OAK 150
PHOTB46OAK-0-BX1 135CM PHOENIX OTTOMAN BED OAK - BOX 1 0
PHOTB46OAK-0-BX2 135CM PHOENIX OTTOMAN BED OAK - BOX 2 5
PHOTB46OAK-0-BX3 135CM PHOENIX OTTOMAN BED OAK - BOX 3 5
PHOTB46OAK-0-BX4 135CM PHOENIX OTTOMAN BED OAK - BOX 4 3
PHOTB46OAK-0-BX5 135CM PHOENIX OTTOMAN BED OAK - BOX 5 8

After
PHOTB46OAK 135CM PHOENIX OTTOMAN BED OAK 171

**chris.birlea** · 11-09-2016, 12:35 PM

Hi Leo,

No sorry. Like this;

Item Number Product description Inventory Actual inventory
PHOTB46OAK 135CM PHOENIX OTTOMAN BED OAK 150 150 (complete)
PHOTB46OAK-0-BX1 135CM PHOENIX OTTOMAN BED OAK - BOX 1 3 153
PHOTB46OAK-0-BX2 135CM PHOENIX OTTOMAN BED OAK - BOX 2 5 155
PHOTB46OAK-0-BX3 135CM PHOENIX OTTOMAN BED OAK - BOX 3 5 155
PHOTB46OAK-0-BX4 135CM PHOENIX OTTOMAN BED OAK - BOX 4 3 153
PHOTB46OAK-0-BX5 135CM PHOENIX OTTOMAN BED OAK - BOX 5 8 158

Hope this makes sense.

Cheers

**chris.birlea** · 11-09-2016, 12:40 PM

Hi Leo,

Please see attached picture. Hope this makes sense.

Cheers

**chris.birlea** · 11-09-2016, 12:40 PM

please see below comment cheers

**LeoTaxi** · 11-09-2016, 01:11 PM

With this code result go to sheet2
see this is present or change in code

Please Login or Register  to view this content.

Kind regards
Leo

**FlameRetired** · 11-09-2016, 01:36 PM

Edit Never mind this post. I see it, now.

Item Number Product description Inventory Actual inventory

The applying filter to the sample what results do you expect?

Row\Col	A	B	C
1	No.	Description	Inventory
1933	PHOTB46OAK	135CM PHOENIX OTTOMAN BED OAK	150
1934	PHOTB46OAK-0-BX1	135CM PHOENIX OTTOMAN BED OAK - BOX 1	0
1935	PHOTB46OAK-0-BX2	135CM PHOENIX OTTOMAN BED OAK - BOX 2	5
1936	PHOTB46OAK-0-BX3	135CM PHOENIX OTTOMAN BED OAK - BOX 3	5
1937	PHOTB46OAK-0-BX4	135CM PHOENIX OTTOMAN BED OAK - BOX 4	3
1938	PHOTB46OAK-0-BX5	135CM PHOENIX OTTOMAN BED OAK - BOX 5	8

**FlameRetired** · 11-10-2016, 12:19 AM

Try array entering this formula in D2 and filling down.

Formula:

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

I haven't done a painstaking check of the results. What I have checked stands up I believe.

**FlameRetired** · 11-10-2016, 03:00 AM

I've had a chance to do a more thorough check of the results of my post #11.

It has some anomalies.

When the non-zero count of the full sets equals a BOX count the BOX count fails to add. I only found this on a handful of sets. Mostly they have a full set count of 1. There is one with a count of 2 and another of 4 where this happens. I haven't found a work around for this or found any other instances.

Also there is some inconsistency in the format of Descriptions. The full sets occasionally have a "-" in them. My formula relies upon them being in the subsequent BOX items only, and when it happens the results are unpredictable. Fortunately there are very few of them. Again I haven't found a work around for this.

**chris.birlea** · 11-10-2016, 04:41 AM

Wow, i am extremely impressed.

Thank you so much- you should be very proud of yourself.

I appreciate your help so much.

Kind Regards

Chris

**FlameRetired** · 11-10-2016, 01:28 PM

Chris,

You are welcome. Glad to hear it helped.

Thank you for the feedback, kind words and the rep.

Dave

**LeoTaxi** · 11-10-2016, 06:57 PM

same same same same same same

Need help asap- i need to add items to the parent item using a formula

LinkBack

Thread Tools

Rate This Thread

Display

Need help asap- i need to add items to the parent item using a formula

Re: Need help asap- i need to add items to the parent item using a formula

Re: Need help asap- i need to add items to the parent item using a formula

Re: Need help asap- i need to add items to the parent item using a formula

Re: Need help asap- i need to add items to the parent item using a formula

Re: Need help asap- i need to add items to the parent item using a formula

Re: Need help asap- i need to add items to the parent item using a formula

Re: Need help asap- i need to add items to the parent item using a formula

Re: Need help asap- i need to add items to the parent item using a formula

Re: Need help asap- i need to add items to the parent item using a formula

Re: Need help asap- i need to add items to the parent item using a formula

Re: Need help asap- i need to add items to the parent item using a formula

Re: Need help asap- i need to add items to the parent item using a formula

Re: Need help asap- i need to add items to the parent item using a formula

Re: Need help asap- i need to add items to the parent item using a formula

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Yearly cost breakdown per item for multiple items: Issues with formula.

[SOLVED] Macro in excel for converting forecast Child Item (BoM) to Parent Item (SKU)

[SOLVED] Lookup parent then collect sub items

Unable to get the parent item property in a Pivot table using VBA

[SOLVED] Parent child relationships(working out parent item) for each item

"This field has more than 10,000 items under one or more parent items."

need a formula to search for an item in a table and return all items related to it

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions