jeffdolton
Board Regular
- Joined
- Dec 21, 2020
- Messages
- 100
- Office Version
- 2010
- Platform
- Windows
Hi, Firstly thanks to Maabadi who has given me great code for my project. Here is a OneDrive link to my workbook example file2.xlsm
I receive a weekly data download from an EPOS system as shown in columns A:L in the first tab. The items purchased are shown in column L. Mabaadi has given me code that splits the string in column L for each type of item purchased and has shown the first item purchased in column M and the quantity of items purchased in column N. Subsequent items purchased and their quantity are shown in steps of five columns. This leaves three blank cells to the right of the sequence beginning with column N, which are named Category, Unit Price and Total Price.
I'm looking for code please to insert a XLOOKUP function in each of the first two blank columns and a simple multiplication of the first two columns in the third column. I've highlighted the columns O:Q where I have manually inserted the function and this shows what it should look like.
The split string code allows for up to 50 separations from data in column L. The lookup table is in the second tab and the data below the headings in yellow is the product table output from the EPOS system. Typically, this output format doesn't match the receipts report and so, by an agricultural method, I've produced the identical description match in column Q of the lookup table. If you also know of a way to simplify this, I would be grateful.
Many thanks for your help.
I receive a weekly data download from an EPOS system as shown in columns A:L in the first tab. The items purchased are shown in column L. Mabaadi has given me code that splits the string in column L for each type of item purchased and has shown the first item purchased in column M and the quantity of items purchased in column N. Subsequent items purchased and their quantity are shown in steps of five columns. This leaves three blank cells to the right of the sequence beginning with column N, which are named Category, Unit Price and Total Price.
I'm looking for code please to insert a XLOOKUP function in each of the first two blank columns and a simple multiplication of the first two columns in the third column. I've highlighted the columns O:Q where I have manually inserted the function and this shows what it should look like.
The split string code allows for up to 50 separations from data in column L. The lookup table is in the second tab and the data below the headings in yellow is the product table output from the EPOS system. Typically, this output format doesn't match the receipts report and so, by an agricultural method, I've produced the identical description match in column Q of the lookup table. If you also know of a way to simplify this, I would be grateful.
Many thanks for your help.