jeffdolton
Board Regular
- Joined
- Dec 21, 2020
- Messages
- 100
- Office Version
- 2010
- Platform
- Windows
Hi,
I’ve opened up another thread as my request this time is a little different. With help from experts on this forum, and using some basic excel logic, I’ve managed to separate purchase data from an EPOS system. I would like to consolidate all of this into just two functions if possible; one to show the product purchased and one to show the number of products purchased in one transaction.
The EPOS data extract shows items purchased in one transaction and the items are separated by commas, although options selected are separated by a comma within parentheses.
An example of a single transaction is:
2 x bacon bap 2 sausages, Bottled beer 500ml, 3 x Coffee (Own Cup), Polo Shirt (Navy, Large)
In this case four products have been purchased in one transaction. A weekly EPOS extract can have hundreds of rows of transactions.
I need to separate each product purchase on the same row as follows:
Selection 1. Selection 2. ..... Selection 15
Product. No Product. No. Product No.
I’ve catered for up to 15 different products being purchased under the one transaction.
As I see it the following is required:
i. Separate each product group contained within a comma (but not the option separated by a comma in brackets).
ii. Leave the comma within the parentheses as this is how a lookup table appears . If this is too difficult then these commas can stay (there can be up to three commas within brackets)
iii. Separate the first number in the product group and if there is no number return a value of 1.
iv. Remove the space x space from the selection. .
So the output should look like this.
Selection 1. Selection 2 Selection 3. S Selection 4
Product No. Product. No. Product. No. Product. No.
bacon bap 2 sausages 2 Bottled beer 500ml. 1 Coffee (Own Cup) 3 Polo Shirt ( Navy Large 1
As always, many thanks for your help.
I’ve opened up another thread as my request this time is a little different. With help from experts on this forum, and using some basic excel logic, I’ve managed to separate purchase data from an EPOS system. I would like to consolidate all of this into just two functions if possible; one to show the product purchased and one to show the number of products purchased in one transaction.
The EPOS data extract shows items purchased in one transaction and the items are separated by commas, although options selected are separated by a comma within parentheses.
An example of a single transaction is:
2 x bacon bap 2 sausages, Bottled beer 500ml, 3 x Coffee (Own Cup), Polo Shirt (Navy, Large)
In this case four products have been purchased in one transaction. A weekly EPOS extract can have hundreds of rows of transactions.
I need to separate each product purchase on the same row as follows:
Selection 1. Selection 2. ..... Selection 15
Product. No Product. No. Product No.
I’ve catered for up to 15 different products being purchased under the one transaction.
As I see it the following is required:
i. Separate each product group contained within a comma (but not the option separated by a comma in brackets).
ii. Leave the comma within the parentheses as this is how a lookup table appears . If this is too difficult then these commas can stay (there can be up to three commas within brackets)
iii. Separate the first number in the product group and if there is no number return a value of 1.
iv. Remove the space x space from the selection. .
So the output should look like this.
Selection 1. Selection 2 Selection 3. S Selection 4
Product No. Product. No. Product. No. Product. No.
bacon bap 2 sausages 2 Bottled beer 500ml. 1 Coffee (Own Cup) 3 Polo Shirt ( Navy Large 1
As always, many thanks for your help.