• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Modify the formula for fetching names from all Sheets of the file without Duplication

Hany ali

Active Member
Hello My Dear ... Please kindly help me in adjusting this equation and making it work. It is the equation of fetching all the names from the pages starting from page 1 to page 4 without repetition and placing them on the Total page starting from cell B2
Code:
=IFERROR(SMALL(('1'!$B$2:$B$12,'2'!$B$2:$B$38,'3'!$B$2:$B$40,'4'!$B$2:$B$41),1+SUMPRODUCT(COUNTIF(B$1:B1,CHOOSE({1,2,3,4},'1'!$B$2:$B$12,'2'!$B$2:$B$38,'3'!$B$2:$B$40,'4'!$B$2:$B$41)))),"")
 

Attachments

bosco_yip

Excel Ninja
Try to use Excel Consolidate build-in function to merge sheets and remove duplicates

1] Add a Helper column in Sheet "1", D2:D12 and all cells filled with value of 1

2] In Data tab >> click Consolidate >>

>> In Function select Sum

>>In Reference, use button to access the data needed

>> To add the next dataset, click Add and then select the data in the same way.

>> Do this for all the datasets that you want to merge.

>> Check the box titled Left column

>> Finally, click OK.

3] Remove all Helper Columns as required.

4] Then, Column B is a merged unique list from 4 sheets.

77527
 

Attachments

bosco_yip

Excel Ninja
thanks alot ... good work ,if possible by Function
Formula solution,

1] For Excel 2019 and above.

In Sheet "Total" B2, formula copied down :

=IFERROR(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",1,'1'!B$2:B$12,'2'!B$2:B$38,'3'!B$2:B$40,'4'!B$2:B$41)&"</b></a>","//b[not(preceding::*=.)]["&ROW(A1)&"]"),"")

2] For all Excel versions.

In Sheet "Total" B2, array (CSE) formula copied down :

=IFERROR(INDEX('1'!B$2:B$12,MATCH(0,COUNTIF(B$1:B1,'1'!B$2:B$12),0)),IFERROR(INDEX('2'!B$2:B$38,MATCH(0,COUNTIF(B$1:B1,'2'!B$2:B$3812),0)),IFERROR(INDEX('3'!B$2:B$40,MATCH(0,COUNTIF(B$1:B1,'3'!B$2:B$40),0)),IFERROR(INDEX('4'!B$2:B$41,MATCH(0,COUNTIF(B$1:B1,'4'!B$2:B$41),0)),""))))

Regards
 
Top