Excel 2007 : Formula to find and copy duplicated entries to another worksheet

**Mikey7346** · 06-29-2012, 05:11 AM

Hi,
Need some assistance please. Have attached a small part-section of a spreadsheet where I want to copy only the duplicated entries that are in TAB A into TAB B. I don’t want to list and then colour sort , copy and paste, but would like, if possible, a formula in TAB B that will automatically find and copy the duplicate rows into TAB B eg copy the duplicates highlighted in blue in the sample from rows 14 & 15 and 17 & 18 etc etc in TAB A onto rows 4/5 and 6/7 etc of Tab B keeping the colour coding, which is always blue.
Much appreciated if someone could assist.
Rgds Mikey

**DBY** · 06-29-2012, 10:33 AM

Hello Mikey
If you want to do this with formulas, it might be a good idea to add a helper column to your Table on TAB A, as in my attached example (DupRef). This avoids the use of Array formulas and as it's part of the table, it automatically expands with it, it can also be hidden if you wish. I' m assuming the duplicates are referenced by the Post Code Sector column, as this is where the formula in the helper column looks.

As for the fomatting, you'll have to apply that to TAB B, as formulas only carry across values. You'll also have to expand the number of formulas to cover the number of duplicates. Not sure how big your real data set is but you can try this approach to see how it performs.

Hope this helps.
DBY