Group By Character Name and list all Scene numbers

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!
 
Oh no! That's a bummer. Sandy is my boy. What did he do?

Anyway how's this?

TCOs working ELC Zones 2021 JJ.xlsx
ABC
1CHARACTER NAMESCENE NUMBERCOUNT
2MICHAELE01 SC 01, E01 SC 02, E01 SC 03, E01 SC 05, E02 SC 02, E02 SC 04, E02 SC 06, E02 SC 108
3JACKE01 SC 01, E01 SC 03, E01 SC 05, E02 SC 05, E02 SC 095
4ELIZABETHE01 SC 01, E01 SC 04, E02 SC 013
5JOHNE01 SC 02, E02 SC 01, E02 SC 04, E02 SC 094
6SAMANTHAE01 SC 03, E01 SC 04, E02 SC 02, E02 SC 084
7LOGANE01 SC 03, E01 SC 04, E02 SC 03, E02 SC 074
8EVANE02 SC 011
9ANGELE02 SC 03, E02 SC 072
Table4


Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"CHARACTER NAME"}, {{"SCENE NUMBER", each _, type table [SCENE NUMBER=text, CHARACTER NAME=text]}, {"COUNT", each Table.RowCount(_), Int64.Type}}),
    Combine = Table.TransformColumns(Group,{{"SCENE NUMBER", each Text.Combine(_[SCENE NUMBER],", ")}})
in
    Combine
 
Last edited:
Upvote 0

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Oh no! That's a bummer. Sandy is my boy. What did he do?

Anyway how's this?

TCOs working ELC Zones 2021 JJ.xlsx
ABC
1CHARACTER NAMESCENE NUMBERCOUNT
2MICHAELE01 SC 01, E01 SC 02, E01 SC 03, E01 SC 05, E02 SC 02, E02 SC 04, E02 SC 06, E02 SC 108
3JACKE01 SC 01, E01 SC 03, E01 SC 05, E02 SC 05, E02 SC 095
4ELIZABETHE01 SC 01, E01 SC 04, E02 SC 013
5JOHNE01 SC 02, E02 SC 01, E02 SC 04, E02 SC 094
6SAMANTHAE01 SC 03, E01 SC 04, E02 SC 02, E02 SC 084
7LOGANE01 SC 03, E01 SC 04, E02 SC 03, E02 SC 074
8EVANE02 SC 011
9ANGELE02 SC 03, E02 SC 072
Table4


Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"CHARACTER NAME"}, {{"SCENE NUMBER", each _, type table [SCENE NUMBER=text, CHARACTER NAME=text]}, {"COUNT", each Table.RowCount(_), Int64.Type}}),
    Combine = Table.TransformColumns(Group,{{"SCENE NUMBER", each Text.Combine(_[SCENE NUMBER],", ")}})
in
    Combine

Hey, Thanks for the code, It's working but for few values, it's showing an error as below. Plz, check once.

1612508786425.png
 
Upvote 0
Oh no! That's a bummer. Sandy is my boy. What did he do?

Anyway how's this?

TCOs working ELC Zones 2021 JJ.xlsx
ABC
1CHARACTER NAMESCENE NUMBERCOUNT
2MICHAELE01 SC 01, E01 SC 02, E01 SC 03, E01 SC 05, E02 SC 02, E02 SC 04, E02 SC 06, E02 SC 108
3JACKE01 SC 01, E01 SC 03, E01 SC 05, E02 SC 05, E02 SC 095
4ELIZABETHE01 SC 01, E01 SC 04, E02 SC 013
5JOHNE01 SC 02, E02 SC 01, E02 SC 04, E02 SC 094
6SAMANTHAE01 SC 03, E01 SC 04, E02 SC 02, E02 SC 084
7LOGANE01 SC 03, E01 SC 04, E02 SC 03, E02 SC 074
8EVANE02 SC 011
9ANGELE02 SC 03, E02 SC 072
Table4


Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"CHARACTER NAME"}, {{"SCENE NUMBER", each _, type table [SCENE NUMBER=text, CHARACTER NAME=text]}, {"COUNT", each Table.RowCount(_), Int64.Type}}),
    Combine = Table.TransformColumns(Group,{{"SCENE NUMBER", each Text.Combine(_[SCENE NUMBER],", ")}})
in
    Combine
Hey, Can we get the scene numbers also unique values as we did for names?
 
Upvote 0
SEQ Pattern.xlsm
ABCDEFG
1SCENE NUMBERCHARACTER NAMECHARACTER NAMEScenesCount
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 108
3E01 SC 01JACKJACKE01 SC 01, E01 SC 03, E01 SC 05, E02 SC 05, E02 SC 095
4E01 SC 01ELIZABETHELIZABETHE01 SC 01, E01 SC 04, E02 SC 013
5E01 SC 02JOHNJOHNE01 SC 02, E02 SC 01, E02 SC 04, E02 SC 094
6E01 SC 02MICHAELSAMANTHAE01 SC 03, E01 SC 04, E02 SC 02, E02 SC 084
7E01 SC 03JACKLOGANE01 SC 03, E01 SC 04, E02 SC 03, E02 SC 074
8E01 SC 03SAMANTHAEVANE02 SC 011
9E01 SC 03MICHAELANGELE02 SC 03, E02 SC 072
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
33E02 SC 03ANGEL
Sheet6


Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"CHARACTER NAME"}, {{"Scenes", each Table.Distinct(_), type table [SCENE NUMBER=text, CHARACTER NAME=text]}, {"Count", each Table.RowCount(Table.Distinct(_)), Int64.Type}}),
    Combine = Table.TransformColumns(Group,{{"Scenes", each Text.Combine(_[SCENE NUMBER],", ")}})
