Group By Character Name and list all Scene numbers

S

signup

New Member
Joined
Feb 15, 2018
Messages
40
Office Version
  1. 2019
Platform
  1. Windows
Hello All, Hope you are doing Fine.

I have Excel data as shown in the below table. Can anyone help me to get the desired output?

SCENE NUMBER​
CHARACTER NAME​
E01 SC 01​
MICHAEL​
E01 SC 01​
JACK​
E01 SC 01​
ELIZABETH​
E01 SC 02​
JOHN​
E01 SC 02​
MICHAEL​
E01 SC 03​
JACK​
E01 SC 03​
SAMANTHA​
E01 SC 03​
MICHAEL​
E01 SC 03​
LOGAN​
E01 SC 04​
ELIZABETH​
E01 SC 04​
SAMANTHA​
E01 SC 04​
LOGAN​
E01 SC 05​
MICHAEL​
E01 SC 05​
JACK​
E02 SC 01​
EVAN​
E02 SC 01​
ELIZABETH​
E02 SC 01​
JOHN​
E02 SC 02​
MICHAEL​
E02 SC 02​
SAMANTHA​
E02 SC 03​
ANGEL​
E02 SC 03​
LOGAN​
E02 SC 04​
JOHN​
E02 SC 04​
MICHAEL​
E02 SC 05​
JACK​
E02 SC 06​
MICHAEL​
E02 SC 07​
ANGEL​
E02 SC 07​
LOGAN​
E02 SC 08​
SAMANTHA​
E02 SC 09​
JACK​
E02 SC 09​
JOHN​
E02 SC 10​
MICHAEL​

In the above sheet, I have character names from the scene. I need to prepare Character-wise scene numbers for my project. So, by making characters unique I need to create a list of scene numbers.

EXPECTED OUTPUT : (Note: Below output is Just to understand the requirement only but not precisely like this)

I need to get all the scene numbers list for the character.

MICHAEL - E01 SC 01, E01 SC 02, E01 SC 03, E01 SC 05, E02 SC 02, E02 SC 04, E02 SC 06, E02 SC 10
JACK - E01 SC 01, E01 SC 03, E01 SC 05, E02 SC 05, E02 SC 09
ELIZABETH - E01 SC 01, E01 SC 04, E02 SC 01
JOHN - E01 SC 02, E02 SC 01, E02 SC 04, E02 SC 09
SAMANTHA - E01 SC 03, E01 SC 04, E02 SC 02, E02 SC 08
LOGAN - E01 SC 03, E01 SC 04, E02 SC 03, E02 SC 07
EVAN - E02 SC 01
ANGEL - E02 SC 03, E02 SC 07

If there is any way to achieve this, Please help me.

Thanks in advance!
 
Sort by date Sort by votes
Fluff said:
If you would prefer a macro
VBA Code: 
Sub signup()
   Dim Cl As Range
   Dim Dic As Object
  
   Set Dic = CreateObject("scripting.dictionary")
   With Sheets("Master")
      For Each Cl In .Range("B2", .Range("B" & Rows.Count).End(xlUp))
         If Not Dic.Exists(Cl.Value) Then
            Dic.Add Cl.Value, Cl.Offset(, -1).Value
         Else
            Dic(Cl.Value) = Dic(Cl.Value) & ", " & Cl.Offset(, -1).Value
         End If
      Next Cl
      .Range("D2").Resize(Dic.Count, 2).Value = Application.Transpose(Array(Dic.keys, Dic.items))
   End With
End Sub
Change sheet name to suit.
Click to expand...
Thank you so much, Will try with this also..
 
Upvote 0

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
sandy666 said:
I suggest to learn Power Query and not just copy / paste
docs.microsoft.com

Power Query documentation - Power Query

Power Query is used to filter, combine, and mash up data from one or more supported data sources.
docs.microsoft.com
Click to expand...
Sure, Thanks for sharing this. Before that, can u do quick help with this one? As you already said still I'm getting the error with the Table name..

1612365918091.png


I thought it is worksheet name so kept it as "Table1". Plz dont mind and Plz consider me as noobie.

1612365995891.png
 
Upvote 0
sandy666 said:
remember that source table name should be the same as in the code (here is Table1)
Click to expand...
your source range should be an Excel Table (Ctrl+T) then check the name of source table and change in the code to this name
sheet name is not a Table name
tblname.png

or check in Name Manager
 
Last edited:
Upvote 0
T
sandy666 said:
your source range should be an Excel Table (Ctrl+T) then check the name of source table and change in the code to this name
sheet name is not a Table name
View attachment 31138
or check in Name Manager
Click to expand...
Thank you so much, I got the required result now. You saved a lot of time. Thank you so much :)
 
