# 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

• 20.4 KB Views: 7

#### 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.

#### Attachments

• 44.5 KB Views: 3

#### 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

#### Hany ali

##### Active Member
Excellent.. you are really creative, my honorable professor, and this is really what is required.. thank you very much