Multiple replacement items

[charizard]

Dear all, I am having an Excel issue that I am trying to solve for quite long. I have items that I sell on a regular bases. At one time, that item is replaced with another one. My challenge is to transfer sold quantities to substitute item, together with qty sold of the original item. The most challenging are the duplicates, meaning that one item can be replaced by two same items. Just to mention, there are more than 190.000 items that might be replaced anytime, so I would like to make this as automatic as possible. Thanks in advance. Attached is an example of what I am trying to accomplish.


Excel replacement issue.PNG

[Pepe Le Mokko]

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[charizard]

Hi Pepe Le Mokko,

Thanks for your suggestions. The sample workbook is now uploaded, with before and after sheets.

[63falcondude]

Here's how I would solve this:

F2 =IF(C2="No replacement",A2,C2)

A12 =INDEX(F$2:F$6,MATCH(0,COUNTIF(A$11:A11,F$2:F$6),0)) Ctrl Shift Enter

B12 =SUMPRODUCT((B$2:B$6+D$2:D$6)*((C$2:C$6=A12)+(A$2:A$6=A12)))

Drag all formulas down.

See attachment.

[Pepe Le Mokko]

Hi Pepe Le Mokko,

Thanks for your suggestions. The sample workbook is now uploaded, with before and after sheets.

Most welcome