Sort and shift rows with similar data

[Fabian451]

Dear Fourm

I have a table with 2000+ rows with similar data, but not arranged in the way I wanted.

typical row looks liket this and I am looking for a way to get all ""produced by" and "Film editing by" from the different rows in the same collumn (I attached a sample):

Cast Raymond Bagatsing Manuel Quezon Rachel Alejandro Aurora Quezon Kate Alejandrino Baby Quezon Billy Ray Gallion Alex Frieder David Bianco Dwight Eisenhower Jennifer Blair-Bianco Mamie Eisenhower Tony Ahn Herbert Frieder James Paoleli Paul McNutt Rest of cast listed alphabetically: Bernard Carritero Narciso Ramos Jeremy Domingo Ambassador Feng Shen Hamilton McLeod Ross Barnaby McLeod Mister Miller Natalia Moon Vera Directed by Matthew E. Rosen (as Matthew Rosen) Written by Dren Pavia Janice Y. Perez Dean Rosen Produced by Carlo L. Katigbak producer (as Carlo Katigbak) Olivia M. Lamasan producer Lorena H. Rosen producer Linggit Tan producer Music by Dean Rosen Cinematography by Matthew E. Rosen Film Editing by Reuben Joseph Aquino Production Design by Maria Rowella Talusig (as Rowella Talusig) Sound Department Anglea Pereyra sound designer Visual Effects by Toni Gozum colorist Camera and Electrical Department Jo Beth Javier camera operator Unbenannte Tabelle.xlsx

[Glenn Kennedy]

Hi. Before we get going on this one...

Is your forum profile showing the Excel PRODUCT that you need this request to work with? I have no idea what 16.27 means.

The best solutions often rely on knowing WHICH Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date. If you aren't sure, in Excel go to File/Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

The three most recent Excel PRODUCTS are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the Version number in your profile (e.g. MS365 (PC) Version 2211). The version number is in the About Excel section further down the Account page.

Cheers,

Glenn.

[AskMeAboutExcel]

Your example and description isn't clear. Can you create a new attachment that clearly shows

1) What your starting dataset looks like
2) A sample of how the output would look

Depending on your use case, might want to look at pulling the data from https://www.imdb.com/interfaces/ (note licensing limitations) and using PowerQuery to link the datasets.

[Fabian451]

Thanks. My Excel is 2019 (says: Version 16.43 currenctly). But I mainly work with google sheets. The starting point ist the session I attached and the end point is a sorted list, where all entries of the value "produced by" are in the same collumn.

The data is always in the same order. So, first cast, then director, then producer and so on, only some sets are missing entries or have different numbers of entries per category, so I need a way shift the rows after a certain point to another part of the table.

[AskMeAboutExcel]

A mocked up example would be helpful, as otherwise still second-guessing what you are looking for.

E.g. in row 25 there's a supervising producer, an exec producer, an associate producer and two other producers. Are you expecting just the first? Or all of them, and if so combined to a single cell or split across multiple columns?

Also is there a limit (e.g. up to x number of producers) or you want all listed?

[Fabian451]

The sheet has a handfull of main categories, under these categories theres a name and following an occupation/job. I only want to sort the table along the main categories such as "cast", "directed by", "written by" and so on.

a line like:

Cast Judel Dubinsky Grandfather Juda Bleich Morris Rothstein, father Bella Gudinsky Mother Seymour Rechzeit Son Charlotte Goldstein Daughter Isidore Meltzer Comedian Directed by Sidney M. Goldin Written by Harry Kalmanowitz(play) Harry Kalmanowitz(screenplay) Cinematography by Charles Levine Sam Rosen Film Editing by Louis Schwartz Sound Department Douglas Shearer sound recordist

would be separated at "Directeb by" and "Written by" and "Cinematography by", "Film Editing by" and "Sound department" and then the content of the following collumns shifted to the right, so that a the end, all correspoding infos are in the same collumns,

[Glenn Kennedy]

Do you want a GS solution? If so, I will move the thread and step aside, as I know nothing about GS. You should ALWAYS state in Post 1 that a GS solution is needed, when that is the requirement.

It's easy with Office 365...

[Fabian451]

Easy sounds tempting.. but I d not have acess to 365

If it helps, please shift it to the GS section (or Mac xcel 2019, if that sounds more promising)

f