Group By Character Name and list all Scene numbers

S

signup

New Member
Joined
Feb 15, 2018
Messages
42
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
lrobbo314 said:
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
Click to expand...
Hey @lrobbo314
Can we get one more column, combining multiple values by grouping Character Name and using "," as a delimiter.?(Like we did for Scene Number Column)

Thanks in advance!

Book1
ABCDEFGHI
1SCENE NUMBERCHARACTER NAMECOLUMN 2
2E01 SC 01MICHAELText
3E01 SC 01JACKTextCHARACTER NAMESCENESTEXT
4E01 SC 01ELIZABETHTextMICHAEL E01 SC 01, E01 SC 02, E01 SC 03, E01 SC 05, E02 SC 02, E02 SC 04, E02 SC 06, E02 SC 10Text, Text, Text
5E01 SC 02JOHNTextJACK E01 SC 01, E01 SC 03, E01 SC 05, E02 SC 05, E02 SC 09Text, Text, Text
6E01 SC 02MICHAELTextELIZABETH E01 SC 01, E01 SC 04, E02 SC 01Text, Text, Text
7E01 SC 03JACKTextJOHN E01 SC 02, E02 SC 01, E02 SC 04, E02 SC 09Text, Text, Text
8E01 SC 03SAMANTHATextSAMANTHA E01 SC 03, E01 SC 04, E02 SC 02, E02 SC 08Text, Text, Text
9E01 SC 03MICHAELTextLOGAN E01 SC 03, E01 SC 04, E02 SC 03, E02 SC 07Text, Text, Text
10E01 SC 03LOGANTextEVAN E02 SC 01Text, Text, Text
11E01 SC 04ELIZABETHTextANGEL E02 SC 03, E02 SC 07Text, Text, Text
12E01 SC 04SAMANTHAText
13E01 SC 04LOGANText
14E01 SC 05MICHAELText
15E01 SC 05JACKText
16E02 SC 01EVANText
17E02 SC 01ELIZABETHText
18E02 SC 01JOHNText
19E02 SC 02MICHAELText
20E02 SC 02SAMANTHAText
21E02 SC 03ANGELText
22E02 SC 03LOGANText
23E02 SC 04JOHNText
24E02 SC 04MICHAELText
25E02 SC 05JACKText
26E02 SC 06MICHAELText
27E02 SC 07ANGELText
28E02 SC 07LOGANText
29E02 SC 08SAMANTHAText
30E02 SC 09JACKText
31E02 SC 09JOHNText
32E02 SC 10MICHAELText
Sheet1
 
Upvote 0

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
In your example it says 'Text,Text,Text'. What do you actually want in that column?
 
Upvote 0
Do you mean like this?

Book2
ABCDEFGH
1SCENE NUMBERCHARACTER NAMETestCHARACTER NAMEScenesTestCount
2E01 SC 01MICHAELAMICHAELE01 SC 01, E01 SC 02, E01 SC 03, E01 SC 05, E02 SC 02, E02 SC 04, E02 SC 06, E02 SC 10A,C,B8
3E01 SC 01JACKAJACKE01 SC 01, E01 SC 03, E01 SC 05, E02 SC 05, E02 SC 09A,C,B5
4E01 SC 01ELIZABETHCELIZABETHE01 SC 01, E01 SC 04, E02 SC 01C,A3
5E01 SC 02JOHNCJOHNE01 SC 02, E02 SC 01, E02 SC 04, E02 SC 09C,B,A4
6E01 SC 02MICHAELCSAMANTHAE01 SC 03, E01 SC 04, E02 SC 02, E02 SC 08C,B4
7E01 SC 03JACKCLOGANE01 SC 03, E01 SC 04, E02 SC 03, E02 SC 07B,A4
8E01 SC 03SAMANTHACEVANE02 SC 01C1
9E01 SC 03MICHAELCANGELE02 SC 03, E02 SC 07, E02 SC 03B,C,A3
10E01 SC 03LOGANB
11E01 SC 04ELIZABETHA
12E01 SC 04SAMANTHAB
13E01 SC 04LOGANB
14E01 SC 05MICHAELC
15E01 SC 05JACKB
16E02 SC 01EVANC
17E02 SC 01ELIZABETHA
18E02 SC 01JOHNB
19E02 SC 02MICHAELB
20E02 SC 02SAMANTHAB
21E02 SC 03ANGELB
22E02 SC 03LOGANA
23E02 SC 04JOHNA
24E02 SC 04MICHAELC
25E02 SC 05JACKC
26E02 SC 06MICHAELC
27E02 SC 07ANGELC
28E02 SC 07LOGANA
29E02 SC 08SAMANTHAC
30E02 SC 09JACKB
31E02 SC 09JOHNA
32E02 SC 10MICHAELA
33E02 SC 03ANGELA
Sheet1
 
