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

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

i think i need to see the before and after


Kind regards
Leo

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

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

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

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

please see below comment cheers

Hi Leo,

Please see attached picture. Hope this makes sense.

Cheers

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

Sub test()
Dim myval, x, i, arr
arr = Sheets("sheet1").Range("A1").CurrentRegion
ReDim sn(1 To UBound(arr), 1 To UBound(arr, 2) + 1)
i = 1
For x = 1 To UBound(arr)
    If InStr(1, arr(x, 2), "BOX") = 0 Then
        myval = arr(x, 3)
        sn(i, 1) = arr(x, 1): sn(i, 2) = arr(x, 2): sn(i, 3) = arr(x, 3): sn(i, 4) = myval
        i = i + 1
    Else
        sn(i, 1) = arr(x, 1): sn(i, 2) = arr(x, 2): sn(i, 3) = arr(x, 3): sn(i, 4) = myval + arr(x, 3)
        i = i + 1
    End If
Next
Sheets("sheet2").Range("A1").Resize(UBound(sn), UBound(sn, 2)) = sn
Sheets("sheet2").Columns.AutoFit
End Sub

Kind regards
Leo

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

same same same same same same