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