Indexing a array of values

[jimblimm]

my data a1:d10

[TABLE="width: 300"]
<tbody>[TR]
[TD]days
[/TD]
[TD]team 1[/TD]
[TD]team 2[/TD]
[TD]team 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]06[/TD]
[TD]69[/TD]
[TD]09[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]89[/TD]
[TD]99[/TD]
[TD]89[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]36[/TD]
[TD]37[/TD]
[TD]67[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]45[/TD]
[TD]55[/TD]
[TD]45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]09[/TD]
[TD]99[/TD]
[TD]09[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]22[/TD]
[TD]23[/TD]
[TD]54[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]06[/TD]
[TD]24[/TD]
[TD]06[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]77[/TD]
[TD]88[/TD]
[TD]99[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]25[/TD]
[TD]36[/TD]
[TD]45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


the results im trying to get F10:L14

[TABLE="width: 300"]
<tbody>[TR]
[TD]points[/TD]
[TD]1st [/TD]
[TD]2nd[/TD]
[TD]3rd[/TD]
[TD]4th[/TD]
[TD]5th[/TD]
[TD]6th[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]06
[/TD]
[TD]1[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]89[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]99[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]45[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]09[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

in the second set of data, it list the days when the points where given.

i am basically trying to index the days (smallest to Largest(days) when the points where given

EXAMPLE POINT "06" was given on day 1 and day 7

"99" was given on day 2, DAY 5, DAY 8, even if there are duplicates on the same day, it only count as one
"09" was given on day 5

 

[Marcelo Branco]

Maybe

Assuming points is located at F9; 6 at F10, 89 at F11 ...

Array formula in G10 (Excel 2007 or higher)

=IFERROR(INDEX($A$2:$A$10,SMALL(IF(MMULT(IF($B$2:$D$10=$F10,$A$2:$A$10,0),{1;1;1})>0,ROW($A$2:$A$10)-ROW($A$2)+1),COLUMNS($F10:F10))),"")

confirmed with Ctrl+Shift+Enter , not just Enter
(hold down both Ctrl and Shift keys and hit Enter)

copy across and down

M.

 

[hurgadion]

Hi,
I propose the VBA Solution:

Sub Indexing()
Dim tbl(), x&, y&, xran As Range
Dim NoDupes As New Collection

Set xran = Range("B2:D10")

For Each kom In xran
  On Error Resume Next
  NoDupes.Add 1, CStr(kom.Value)
  If Err.Number = 0 Then
    x = x + 1
    ReDim Preserve tbl(1 To x)
    tbl(x) = kom.Value
  End If
Next kom

Cells(10, 6).Resize(x) = Application.Transpose(tbl)

For i = 10 To 9 + x
  y = 0
  Set NoDupes = Nothing
  For Each kom In xran
    If kom.Value = Cells(i, 6).Value Then
       On Error Resume Next
       NoDupes.Add 1, CStr(Cells(kom.Row, 1).Value)
       If Err.Number = 0 Then
          y = y + 1
          ReDim Preserve tbl(1 To y)
          tbl(y) = Cells(kom.Row, 1).Value
       End If
     End If
  Next kom

Cells(i, 7).Resize(, y) = tbl
Next i
End Sub

Best regards.