Extract name

E

ehsas63

New Member
Joined
Jan 5, 2008
Messages
29
Hi

I need help on how to extract unique name from the excel cell and then sum up the total score for each name.

I have excel sheet where the name for all participates has entered in one cell as example below


Name Score
ABC;#DEF;#HIJ;# 5
ABC;#XYZ;#HIJ;# 2
MNO;#DEF;#HIJ;# 3
ABC;#DEF;#QRS;# 5

I need help on formula to first extra the unique name from each cell in seperate column such as below

ABC
DEF
HIJ
XYZ

Then I need another formula to sum up the total score for each individua as example below

Name Score
ABC 12
DEF 13
HIJ 10

Thanks
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
Do you have something like this in a single cell?

[TABLE="class: grid, width: 250"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: right"]
ABC;#DEF;#HIJ;# 5
ABC;#XYZ;#HIJ;# 2
MNO;#DEF;#HIJ;# 3
ABC;#DEF;#QRS;# 5​
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]



And the result is like this:

[TABLE="class: grid, width: 250"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: right"]
ABC​
[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]DEF[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]HIJ[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]ETC.[/TD]
[TD="align: right"]nn[/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]





It could be with a macro. If you want, I can prepare a macro.
 
Upvote 0
Thanks for the update.

The participants names are as follow:


Column A
Row 1: ABC;#DEF;#HIJ;#
Row 2: ABC;#XYZ;#HIJ;#
Row 3: MNO;#DEF;#HIJ;#
Row 4: ABC;#DEF;#QRS;#

The scores are in Column B for each row as follow

Score
5
2
3
5

I prefer to get the result via formula. Thanks
 
Upvote 0
Try the following.

With a single formula it is not possible.

To give you an idea. To find the first name of A1:

=MID(A1,1,FIND(";",A1,1)-1)

To find the second name:

=IF(IFERROR(MATCH(MID(A1,FIND(";",A1,1)+2,FIND(";",A1,FIND(";",A1,1)+1)-FIND(";",A1,1)-2),D1,0),"")="",MID(A1,FIND(";",A1,1)+2,FIND(";",A1,FIND(";",A1,1)+1)-FIND(";",A1,1)-2),"")

And a similar formula to find the third name.

Next, create 3 formulas for cell A2 and so on

You have to extract the names and then get the only ones.
You would have to put the names in another column, change the semicolon for nothing, separate the text into columns, then join the columns into one and then get the only ones.

You have to put the unique values in column D, then put the formula in cell E1 and copy it to the last value in column D.



[TABLE="class: grid, width: 434"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ABC;#DEF;#HIJ;#[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD]ABC[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ABC;#XYZ;#HIJ;#[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]DEF[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]MNO;#DEF;#HIJ;#[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]HIJ[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]ABC;#DEF;#QRS;#[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD]XYZ[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]MNO[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]QRS[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]


<tbody>
[TH="align: center"]Cell[/TH]
[TH="align: left"]Formula[/TH]

[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] , align: center"]D1[/TH]
[TD="align: left"]=SUMIF($A$1:$A$4,"*"&D1&"*",$B$1:$B$4)[/TD]

</tbody>

Change the 4 by the last row of the participants
 
Upvote 0
If you are willing to change your mind about using VB code, here is a macro that you can use (output goes to Columns D and E)...
Code: 
[table="width: 500"]
[tr]
	[td]Sub GetUniqueNamesAndTheirScoreSummations()
  Dim X As Long, Cell As Range, sNames() As String
  With CreateObject("Scripting.Dictionary")
    For Each Cell In Range("A1", Cells(Rows.Count, "A").End(xlUp))
      sNames = Split(Cell.Value, ";#")
      For X = 0 To UBound(sNames) - 1
        .Item(sNames(X)) = .Item(sNames(X)) + Cell.Offset(, 1).Value
      Next
    Next
    Range("D:E").Clear
    Range("D1").Resize(.Count) = Application.Transpose(.Keys)
    Range("E1").Resize(.Count) = Application.Transpose(.Items)
  End With
End Sub[/td]
[/tr]
[/table]
 
Upvote 0
Thanks for the help. What will be the formula for getting third name.

I would to clarify that in each row there are more than 20 names which are separated by ";#" . Is there any possibility to extract the unique names based on ";#".

Thanks


DanteAmor said:
Try the following.

With a single formula it is not possible.

To give you an idea. To find the first name of A1:

=MID(A1,1,FIND(";",A1,1)-1)

To find the second name:

=IF(IFERROR(MATCH(MID(A1,FIND(";",A1,1)+2,FIND(";",A1,FIND(";",A1,1)+1)-FIND(";",A1,1)-2),D1,0),"")="",MID(A1,FIND(";",A1,1)+2,FIND(";",A1,FIND(";",A1,1)+1)-FIND(";",A1,1)-2),"")

And a similar formula to find the third name.

Next, create 3 formulas for cell A2 and so on

You have to extract the names and then get the only ones.
You would have to put the names in another column, change the semicolon for nothing, separate the text into columns, then join the columns into one and then get the only ones.

You have to put the unique values in column D, then put the formula in cell E1 and copy it to the last value in column D.



[TABLE="class: grid, width: 434"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ABC;#DEF;#HIJ;#[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD]ABC[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ABC;#XYZ;#HIJ;#[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]DEF[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]MNO;#DEF;#HIJ;#[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]HIJ[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]ABC;#DEF;#QRS;#[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD]XYZ[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]MNO[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]QRS[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]


<tbody>
[TH="align: center"]Cell[/TH]
[TH="align: left"]Formula[/TH]

[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] , align: center"]D1[/TH]
[TD="align: left"]=SUMIF($A$1:$A$4,"*"&D1&"*",$B$1:$B$4)[/TD]

</tbody>

Change the 4 by the last row of the participants
Click to expand...
 
Upvote 0
Thanks for the help. Unfortunately I can't use VB code. Appreciate if I can get formula.Thanks

Rick Rothstein said:
If you are willing to change your mind about using VB code, here is a macro that you can use (output goes to Columns D and E)...
Code: 
[table="width: 500"]
[tr]
	[td]Sub GetUniqueNamesAndTheirScoreSummations()
  Dim X As Long, Cell As Range, sNames() As String
  With CreateObject("Scripting.Dictionary")
    For Each Cell In Range("A1", Cells(Rows.Count, "A").End(xlUp))
      sNames = Split(Cell.Value, ";#")
      For X = 0 To UBound(sNames) - 1
        .Item(sNames(X)) = .Item(sNames(X)) + Cell.Offset(, 1).Value
      Next
    Next
    Range("D:E").Clear
    Range("D1").Resize(.Count) = Application.Transpose(.Keys)
    Range("E1").Resize(.Count) = Application.Transpose(.Items)
  End With
End Sub[/td]
[/tr]
[/table]
Click to expand...
 
Upvote 0
Hi, Please help on formula to extracts the names. Thanks


ehsas63 said:
Thanks for the help. What will be the formula for getting third name.

I would to clarify that in each row there are more than 20 names which are separated by ";#" . Is there any possibility to extract the unique names based on ";#".

Thanks
Click to expand...
 
Upvote 0
These are the formulas. We need 3 auxiliary columns for the formulas


[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 427"]
<tbody>[TR]
[TD="class: xl65, width: 427, align: center"]STRING[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80, align: center"]SCORE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl65, width: 56, align: center"]CELL[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 55"]
<tbody>[TR]
[TD="class: xl65, width: 55, align: center"]ROW[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 45"]
<tbody>[TR]
[TD="class: xl65, width: 45, align: center"]MANY[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD="class: xl65, width: 124, align: center"]NAME[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 47"]
<tbody>[TR]
[TD="class: xl65, width: 47, align: center"]SCORE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 427"]
<tbody>[TR]
[TD="class: xl65, width: 427"]name last;#Dante Amor;#name3 last3;#name4 last4;#[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]5[/TD]
[TD]A2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD="class: xl65, width: 124"]name last[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 427"]
<tbody>[TR]
[TD="class: xl65, width: 427"]name5 last5;#Dante Amor;#name2 last2;#[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[TD]A2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD="class: xl65, width: 124"]Dante Amor[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][TABLE="width: 427"]
<tbody>[TR]
[TD="class: xl65, width: 427"]name6 last6;#Dante Amor;#name last;#[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]3[/TD]
[TD]A2[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD="class: xl65, width: 124"]name3 last3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][TABLE="width: 427"]
<tbody>[TR]
[TD="class: xl65, width: 427"]name7 last7;#Dante Amor;#name last;#name2 last2;#name5 last5;#[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[TD]A2[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD="class: xl65, width: 124"]name4 last4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]A3[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD="class: xl65, width: 124"]name5 last5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD]A3[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD="class: xl65, width: 124"]Dante Amor[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD]A3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD="class: xl65, width: 124"]name2 last2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD]A4[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD="class: xl65, width: 124"]name6 last6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD]A4[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD="class: xl65, width: 124"]Dante Amor[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD]A4[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD="class: xl65, width: 124"]name last[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD]A5[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD="class: xl65, width: 124"]name7 last7[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD]A5[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD="class: xl65, width: 124"]Dante Amor[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD]A5[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD="class: xl65, width: 124"]name last[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD]A5[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD="class: xl65, width: 124"]name2 last2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD]A5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD="class: xl65, width: 124"]name5 last5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[TD]A6[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD="class: xl65, width: 124, align: center"]#¡VALOR![/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]


Initial Value

[TABLE="class: grid, width: 25"]
<tbody>[TR]
[TD]CELL[/TD]
[TD]VALUE[/TD]
[TD]Comments[/TD]
[/TR]
[TR]
[TD]C2[/TD]
[TD]A2[/TD]
[TD]First cell with the first string[/TD]
[/TR]
[TR]
[TD]D2[/TD]
[TD]2[/TD]
[TD]Initial row[/TD]
[/TR]
[TR]
[TD]E2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


Formulas

[TABLE="class: grid, width: 25"]
<tbody>[TR]
[TD]CELL[/TD]
[TD]FORMULA[/TD]
[/TR]
[TR]
[TD]C3[/TD]
[TD]=IF(IFERROR(FIND("@",SUBSTITUTE(INDIRECT(C2),"#","@",E2+1)),1)=1,"A"&D2+1,C2)[/TD]
[/TR]
[TR]
[TD]D3[/TD]
[TD]=IF(C2=C3,D2,D2+1)[/TD]
[/TR]
[TR]
[TD]E3[/TD]
[TD]=IF(C2=C3,E2+1,1)[/TD]
[/TR]
</tbody>[/TABLE]

Then copy the 3 formulas until the cell appears, in my example, cell A6, in your case until the last cell with data.

Formulas
[TABLE="class: grid, width: 25"]
<tbody>[TR]
[TD]CELL[/TD]
[TD]FORMULA[/TD]
[/TR]
[TR]
[TD]F2[/TD]
[TD]=IF(E2=1,MID(INDIRECT(C2),1,FIND(";",INDIRECT(C2),1)-1),MID(INDIRECT(C2),FIND("@",SUBSTITUTE(INDIRECT(C2),";#","@",E2-1))+2,FIND("@",SUBSTITUTE(INDIRECT(C2),";#","@",E2))-FIND("@",SUBSTITUTE(INDIRECT(C2),";#","@",E2-1))-2))[/TD]
[/TR]
[TR]
[TD]G2[/TD]
[TD]=INDIRECT("B"&D2)[/TD]
[/TR]
</tbody>[/TABLE]

Then copy the 2 formulas to the last row with formulas of the column C. If you copy the formula plus rows the result will be #!VALOR!

Finally select the cells with the result, from F1 to G16 and create a dynamic table to see the scores of each name.

See image
https://www.dropbox.com/s/ljeafpg7iz7k9bv/first td.jpg?dl=0


Regards Dante Amor
 
Upvote 0
Thank you Dante Amor. I appreciate your help. I got the results except dynamic table.

Could you please also help me with dynamic table formula.

Thanks

DanteAmor said:
These are the formulas. We need 3 auxiliary columns for the formulas


[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 427"]
<tbody>[TR]
[TD="class: xl65, width: 427, align: center"]STRING[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80, align: center"]SCORE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 56"]
<tbody>[TR]
[TD="class: xl65, width: 56, align: center"]CELL[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 55"]
<tbody>[TR]
[TD="class: xl65, width: 55, align: center"]ROW[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 45"]
<tbody>[TR]
[TD="class: xl65, width: 45, align: center"]MANY[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD="class: xl65, width: 124, align: center"]NAME[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 47"]
<tbody>[TR]
[TD="class: xl65, width: 47, align: center"]SCORE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 427"]
<tbody>[TR]
[TD="class: xl65, width: 427"]name last;#Dante Amor;#name3 last3;#name4 last4;#[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]5[/TD]
[TD]A2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD="class: xl65, width: 124"]name last[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 427"]
<tbody>[TR]
[TD="class: xl65, width: 427"]name5 last5;#Dante Amor;#name2 last2;#[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[TD]A2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD="class: xl65, width: 124"]Dante Amor[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][TABLE="width: 427"]
<tbody>[TR]
[TD="class: xl65, width: 427"]name6 last6;#Dante Amor;#name last;#[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]3[/TD]
[TD]A2[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD="class: xl65, width: 124"]name3 last3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][TABLE="width: 427"]
<tbody>[TR]
[TD="class: xl65, width: 427"]name7 last7;#Dante Amor;#name last;#name2 last2;#name5 last5;#[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[TD]A2[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD="class: xl65, width: 124"]name4 last4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]A3[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD="class: xl65, width: 124"]name5 last5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD]A3[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD="class: xl65, width: 124"]Dante Amor[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD]A3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD="class: xl65, width: 124"]name2 last2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD]A4[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD="class: xl65, width: 124"]name6 last6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD]A4[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD="class: xl65, width: 124"]Dante Amor[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD]A4[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD="class: xl65, width: 124"]name last[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD]A5[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD="class: xl65, width: 124"]name7 last7[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD]A5[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD="class: xl65, width: 124"]Dante Amor[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD]A5[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD="class: xl65, width: 124"]name last[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD]A5[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD="class: xl65, width: 124"]name2 last2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD]A5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD="class: xl65, width: 124"]name5 last5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[TD]A6[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD="class: xl65, width: 124, align: center"]#¡VALOR![/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]


Initial Value

[TABLE="class: grid, width: 25"]
<tbody>[TR]
[TD]CELL[/TD]
[TD]VALUE[/TD]
[TD]Comments[/TD]
[/TR]
[TR]
[TD]C2[/TD]
[TD]A2[/TD]
[TD]First cell with the first string[/TD]
[/TR]
[TR]
[TD]D2[/TD]
[TD]2[/TD]
[TD]Initial row[/TD]
[/TR]
[TR]
[TD]E2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


Formulas

[TABLE="class: grid, width: 25"]
<tbody>[TR]
[TD]CELL[/TD]
[TD]FORMULA[/TD]
[/TR]
[TR]
[TD]C3[/TD]
[TD]=IF(IFERROR(FIND("@",SUBSTITUTE(INDIRECT(C2),"#","@",E2+1)),1)=1,"A"&D2+1,C2)[/TD]
[/TR]
[TR]
[TD]D3[/TD]
[TD]=IF(C2=C3,D2,D2+1)[/TD]
[/TR]
[TR]
[TD]E3[/TD]
[TD]=IF(C2=C3,E2+1,1)[/TD]
[/TR]
</tbody>[/TABLE]

Then copy the 3 formulas until the cell appears, in my example, cell A6, in your case until the last cell with data.

Formulas
[TABLE="class: grid, width: 25"]
<tbody>[TR]
[TD]CELL[/TD]
[TD]FORMULA[/TD]
[/TR]
[TR]
[TD]F2[/TD]
[TD]=IF(E2=1,MID(INDIRECT(C2),1,FIND(";",INDIRECT(C2),1)-1),MID(INDIRECT(C2),FIND("@",SUBSTITUTE(INDIRECT(C2),";#","@",E2-1))+2,FIND("@",SUBSTITUTE(INDIRECT(C2),";#","@",E2))-FIND("@",SUBSTITUTE(INDIRECT(C2),";#","@",E2-1))-2))[/TD]
[/TR]
[TR]
[TD]G2[/TD]
[TD]=INDIRECT("B"&D2)[/TD]
[/TR]
</tbody>[/TABLE]

Then copy the 2 formulas to the last row with formulas of the column C. If you copy the formula plus rows the result will be #!VALOR!

Finally select the cells with the result, from F1 to G16 and create a dynamic table to see the scores of each name.

See image
https://www.dropbox.com/s/ljeafpg7iz7k9bv/first td.jpg?dl=0


Regards Dante Amor
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Formulas using a moving range producing an error message (#NAME?)
Replies
4
Views
118
Alroj
A
S
  • Question Question
A tricky array sum question into a single cell...
Replies
4
Views
313
etaf
etaf
S
  • Solved
Few problems with dynamic arrays
2
Replies
15
Views
325
djclements
djclements
V
  • Question Question
Multiple IF with SumProduct
Replies
8
Views
349
djclements
djclements
K
  • Solved
Formula to highlight cells if one cell contains specific text and another cell has only one of two requirements
Replies
8
Views
947
kmsprague
K

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top