I have a excel file which will get updated with new columns every week in 4 sheets st1 st2 st3 and st4
each sheet has same data format but different numbers
from each sheet i want to extract data from column A which has tickets and out of remaining columns from B onwards we have dates but I want to select latest dates added in the file.
after the dates there are some empty columns and then some working in few columns which I want to ignore.
from each column with selected dates I want to exclude value 0 .
the filtered data without 0 should be append in another sheet which should have tickets and data without 0 for the latest 4 dates which gets included in the file.
i just need new data.
trying to share the how the Excel file looks like.
excel file has 4 sheets st1 st2 st3 st4
from each sheet i want to read data from row 2
from row2 I want data from col A and 4 new columns.
I will filter each new column to exclude 0 and then append to a new sheet col A and filtered column
this will be repeated for all 4 new columns
row two has column A2 with identifiers and starting from B2 we have dates then some empty columns after that there is data which I don't want to include.
next from each new 4 column added which has date I want to exclude data showing 0 for the identifier.
after that i to append the data in a new sheet
sample sheet data is shown below
columns A1 A2 A3 A4 A5 A6
Row1 1 1x 2x 3x 4x 5x 6x
Row 2. identifier 2023071 2023072 2023073 2023074 2023075
row4 x 1 2 3 4 5
row5 b 1 2 3 4 0
row6 c 4 0 6 7 5
manual steps which I want to automate
filter on row 2
filter on latest new 4 column with dates
I will filter one date column and uncheck 0
paste the identifier column and filtered date column data to another sheet.
repeat for remaining 3 columns
there are blank columns after new dates and then some data which is not required.
the file gets updated weekly so I want to repeat this with new dates which gets added.
how to do this in alteryx?
Thank you
