Match on column headers then check data in each of those columns

[Til22]

Hi,

I am struggling to get my head around how to approach this.   I have a set of data that has a column for every week of the year.  What I would like to do is to dynamically look up only the five upcoming weeks, and then check that the end users has entered data in each of those five columns, and if they have been left flag them in a new column highlight which weeks do not have data in them.

My list of upcoming weeks to check

This is my source data.  The source data contains every week of the year, but I only want to look at the up coming 5 weeks

Effectively I would only like to lookup the columns in the list of weeks to check, and where data has not been entered into those columns have a new validation column naming the columns which do not have data in them.

I hope this makes sense.  Any help would be appreciated.

cheers,

Til22

[atcodedog05]

Hi @Til22 

Can you provide this data in an excel file so that we can work on a solution using it.

[Til22]

The attached file has a sample of the source data and the list of weeks to check.  Thanks

[atcodedog05]

Hi @Til22 

Here is how you can do it.

Workflow:

1. Using transpose to convert columns to rows.

2. Using join tool to only keep weektocheck in data

3. Using filter tool to keep only nulls (flag)

4. Using summarize tool to create list of missing for each data type.

5. Using find and replace to do a vlookup and join back validation column to main data.

This should also handles changing data.

Hope this helps : )

[Jean-Balteryx]

Hi @Til22 ,

Here is how you can do it ! It transposes the data to join with weeks to check and only keep them. Then it find the missing weeks and concatenates them for each row. It crosstabs back the data with only the five weeks and joins the missing weeks.

EDIT : my workflow returned only the five upcoming dates when it has to return every date. It is corrected !

[Til22]

That's fantastic.  I'll have a go at getting it to work with my real data.  

Thank you!