Using Index and Match, returning duplicates.

[monolithicjoe]

Hello,

I am attempting to auto populate a list based on times. I am using this formula and it works great if the shift times are different =INDEX(Sheet1!A:A,MATCH("XXXXXX",Sheet1!C:C, 0))

However, when they are the same, i.e =INDEX(Sheet1!A:A,MATCH("7:00A - 3:30P",Sheet1!C:C, 0)) it will obviously fill in the first name it finds.

Also the reason why all of rows A and C are selected is because the names and times do not always appear in the same row.

Example:
[TABLE="width: 289"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Doe, John[/TD]
[TD][/TD]
[TD]7:00A - 3:30P[/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[TD][/TD]
[TD]7:00A - 3:30P[/TD]
[/TR]
[TR]
[TD]Rodger, John[/TD]
[TD][/TD]
[TD]7:00A - 3:30P
[/TD]
[/TR]
</tbody>[/TABLE]

Will show as:
[TABLE="width: 104"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Doe, John[/TD]
[/TR]
[TR]
[TD]Doe, John[/TD]
[/TR]
[TR]
[TD]Doe, John

But would like:
[TABLE="width: 103"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Doe, John[/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[/TR]
[TR]
[TD]Rodger, John

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[jorismoerings]

Hi,

try something like this:

Book1
	A	B	C	D	E
1	Doe, John	7:00A - 3:30P			7:00A - 3:30P
2	Smith, John	7:00A - 3:30P			Doe, John
3	Rodger, John	7:00A - 3:30P			Smith, John
4					Rodger, John
5
Sheet1

Cell Formulas
Range	Formula
E2	{=IF(ISERROR(INDEX($A$1:$A$3,SMALL(IF($B$1:$B$3=$E$1,ROW($A$1:$A$3)),ROW(1:1)),1)),"",INDEX($A$1:$A$3,SMALL(IF($B$1:$B$3=$E$1,ROW($A$1:$A$3)),ROW(1:1)),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[monolithicjoe]

Hi,

try something like this:

	A	B	C	D	E
Doe, John	7:00A - 3:30P	7:00A - 3:30P
Smith, John	7:00A - 3:30P	Doe, John
Rodger, John	7:00A - 3:30P	Smith, John
Rodger, John

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]{=IF(ISERROR(INDEX($A$1:$A$3,SMALL(IF($B$1:$B$3=$E$1,ROW($A$1:$A$3)),ROW(1:1)),1)),"",INDEX($A$1:$A$3,SMALL(IF($B$1:$B$3=$E$1,ROW($A$1:$A$3)),ROW(1:1)),1))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Thanks for the help! But in order to apply this for my needs I would need it to read off one sheet so it fills into another. I tried to edit it so it references the data in Sheet1 but it does not fill in anything.

 

[jorismoerings]

look at this:

Book1
	A
1	7:00A - 3:30P
2	Doe, John
3	Smith, John
4	Rodger, John
Sheet2

Cell Formulas
Range	Formula
A2	{=IF(ISERROR(INDEX(Sheet1!$A$1:$A$3,SMALL(IF(Sheet1!$B$1:$B$3=$A$1,ROW(Sheet1!$A$1:$A$3)),ROW($A1:$A1)),1)),"",INDEX(Sheet1!$A$1:$A$3,SMALL(IF(Sheet1!$B$1:$B$3=$A$1,ROW(Sheet1!$A$1:$A$3)),ROW($A1:$A1)),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Aladin Akyurek]

Sheet1 (data)

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][/tr][tr][td]

1​

[/td][td][/td][td][/td][/tr]
[tr][td]

2​

[/td][td]Doe, John[/td][td]7:00A - 3:30P[/td][/tr]
[tr][td]

3​

[/td][td][/td][td][/td][/tr]
[tr][td]

4​

[/td][td]Smith, John[/td][td]7:00A - 3:30P[/td][/tr]
[tr][td]

5​

[/td][td]Rodger, John[/td][td]7:00A - 3:30P[/td][/tr]
[tr][td]

6​

[/td][td]Del Monte, Linda[/td][td]9:00A - 4:30P[/td][/tr]
[tr][td]

7​

[/td][td]

​

[/td][td]

​

[/td][/tr]
[/table]

Sheet2 (processing)

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][/tr][tr][td]

1​

[/td][td]7:00A - 3:30P[/td][/tr]
[tr][td]

2​

[/td][td]

3​

[/td][/tr]
[tr][td]

3​

[/td][td][/td][/tr]
[tr][td]

4​

[/td][td]Doe, John[/td][/tr]
[tr][td]

5​

[/td][td]Smith, John[/td][/tr]
[tr][td]

6​

[/td][td]Rodger, John[/td][/tr]
[tr][td]

7​

[/td][td][/td][/tr]
[/table]

In A2 just enter:

=COUNTIFS(Sheet1!$B$2:$B$5,A$1)

In A4 control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$4:A4)>$A$2,"",INDEX(Sheet1!$A$2:$A$5,SMALL(IF(Sheet1!$B$2:$B$5=$A$1,ROW(Sheet1!$A$2:$A$5)-ROW(Sheet1!$A$2)+1),ROWS($A$4:A4))))