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

[MilenaKanwal]

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.



[TABLE="class: outer_border, width: 100"]
<TBODY>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Feb[/TD]
[TD][/TD]
[TD]Name[/TD]
[TD]Mar[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Combined List[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Smith[/TD]
[TD]112[/TD]
[TD][/TD]
[TD]Jones[/TD]
[TD]97[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Smith[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Jones[/TD]
[TD]128[/TD]
[TD][/TD]
[TD]Farley[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jones[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Farley[/TD]
[TD]128[/TD]
[TD][/TD]
[TD]Dunham[/TD]
[TD]128[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Farley[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Dunham[/TD]
[TD]104[/TD]
[TD][/TD]
[TD]Carlon[/TD]
[TD]104[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Dunham[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Carlon[/TD]
[TD]120[/TD]
[TD][/TD]
[TD]Grant[/TD]
[TD]120[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Carlon[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Grant[/TD]
[TD]78[/TD]
[TD][/TD]
[TD]Jenson[/TD]
[TD]72[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Grant[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Jackman[/TD]
[TD]128[/TD]
[TD][/TD]
[TD]Bujji[/TD]
[TD]128[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jackman[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Finley[/TD]
[TD]104[/TD]
[TD][/TD]
[TD]Senora[/TD]
[TD]128[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Finley[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Jenson[/TD]
[TD]48[/TD]
[TD][/TD]
[TD]Rishona[/TD]
[TD]128[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jenson[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Bujji[/TD]
[TD]48[/TD]
[TD][/TD]
[TD]Satinka[/TD]
[TD]80[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Bujji[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Senora[/TD]
[TD]48[/TD]
[TD][/TD]
[TD]Goway[/TD]
[TD]42[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Senora[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Hays[/TD]
[TD]60[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Hays[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Rishona[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Satinka[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Goway[/TD]
[/TR]
</TBODY>[/TABLE]

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.

 

[Marcelo Branco]

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.

 

[MilenaKanwal]

Marcelo, that worked beautifully. Thank you so much!

 

[Marcelo Branco]

Marcelo, that worked beautifully. Thank you so much!

You are welcome and thanks for the feedback

M.