Combine 2 lists into 1 w/ no duplicates. Formula/function only.

MilenaKanwal

New Member
Joined
Mar 18, 2014
Messages
12
I have 2 timesheet lists that are delivered monthly, and the length varies significantly each month. I need to form one list that shows all of the people from both lists... without duplicating if someone is on both lists.

I have written a formula that works, with one little hiccup. There's a cell that returns a value of '0', but I can't figure out where it is coming from. I've selected all cell that are unpopulated, and hit the 'delete' button to ensure that there wasn't a cell with a space that was causing this issue.

The formula I'm using is:

=IFERROR(IFERROR(INDEX($A$2:A$250, MATCH(0, COUNTIF($I$1:I1,$A$2:$A$250), 0)), INDEX($D$2:$D$250, MATCH(0, COUNTIF($I$1:I1, $D$2:$D$250), 0))), "")


Where list 1 begins at A2, List 2 begins at D2 and the combined list begins at I2 (row 1 houses the colum headers). I've copied the formula downward, so the un-anchored I1 (in blue above) changes to include additional cells all the way down. You can see below that the formula is successful, except for in cell I14.



ABCDEFGHI
1NameFebNameMarCombined List
2Smith112Jones97Smith
3Jones128Farley100Jones
4Farley128Dunham128Farley
5Dunham104Carlon104Dunham
6Carlon120Grant120Carlon
7Grant78Jenson72Grant
8Jackman128Bujji128Jackman
9Finley104Senora128Finley
10Jenson48Rishona128Jenson
11Bujji48Satinka80Bujji
12Senora48Goway42Senora
13Hays60Hays
140
15Rishona
16Satinka
17Goway

<TBODY>
</TBODY>

I'm using a range of 2 through 250 to accomodate the large fluctuations in the size of each list.

I appreciate any help you can offer me.

PS- How can I include a screenshot image or file? My permissions are set to 'You may not post attachments' and pasting an image in the body of this thread is seemingly impossible.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,999
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Maybe this

I2
=IFERROR(IFERROR(INDEX($A$2:A$250, MATCH(0, IF($A$2:$A$250<>"",COUNTIF($I$1:I1,$A$2:$A$250)), 0)), INDEX($D$2:$D$250, MATCH(0, IF($D$2:$D$250<>"",COUNTIF($I$1:I1, $D$2:$D$250)), 0))), "")

Ctrl+Shift+Enter

copy down

M.
 
Last edited:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,253
Messages
5,769,051
Members
425,512
Latest member
wannabe_excel_wiz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top