Upvote 0
Fluff said:
Thanks for that.
With a formula
+Fluff 1.xlsm
ABCDE
1SCENE NUMBERCHARACTER NAME
2E01 SC 01MICHAELMICHAELE01 SC 01, E01 SC 02, E01 SC 03, E01 SC 05, E02 SC 02, E02 SC 04, E02 SC 06, E02 SC 10
3E01 SC 01JACKELIZABETHE01 SC 01, E01 SC 04, E02 SC 01
4E01 SC 01ELIZABETHJACKE01 SC 01, E01 SC 03, E01 SC 05, E02 SC 05, E02 SC 09
5E01 SC 02JOHNSAMANTHAE01 SC 03, E01 SC 04, E02 SC 02, E02 SC 08
6E01 SC 02MICHAELJOHNE01 SC 02, E02 SC 01, E02 SC 04, E02 SC 09
7E01 SC 03JACKANGELE02 SC 03, E02 SC 07
8E01 SC 03SAMANTHA  
9E01 SC 03MICHAEL  
10E01 SC 03LOGAN  
11E01 SC 04ELIZABETH  
12E01 SC 04SAMANTHA  
13E01 SC 04LOGAN
14E01 SC 05MICHAEL
15E01 SC 05JACK
16E02 SC 01EVAN
17E02 SC 01ELIZABETH
18E02 SC 01JOHN
19E02 SC 02MICHAEL
20E02 SC 02SAMANTHA
21E02 SC 03ANGEL
22E02 SC 03LOGAN
23E02 SC 04JOHN
24E02 SC 04MICHAEL
25E02 SC 05JACK
26E02 SC 06MICHAEL
27E02 SC 07ANGEL
28E02 SC 07LOGAN
29E02 SC 08SAMANTHA
30E02 SC 09JACK
31E02 SC 09JOHN
32E02 SC 10MICHAEL
33
34
Main
Cell Formulas
RangeFormula
D2:D12D2=IFERROR(INDEX($B$2:$B$4000,AGGREGATE(15,6,(ROW($B$2:$B$4000)-ROW($B$2)+1)/(ISNA(MATCH($B$2:$B$4000,D$1:D1,0)))/($B$2:$B$4000<>""),ROWS(D$2:D2))),"")
E2:E12E2=IF(D2="","",TEXTJOIN(", ",,IF($B$2:$B$8552=D2,$A$2:$A$8552,"")))
Press CTRL+SHIFT+ENTER to enter array formulas.
Click to expand...

@Fluff I tried for a while but have come to the conclusion that there is no way to use 'Let' to turn this into a single cell formula. Just curious what you think.

Either way, here's as close as I got using the dynamic array formulas.

Book1
DE
2ANGELE02 SC 03, E02 SC 07
3ELIZABETHE01 SC 01, E01 SC 04, E02 SC 01
4EVANE02 SC 01
5JACKE01 SC 01, E01 SC 03, E01 SC 05, E02 SC 05, E02 SC 09
6JOHNE01 SC 02, E02 SC 01, E02 SC 04, E02 SC 09
7LOGANE01 SC 03, E01 SC 04, E02 SC 03, E02 SC 07
8MICHAELE01 SC 01, E01 SC 02, E01 SC 03, E01 SC 05, E02 SC 02, E02 SC 04, E02 SC 06, E02 SC 10
9SAMANTHAE01 SC 03, E01 SC 04, E02 SC 02, E02 SC 08
Sheet2
Cell Formulas
RangeFormula
D2:D9D2=UNIQUE(SORT(B2:B32))
E2:E9E2=TEXTJOIN(", ",TRUE,IF($B$2:$B$32=D2,$A$2:$A$32,""))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
Dynamic array formulas.
 
