Multi Row & Multi Field ?

Question

All,

Sample Data attached.

-  I have an input Data set with four sheets ( Each sheet has different schema)
-  Firstly i want to Identify the Users who falls under AREA = EUROPE (From sheet4)
-  Take their EMP ID,FIRST NAME and LAST NAME.(From sheet4)
-  Go to sheet1 Check the all the rows of Data and in Multiple Columns to see

If any of those columns has got a EMP ID value who belongs to AREA  = EUROPE

(OR)

If any of those columns has got  FIRST AND LAST  NAME who belongs to AREA  = EUROPE

then  replace those values with Masked Data.
-  Repeat the above for all the sheets (from sheet1 to Sheet4).
-  After masking Output the Result in the same format as Input Data set
-  Provide a separate report on how many rows have been Masked for Each Individual tabs in separate Output File.
-  Provide a separate report on how many columns have been impacted with list of column names and sheet names in separate Output  report.

Sample Data.xlsx

danilang · Answer

hi @suby

Here's a start In Italics

- I have an input Data set with four sheets ( Each sheet has different schema): Use 4 input tools, one for each sheet
-  Firstly i want to Identify the Users who falls under AREA = EUROPE (From sheet4):  add a filter tool where [Area]= "Europe"
-  Take their EMP ID,FIRST NAME and LAST NAME.(From sheet4): Pass these as control parameters into a batch macro
-  Go to sheet1 Check the all the rows of Data and in Multiple Columns to see: Pass sheet 1 as the data into the batch macro

In the Batch Macro

If any of those columns has got a EMP ID value who belongs to AREA  = EUROPE:  Use a Multifield tool to replace the text of the multiple columns if _currentField_=EmpID then "MASKED DATA"

(OR)

If any of those columns has got  FIRST AND LAST  NAME who belongs to AREA  = EUROPE: Use Multifield tools to replace the text of the multiple columns wiht Masked data.  the exact method here will depend on whether the there are multiple First/Last name field pairs

then  replace those values with Masked Data.
-  Repeat the above for all the sheets (from sheet1 to Sheet4).  Repeat for sheet 2 and 3.  Sheet 4 you're comparing to itself 
-  After masking Output the Result in the same format as Input Data set
-  Provide a separate report on how many rows have been Masked for Each Individual tabs in separate Output File.:Have the batch macro return how many rows have at least one with "Masked Data".  Transpose the fields so you only have to look at the Value field.    
-  Provide a separate report on how many columns have been impacted with list of column names and sheet names in separate Output  report.This requirement needs more details

If you run into any specific problems when doing this, post the current version of your workflow with specific questions

Dan

Luke_C · Answer

Hi @suby

You could definitely use a multi-field tool to update the 3 fields in one shot based on if they matched to Europe. It would likely be an IF statement and a Replace() function. The challenge is your data isn't presented in a way that will cleanly let you join it to the employee list to get the area. You'll probably need to do some parsing or cleansing to get to that point.

Give it a shot and attach your workflow if you run into specific issues.

suby · Answer

Hi Danilang,

Thanks i appreciate your comments on this and I'm looking for a start and and I'm wondering if there is simple solution that some one could help with in terms of using the Multifield formula.

I've attached sample Input Data in my previous posts attaching the sample output Data.

Sample Data - Output.xlsx