Hello! I have an unusual situation where a Product Code is referenced in a single cell. I need to split this information into multiple lines and adjust the Amount accordingly. If there's three Product Code, I need to split it into three lines and divide the Amount by three. However, if there are two Product Codes, I need to split it into two lines and divide by two. Depending on the number of Product Codes, the lines should be added at the bottom. Please refer to the example below. The delimiters used are spaces, slashes, and commas. I would greatly appreciate your assistance.
Table | ||
Product | Product Code | Amount |
Apple | 1.234.2,2345 | 100 |
Orange | 1.345/3456/2345 | 90 |
Pear | 23.4 5643 8673 | 90 |
Expected Output | ||
Product | Product Code | Amount |
Apple | 1.234.2 | 50 |
Apple | 2345 | 50 |
Orange | 1.345 | 30 |
Orange | 3456 | 30 |
Orange | 2345 | 30 |
Pear | 23.4 | 30 |
Pear | 5643 | 30 |
Pear | 8673 | 30 |
Solved! Go to Solution.
@PhilipMannering
Thank you for providing a solution. While it has partially resolved the issue, I have a concern regarding the summarise tool counting Apple at the bottom (i've added new rows- see below), which results in an incorrect summarize tool total count of 5. My intention is to obtain the respective count of the product code only. Could you please advise if there is a solution to this?
Table | ||
Product | Product Code | Amount |
Apple | 1.234.2,2345 | 100 |
Orange | 1.345/3456/2345 | 90 |
Pear | 23.4 5643 8673 | 90 |
Apple | 88/78/65 | 150 |
Expected Output | ||
Product | Product Code | Amount |
Apple | 1.234.2 | 50 |
Apple | 2345 | 50 |
Orange | 1.345 | 30 |
Orange | 3456 | 30 |
Orange | 2345 | 30 |
Pear | 23.4 | 30 |
Pear | 5643 | 30 |
Pear | 8673 | 30 |
Apple | 88 | 50 |
Apple | 78 | 50 |
Apple | 65 | 50 |