in
    Combine
 
Upvote 0
Solution
SEQ Pattern.xlsm
ABCDEFG
1SCENE NUMBERCHARACTER NAMECHARACTER NAMEScenesCount
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 108
3E01 SC 01JACKJACKE01 SC 01, E01 SC 03, E01 SC 05, E02 SC 05, E02 SC 095
4E01 SC 01ELIZABETHELIZABETHE01 SC 01, E01 SC 04, E02 SC 013
5E01 SC 02JOHNJOHNE01 SC 02, E02 SC 01, E02 SC 04, E02 SC 094
6E01 SC 02MICHAELSAMANTHAE01 SC 03, E01 SC 04, E02 SC 02, E02 SC 084
7E01 SC 03JACKLOGANE01 SC 03, E01 SC 04, E02 SC 03, E02 SC 074
8E01 SC 03SAMANTHAEVANE02 SC 011
9E01 SC 03MICHAELANGELE02 SC 03, E02 SC 072
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
33E02 SC 03ANGEL
Sheet6


Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"CHARACTER NAME"}, {{"Scenes", each Table.Distinct(_), type table [SCENE NUMBER=text, CHARACTER NAME=text]}, {"Count", each Table.RowCount(Table.Distinct(_)), Int64.Type}}),
    Combine = Table.TransformColumns(Group,{{"Scenes", each Text.Combine(_[SCENE NUMBER],", ")}})
in
    Combine
Thank you so much @irobbo314. It was working great as needed. Thanks again for sharing the code. Thank you @Fluff.
 
Upvote 0
───────────────────░█▓▓▓█░▇▆▅▄▃▂
──────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
─────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
──────────░░░───░█▓▓▓▓▓▓█░▇▆▅▄▃▂
─────────░███░──░█▓▓▓▓▓█░▇▆▅▄▃▂
───────░██░░░██░█▓▓▓▓▓█░▇▆▅▄▃▂
──────░█░░█░░░░██▓▓▓▓▓█░▇▆▅▄▃▂
────░██░░█░░░░░░█▓▓▓▓█░▇▆▅▄▃▂
───░█░░░█░░░░░░░██▓▓▓█░▇▆▅▄▃▂
──░█░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░█░░░█░░░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░█░░░░██░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░░█░░░░░██░░░█▓▓▓█░▇▆▅▄▃▂
─░█░█░░░█░░░░░░███▓▓▓▓█░▇▆▅▄▃▂
░█░░░█░░░██░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░█░░░░█████▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░░█░░░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░█░░░░██░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
─░█░█░░░░░████▓▓▓▓██░▇▆▅▄▃▂
─░█░░█░░░░░░░█▓▓██▓█░▇▆▅▄▃▂
──░█░░██░░░██▓▓█▓▓▓█░▇▆▅▄▃▂
───░██░░███▓▓██▓█▓▓█░▇▆▅▄▃▂
────░██▓▓▓███▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓▓▓▓▓▓█░▇▆▅▄▃▂
 
Upvote 0
───────────────────░█▓▓▓█░▇▆▅▄▃▂
──────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
─────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
──────────░░░───░█▓▓▓▓▓▓█░▇▆▅▄▃▂
─────────░███░──░█▓▓▓▓▓█░▇▆▅▄▃▂
───────░██░░░██░█▓▓▓▓▓█░▇▆▅▄▃▂
──────░█░░█░░░░██▓▓▓▓▓█░▇▆▅▄▃▂
────░██░░█░░░░░░█▓▓▓▓█░▇▆▅▄▃▂
───░█░░░█░░░░░░░██▓▓▓█░▇▆▅▄▃▂
──░█░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░█░░░█░░░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░█░░░░██░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░░█░░░░░██░░░█▓▓▓█░▇▆▅▄▃▂
─░█░█░░░█░░░░░░███▓▓▓▓█░▇▆▅▄▃▂
░█░░░█░░░██░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░█░░░░█████▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░░█░░░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░█░░░░██░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
─░█░█░░░░░████▓▓▓▓██░▇▆▅▄▃▂
─░█░░█░░░░░░░█▓▓██▓█░▇▆▅▄▃▂
──░█░░██░░░██▓▓█▓▓▓█░▇▆▅▄▃▂
───░██░░███▓▓██▓█▓▓█░▇▆▅▄▃▂
────░██▓▓▓███▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓▓▓▓▓▓█░▇▆▅▄▃▂
Can I mark it as a solution? if anything needed in the future can I ask or the thread will be closed if I mark it as a solution?
 
Upvote 0
I don't know how all that works. I don't think the thread will be closed.
 
Upvote 0
The thread is not closed if you "Mark as solution" it just shows that you got something that did what you needed.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

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