Create Unique List of Names & Frequency Across 6 Columns

[Jzfbkr]

If anyone can help me I would be very gratefull.

I am looking to create a formula that will create a unique list of names, in a different location that considers 6 columns worth of data as well as the how frequent each name occurs.


Here is an example of what Im working with:

[TABLE="width: 815"]
<colgroup><col><col span="2"><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="class: outer_border, width: 500, align: left"]
<tbody>[TR]
[TD]1st Goalscorer[/TD]
[TD]2nd Goal Scorer[/TD]
[TD]3rd Goal Scorer[/TD]
[TD]4th Goal Scorer[/TD]
[TD]5th[/TD]
[TD]6th[/TD]
[/TR]
[TR]
[TD]Player A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Player B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Player C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Player D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Player C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Player A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Player E[/TD]
[TD]Player F[/TD]
[TD]Player A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Player A[/TD]
[/TR]
[TR]
[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]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Any help with this would be massively appreciated.

Thanks

 

[XOR LX]

Hi,

First go to Name Manager (Formulas tab) and define the following:

Name: Range1
Refers to: =$A$1:$F$8

(Or whatever happens to be the range in question.)

Name: Arry1
Refers to: =ROW(INDIRECT("1:"&COLUMNS(Range1)*ROWS(Range1)))

Name: Arry2
Refers to: =T(OFFSET(INDEX(Range1,1,1),INT((Arry1-1)/COLUMNS(Range1)),MOD(Arry1-1,COLUMNS(Range1)),,))

Exit Name Manager.

Enter this formula in G1:

=SUMPRODUCT((Range1<>"")/COUNTIF(Range1,Range1&""))

Then this array formula** in your first cell of choice:

=IF(ROWS($1:1)>$G$1,"",INDEX(Arry2,SMALL(IF(FREQUENCY(IF(Range1<>"",MATCH(Range1,Arry2,0)),Arry1),Arry1),ROWS($1:1))))

Copy down until you start to get blanks for the results.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).</SPAN></SPAN>

 

[Aladin Akyurek]

Add the following code for ARRAYUNION to your workbook using Alt+F11.

Function ArrayUnion(ParamArray Arg() As Variant) As Variant
' Code: Juan Pablo González
' Spec: Aladin Akyurek
' May 4, 2003
' Ref: TinyURL.com - shorten that long URL into a tiny URL
Dim TempUnion() As Variant
Dim i As Long, Itm As Variant, Ctr As Long
For i = LBound(Arg) To UBound(Arg)
Arg(i) = Arg(i)
If IsArray(Arg(i)) Then
For Each Itm In Arg(i)
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Itm
Next Itm
Else
Ctr = Ctr + 1
ReDim Preserve TempUnion(1 To Ctr) As Variant
TempUnion(Ctr) = Arg(i)
End If
Next i
ArrayUnion = TempUnion
End Function

Once done, set up the following definitions using Formulas | Name Manager...

Define Pdata as referring to:

=arrayunion(
  Sheet1!$A$2:$A$9,
  Sheet1!$B$2:$B$9,
  Sheet1!$C$2:$C$9,
  Sheet1!$D$2:$D$9,
  Sheet1!$E$2:$E$9,
  Sheet1!$F$2:$F$9)

Define Ivec as referring to:

=ROW(INDIRECT("1:"&COLUMNS(Pdata)))

Adjust the foregoing to suit for the sheet name.

Sheet2

A1, control+shift+enter (CSE), not just enter:

=SUM(IF(FREQUENCY(IF(Pdata<>"",
  MATCH("~"&Pdata,Pdata&"",0))),Ivec),1))

A2, Distinct Plist

A3, cse and copy down:

=IF(ROWS($A$3:A3)<=$A$1,
  INDEX(Pdata,SMALL(IF(FREQUENCY(IF(Pdata<>"",
  MATCH("~"&Pdata,Pdata&"",0))),Ivec),Ivec),
  ROWS($A$3:A3))),"")

B3, just enter and copy down:

=IF(A3="","",COUNTIF(Sheet1!$A$2:$F$9,$A3))

 

[Jzfbkr]

Thats worked a treat thank you ever so much!

Happy Belated Yorkshire Day from a fellow Yorkshire man!

 

[XOR LX]

Thats worked a treat thank you ever so much!

Happy Belated Yorkshire Day from a fellow Yorkshire man!

Ah, good man! Nice to know! And to you, too!

Is there a way to get the frequency of each result?

Sure (sorry, forgot that bit!). Assuming you put your first array formula in I1, then in J1:

=IF(I1="","",COUNTIF(Range1,I1))

and copy down as required.

Cheers!