I have a dt with some columns as indicated in the excel file attached. Filtering by Package which spans over 52 weeks of rows per package i want to modify the data based on the Retail column and add it to a new dt which be be the final output.
- Is the whole retail column empty for a particular package. If yes then only add 1 row to the new dt with the start date from the first row and the end date from the last row.
(This example is for Package “C” in the file)
For Package A and B, you can see there are values present in the retail column, now if the retail row is empty skip and move ahead with the next row and check its value. If the value in Retail column is $1.98 MB3 and this value remains same over the consecutive next row then do an add data row for this row and the start date would be the first occurence of the row and end date would be from the last occurence of that particular value from the consecutive row found.
So if this value appears from row 5 to 7 then only 1 data row would be added to the new dt with the start date from row 5 and end date from row 7.
Please see attached file. Things would be more clear.
Test1.xlsx (17.8 KB)