Combining sheets and getting output in table format without NULL value.

Question

Hi,
I am working on solution where I have to combine 4 sheets from 1 excel without duplicate entries, after combining them getting data in 2 part in table format (Part 1 - Processor count and part 2 - verifier count) below is the part of Processor count which have null value and repeating transaction no.

I wanted it to have unique transaction value and the count to be in processor column against transaction type and same goes for verifier

attach is the sample files.

Any help appreciated!!!!!!!

dummy worklog.xlsx

binu_acs · Accepted Answer

@Jyoti12777 one way of doing this with the help of batch macro

binu_acs · Accepted Answer

@Jyoti12777  If one processor has an entry in the Transaction Type and other processor has no entry then it will show as null for that person, for example if you look at your input file - sheet STP_PDBS, the transaction Type Mol01 has two entries for the processor person1 but nothing for processor 2, so if you take the count it will show as below

make sure that you have data present for all processor in all Transaction type

From your output i think Jyoti and Prashant has all the Transaction Type present, that is why it is not showing any NULL values

If you think that is not the case then can you give an example of the input file and expected output then i will take a look