Upvote 0
I very much doubt it's possible & even if it is I don't really see any benefit.
That said I realised that I messed up the formula for col D it should be
+Fluff 1.xlsm
ABCDE
1SCENE NUMBERCHARACTER NAME
2E01 SC 01MICHAELMICHAELE01 SC 01, E01 SC 02, E01 SC 03, E01 SC 05, E02 SC 02, E02 SC 04, E02 SC 06, E02 SC 10
3E01 SC 01JACKJACKE01 SC 01, E01 SC 03, E01 SC 05, E02 SC 05, E02 SC 09
4E01 SC 01ELIZABETHELIZABETHE01 SC 01, E01 SC 04, E02 SC 01
5E01 SC 02JOHNJOHNE01 SC 02, E02 SC 01, E02 SC 04, E02 SC 09
6E01 SC 02MICHAELSAMANTHAE01 SC 03, E01 SC 04, E02 SC 02, E02 SC 08
7E01 SC 03JACKLOGANE01 SC 03, E01 SC 04, E02 SC 03, E02 SC 07
8E01 SC 03SAMANTHAEVANE02 SC 01
9E01 SC 03MICHAELANGELE02 SC 03, E02 SC 07
10E01 SC 03LOGAN  
11E01 SC 04ELIZABETH  
12E01 SC 04SAMANTHA 
13E01 SC 04LOGAN
14E01 SC 05MICHAEL
15E01 SC 05JACK
16E02 SC 01EVAN
17E02 SC 01ELIZABETH
18E02 SC 01JOHN
19E02 SC 02MICHAEL
20E02 SC 02SAMANTHA
21E02 SC 03ANGEL
22E02 SC 03LOGAN
23E02 SC 04JOHN
24E02 SC 04MICHAEL
25E02 SC 05JACK
26E02 SC 06MICHAEL
27E02 SC 07ANGEL
28E02 SC 07LOGAN
29E02 SC 08SAMANTHA
30E02 SC 09JACK
31E02 SC 09JOHN
32E02 SC 10MICHAEL
33
Main
Cell Formulas
RangeFormula
E2:E11E2=IF(D2="","",TEXTJOIN(", ",,IF($B$2:$B$8552=D2,$A$2:$A$8552,"")))
D2:D10D2=IFERROR(INDEX($B$2:$B$4000,AGGREGATE(15,6,(ROW($B$2:$B$4000)-ROW($B$2)+1)/(ISNA(MATCH($B$2:$B$4000,D$1:D1,0)))/($B$2:$B$4000<>""),1)),"")
D11:D12D11=IFERROR(INDEX($B$2:$B$4000,AGGREGATE(15,6,(ROW($B$2:$B$4000)-ROW($B$2)+1)/(ISNA(MATCH($B$2:$B$4000,D$1:D10,0)))/($B$2:$B$4000<>""),ROWS(D$2:D11))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
sandy666 said:
SCENE NUMBERCHARACTER NAMECHARACTER NAMESCENE NUMBER
E01 SC 01MICHAELMICHAELE01 SC 01,E01 SC 02,E01 SC 03,E01 SC 05,E02 SC 02,E02 SC 04,E02 SC 06,E02 SC 10
E01 SC 01JACKJACKE01 SC 01,E01 SC 03,E01 SC 05,E02 SC 05,E02 SC 09
E01 SC 01ELIZABETHELIZABETHE01 SC 01,E01 SC 04,E02 SC 01
E01 SC 02JOHNJOHNE01 SC 02,E02 SC 01,E02 SC 04,E02 SC 09
E01 SC 02MICHAELSAMANTHAE01 SC 03,E01 SC 04,E02 SC 02,E02 SC 08
E01 SC 03JACKLOGANE01 SC 03,E01 SC 04,E02 SC 03,E02 SC 07
E01 SC 03SAMANTHAEVANE02 SC 01
E01 SC 03MICHAELANGELE02 SC 03,E02 SC 07
E01 SC 03LOGAN
E01 SC 04ELIZABETH
E01 SC 04SAMANTHA
E01 SC 04LOGAN
E01 SC 05MICHAEL
E01 SC 05JACK
E02 SC 01EVAN
E02 SC 01ELIZABETH
E02 SC 01JOHN
E02 SC 02MICHAEL
E02 SC 02SAMANTHA
E02 SC 03ANGEL
E02 SC 03LOGAN
E02 SC 04JOHN
E02 SC 04MICHAEL
E02 SC 05JACK
E02 SC 06MICHAEL
E02 SC 07ANGEL
E02 SC 07LOGAN
E02 SC 08SAMANTHA
E02 SC 09JACK
E02 SC 09JOHN
E02 SC 10MICHAEL

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"CHARACTER NAME"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "SCENE NUMBER", each [Count][SCENE NUMBER]),
    Extract = Table.TransformColumns(List, {"SCENE NUMBER", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    Extract
Click to expand...
Hi, is it possible to add one column for a count of the scene numbers for each character?
 
Upvote 0
Sandy666 is no longer a member here, so he cannot help you. I know nothing about PQ so cannot help either, but hopefully @lrobbo314 can.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Returning name on multiple date arrays
Replies
9
Views
375
archibell
A
W
  • Solved
Validate month based on contract dates & Calculate number of weeks in that month to calculate hours booked per month
2
Replies
15
Views
1K
Wishmaster40
W
C
  • Solved
Top 10 Value in a date range with multiple criteria
2
Replies
12
Views
609
Fluff
Fluff
A
  • Solved
sum formula
Replies
4
Views
140
Peter_SSs
Peter_SSs
L
  • Question
Formula Help with Count Number of Vessel Turnaround Trips monthly based on Criteria
Replies
1
Views
228
Lukma
L

Forum statistics

Threads
1,214,649
Messages
6,120,728
Members
448,987
Latest member
marion_davis

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