Data Masking

[suby]

Hello All,

1 - I have 3 sheets ( User, PROUDCT, MARKET)
2 - I want to mask the data in all the three sheets.

Logic 1- On the User TAB i want to mask the all four fields

ID,FIRST NAME,LAST NAME,EMP ID

Where STATE ='ST1' OR 'ST2'

Logic 2 - I want to search in other tabs (PRODUCT and MARKET) and apply the same Logic as above by which means

On PRODUCT TAB the Below two rows should be masked

PROD ID          PROD NAME                                       DESC
P1               RICKY PONTING                                      XXASD
P2               RICKY PONTING ;MARK WESLEY          REDT

On MARKET TAB the Below two rows should be masked
MARKET ID   PROD USER ID     INFO
M1                111;222                    ZZZZZ
M2                 333                         EEEE

I want this to be more Dynamic as we have multiple sheets in the same excel file which needs to be masked.

I can think of Multi Field formula works but i want some help around how to build a Macro which uses a Multi Field Formula so that it checks for each sheet and apply the masking rules where  STATE ='ST1' OR 'ST2.

Attached the sample Data for reference any help would be much appreciated.

[mceleavey]

Hi @suby ,

by "mask" do you mean remove?

[suby]

Hello,

- Yeah it could be any of the below

removing those rows from the Data set OR

Masking using any special characters   OR

Using MD5 

[mceleavey]

@suby ,

I don't know about the logic for the Product and Market sheets as there is no State field there, and no apparent way of joining them, so I've built an example method for the first one, the User tab.

A simple multi-field formula stating that if the State is either ST1 or ST2 then null():

You can then apply that to the other sheets depending on how you are defining the logic for those tabs. If the data joins, then join it prior to the multi-field tool and it should then handle it providing you select all fields at the top.

Hope this helps,

M.

[suby]

Thanks  so much...

That's the challenge i face since we don't have any Joining field its getting complicated so just thought if some one could help me form the community.

[suby]

Jus to Add that's the challenge and the logic would be.

For PRODUCTS Tab take the First Name and Last Name form the User Tab and concatenate them and then search the PRODUCT Tabs to apply the rule.

For MARKET  Tab take the EMP ID from the User Tab and   then search the MARKETS Tabs to apply the rule.

[mceleavey]

Hi @suby ,

Yeah, we won't be able to help you with determining your own logic, but given the info you've supplied I think I can give it a bit of a try.

I've just applied that to get matching records, and then use a full outer join to pull through all records from all inputs, and join them where appropriate, using a combination of the First and Last name on User to join to the name on the product. I then joined PROD ID to PROD USER ID on the Market sheet.

I also, prior to this, split out the records into rows where there was more than one value in the key fields. You might want to check that logic, and if that's not correct, apply a record id and join back using that.

I hope this is close to what you're after.

M.

[suby]

Got it  Thanks  I really appreciate it.