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.

	A	B	C	D	E	F	G	H	I
1	Name	Feb		Name	Mar				Combined List
2	Smith	112		Jones	97				Smith
3	Jones	128		Farley	100				Jones
4	Farley	128		Dunham	128				Farley
5	Dunham	104		Carlon	104				Dunham
6	Carlon	120		Grant	120				Carlon
7	Grant	78		Jenson	72				Grant
8	Jackman	128		Bujji	128				Jackman
9	Finley	104		Senora	128				Finley
10	Jenson	48		Rishona	128				Jenson
11	Bujji	48		Satinka	80				Bujji
12	Senora	48		Goway	42				Senora
13	Hays	60							Hays
14									0
15									Rishona
16									Satinka
17									Goway

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

 

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