Extract name

[ehsas63]

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

 

[DanteAmor]

Do you have something like this in a single cell?

	A	B	C
1	ABC;#DEF;#HIJ;# 5 ABC;#XYZ;#HIJ;# 2 MNO;#DEF;#HIJ;# 3 ABC;#DEF;#QRS;# 5​
2
3

<tbody>
</tbody>

And the result is like this:

	A	B	C
1	ABC​	12
2	DEF	13
3	HIJ	10
4	ETC.	nn

<tbody>
</tbody>

It could be with a macro. If you want, I can prepare a macro.

 

[ehsas63]

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

 

[DanteAmor]

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.

	A	B	C	D	E
1	ABC;#DEF;#HIJ;#	5		ABC	12
2	ABC;#XYZ;#HIJ;#	2		DEF	13
3	MNO;#DEF;#HIJ;#	3		HIJ	10
4	ABC;#DEF;#QRS;#	5		XYZ	2
5				MNO	3
6				QRS	5

<tbody>
</tbody>

Cell	Formula
D1	=SUMIF($A$1:$A$4,"*"&D1&"*",$B$1:$B$4)

<tbody>
</tbody>

Change the 4 by the last row of the participants

 

[Rick Rothstein]

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

[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]

 

[ehsas63]

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

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.

	A	B	C	D	E
1	ABC;#DEF;#HIJ;#	5		ABC	12
2	ABC;#XYZ;#HIJ;#	2		DEF	13
3	MNO;#DEF;#HIJ;#	3		HIJ	10
4	ABC;#DEF;#QRS;#	5		XYZ	2
5				MNO	3
6				QRS	5

<tbody>
</tbody>

Cell	Formula
D1	=SUMIF($A$1:$A$4,"*"&D1&"*",$B$1:$B$4)

<tbody>
</tbody>

Change the 4 by the last row of the participants

 

[ehsas63]

Thanks for the help. Unfortunately I can't use VB code. Appreciate if I can get formula.Thanks

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

[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]

 

[ehsas63]

Hi, Please help on formula to extracts the names. Thanks

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]

These are the formulas. We need 3 auxiliary columns for the formulas

	A	B	C	D	E	F	G
1	STRING <tbody> </tbody>	SCORE <tbody> </tbody>	CELL <tbody> </tbody>	ROW <tbody> </tbody>	MANY <tbody> </tbody>	NAME <tbody> </tbody>	SCORE <tbody> </tbody>
2	name last;#Dante Amor;#name3 last3;#name4 last4;# <tbody> </tbody>	5	A2	2	1	name last <tbody> </tbody>	5
3	name5 last5;#Dante Amor;#name2 last2;# <tbody> </tbody>	2	A2	2	2	Dante Amor <tbody> </tbody>	5
4	name6 last6;#Dante Amor;#name last;# <tbody> </tbody>	3	A2	2	3	name3 last3 <tbody> </tbody>	5
5	name7 last7;#Dante Amor;#name last;#name2 last2;#name5 last5;# <tbody> </tbody>	1	A2	2	4	name4 last4 <tbody> </tbody>	5
6			A3	3	1	name5 last5 <tbody> </tbody>	2
7			A3	3	2	Dante Amor <tbody> </tbody>	2
8			A3	3	3	name2 last2 <tbody> </tbody>	2
9			A4	4	1	name6 last6 <tbody> </tbody>	3
10			A4	4	2	Dante Amor <tbody> </tbody>	3
11			A4	4	3	name last <tbody> </tbody>	3
12			A5	5	1	name7 last7 <tbody> </tbody>	1
13			A5	5	2	Dante Amor <tbody> </tbody>	1
14			A5	5	3	name last <tbody> </tbody>	1
15			A5	5	4	name2 last2 <tbody> </tbody>	1
16			A5	5	5	name5 last5 <tbody> </tbody>	1
17			A6	6	1	#¡VALOR! <tbody> </tbody>	0

<tbody>
</tbody>

Initial Value

CELL	VALUE	Comments
C2	A2	First cell with the first string
D2	2	Initial row
E2	1	1

<tbody>
</tbody>

Formulas

CELL	FORMULA
C3	=IF(IFERROR(FIND("@",SUBSTITUTE(INDIRECT(C2),"#","@",E2+1)),1)=1,"A"&D2+1,C2)
D3	=IF(C2=C3,D2,D2+1)
E3	=IF(C2=C3,E2+1,1)

<tbody>
</tbody>

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

Formulas

CELL	FORMULA
F2	=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))
G2	=INDIRECT("B"&D2)

<tbody>
</tbody>

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

 

[ehsas63]

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

These are the formulas. We need 3 auxiliary columns for the formulas

	A	B	C	D	E	F	G
1	STRING <tbody> </tbody>	SCORE <tbody> </tbody>	CELL <tbody> </tbody>	ROW <tbody> </tbody>	MANY <tbody> </tbody>	NAME <tbody> </tbody>	SCORE <tbody> </tbody>
2	name last;#Dante Amor;#name3 last3;#name4 last4;# <tbody> </tbody>	5	A2	2	1	name last <tbody> </tbody>	5
3	name5 last5;#Dante Amor;#name2 last2;# <tbody> </tbody>	2	A2	2	2	Dante Amor <tbody> </tbody>	5
4	name6 last6;#Dante Amor;#name last;# <tbody> </tbody>	3	A2	2	3	name3 last3 <tbody> </tbody>	5
5	name7 last7;#Dante Amor;#name last;#name2 last2;#name5 last5;# <tbody> </tbody>	1	A2	2	4	name4 last4 <tbody> </tbody>	5
6			A3	3	1	name5 last5 <tbody> </tbody>	2
7			A3	3	2	Dante Amor <tbody> </tbody>	2
8			A3	3	3	name2 last2 <tbody> </tbody>	2
9			A4	4	1	name6 last6 <tbody> </tbody>	3
10			A4	4	2	Dante Amor <tbody> </tbody>	3
11			A4	4	3	name last <tbody> </tbody>	3
12			A5	5	1	name7 last7 <tbody> </tbody>	1
13			A5	5	2	Dante Amor <tbody> </tbody>	1
14			A5	5	3	name last <tbody> </tbody>	1
15			A5	5	4	name2 last2 <tbody> </tbody>	1
16			A5	5	5	name5 last5 <tbody> </tbody>	1
17			A6	6	1	#¡VALOR! <tbody> </tbody>	0

<tbody>
</tbody>

Initial Value

CELL	VALUE	Comments
C2	A2	First cell with the first string
D2	2	Initial row
E2	1	1

<tbody>
</tbody>

Formulas

CELL	FORMULA
C3	=IF(IFERROR(FIND("@",SUBSTITUTE(INDIRECT(C2),"#","@",E2+1)),1)=1,"A"&D2+1,C2)
D3	=IF(C2=C3,D2,D2+1)
E3	=IF(C2=C3,E2+1,1)

<tbody>
</tbody>

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

Formulas

CELL	FORMULA
F2	=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))
G2	=INDIRECT("B"&D2)

<tbody>
</tbody>

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