Upvote 0
alansidman said:
crossposted: Power Query - Add another column to Table.TransformColumns
Click to expand...
Yeah, some random text like below.

SCENE NUMBERCHARACTER NAMECOLUMN 2
E01 SC 01MICHAELTEXT1, TEXT2
E01 SC 01JACKTEXT3
E01 SC 01ELIZABETHTEXT4, TWXT 5
E01 SC 02JOHNTEXT6, TEXT 7
E01 SC 02MICHAELTEXT8,
E01 SC 03JACKTEXT9,
E01 SC 03SAMANTHATEXT10, TEXT11
E01 SC 03MICHAELTEXT12, TEXT13
E01 SC 03LOGANTEXT15
E01 SC 04ELIZABETHTEXT16, TEXT17
E01 SC 04SAMANTHATEXT18, TEXT19, TEXT20
 
Upvote 0
Please post some example data and expected results. It'll make this cluster f go along much more quickly.
 
Upvote 0
lrobbo314 said:
Please post some example data and expected results. It'll make this cluster f go along much more quickly.​
Click to expand...
Just as an example..
​
Scene Number​
LOCATION​
Vehicles​
EP1 SC1​
VILLAGE ROADS​
Logan Ambulance​
EP1 SC2​
NATIONAL HIGHWAY​
Logan ambulance​
EP1 SC2​
NATIONAL HIGHWAY​
black SUV​
EP1 SC3​
FLIGHT​
flight​
EP1 SC4​
HIGHWAY ROAD​
Jack bike​
EP1 SC8​
BEACH​
Tatasumo for terrorist​
EP1 SC8​
BEACH​
Vani car​
EP1 SC8​
BEACH​
Old Car​
EP1 SC12A​
JACK'S HOUSE​
swift car​
EP1 SC13​
MICHAEL'S HOUSE COLONY ROAD​
Logan car​
EP1 SC13​
MICHAEL'S HOUSE COLONY ROAD​
Michael bike​
EP1 SC13​
MICHAEL'S HOUSE COLONY ROAD​
Accident vehicle scorpio​
EP1 SC13​
MICHAEL'S HOUSE COLONY ROAD​
Police vehicle​
EP1 SC13​
MICHAEL'S HOUSE COLONY ROAD​
extra car​
EP1 SC13​
MICHAEL'S HOUSE COLONY ROAD​
Bike 2​
EP1 SC14​
MAIN ROAD​
Michael's bike​
EP1 SC14​
MAIN ROAD​
two buses​
EP1 SC14​
MAIN ROAD​
cars 20​
EP1 SC14​
MAIN ROAD​
bikes and scooters 40​
EP1 SC14​
MAIN ROAD​
Autos 5​
EP1 SC14​
MAIN ROAD​
Tempo vans 2​
EP1 SC14​
MAIN ROAD​
John bike​
EP1 SC14​
MAIN ROAD​
Bharath bike​
EP1 SC14​
MAIN ROAD​
Foods cart​
EP1 SC14​
MAIN ROAD​
Jack bike​
EP1 SC14​
MAIN ROAD​
6689 car​
EP1 SC14​
MAIN ROAD​
Police vehicles 2​
EP1 SC14​
MAIN ROAD​
ambulance​
EP1 SC15​
U TURN POINT​
ambulance​
EP1 SC17​
JOHN'S CAR : ROAD​
John car​
EP1 SC17​
JOHN'S CAR : ROAD​
car​
EP1 SC17​
JOHN'S CAR : ROAD​
bikes​
EP1 SC18​
JOHN'S OFFICE​
John car​
EP1 SC29A​
ROAD UNKNOWN​
John bike​

Required output as Location-wise Vehicles. Also, If you can help with scene-wise vehicles in that location would be a great help. In shooting so couldn't respond back to you early.
Thanks!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Help Please
2 3
Replies
21
Views
532
Asbestos_Jen
Asbestos_Jen
S
  • Question Question
List all staff names that worked under a manager on a certain date
Replies
5
Views
403
Fluff
Fluff
A
  • Solved
Returning name on multiple date arrays
Replies
9
Views
623
archibell
A
O
  • Solved
Merge data in listbox based on another listbox after search for name and dates
Replies
6
Views
187
Omran Y
O
W
  • Solved
Validate month based on contract dates & Calculate number of weeks in that month to calculate hours booked per month
2
Replies
16
Views
2K
phiero21
P

Forum statistics

Threads
1,224,820
Messages
6,181,155
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