Need Help in Excel segregation and Splitting

Hi All,

I have a scenario in Excel. I will be having a excel file and it is having some list of data i want to filter data and create new excel files. I will be having duplicate data if i have 6 or more than 6 duplicate rows for the single record we need to make a different excel. Following that if any have 5 duplicates i need to take 1 row from another record make it as 6 rows and need to create new excel.
Can anyone guide me how can i achieve this.

Input file and it have data segregation sheet also ;

challenge.xlsx (16.6 KB)

Output Files i need like this :

Mark.xlsx (9.2 KB)
Stancy.xlsx (9.0 KB)

Hi @Yoichi any help on this


I think it’s necessary to clarify your requirement more, for example key etc. However, how about the following? (15.0 KB)


1 Like

Hi @Yoichi thanks for the response. I will check this code and update you

@Yoichi you are brilliant. This is working perfectly. Thanks for the solution.

We can take 1st column as our medium to check the records and to group instead of email. Just info I’m telling
Just have small query do we have any possibility to modify this like suppose I have some records with 5 duplicates, 4 duplicates and some may have 1 record or 2 records.
Is it possible to make 5 duplicate records and the item having only 1 record to make them a group. Following that 4 duplicates records and 2 duplicate records merged into a single excel. I’m just checking the possibility.
And also is it possible to write the name of the record in last merging write range, instead of 0,1,2 etc


It’s not impossible. However, number of 1 record datatable may be not always same with number of 5 records datatable.It would be necessary complicated logic to consider any situaltion like this, I think.

And also is it possible to write the name of the record in last merging write range, instead of 0,1,2 etc

In the above sample, the following expression will work.



Thanks @Yoichi . Thanks for the solution.

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.