Dividing cells into two lists and keeping lists constant

%3CLINGO-SUB%20id%3D%22lingo-sub-2581265%22%20slang%3D%22en-US%22%3EDividing%20cells%20into%20two%20lists%20and%20keeping%20lists%20constant%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2581265%22%20slang%3D%22en-US%22%3EHello.%3CBR%20%2F%3E%3CBR%20%2F%3EI%E2%80%99m%20using%20Excel%202016%20for%20PC%2C%20and%20I%E2%80%99m%20trying%20to%20create%20a%20guide%20to%20putting%20up%20posters%20at%20the%20cinema%20I%E2%80%99m%20working%20at.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20a%20long%20list%20of%20coming%20premieres%20and%20have%20filtered%20it%20to%20only%20show%20films%20that%20we%20have%20the%20poster%20for.%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20cinema%20is%20divided%20into%20an%20upstairs%20and%20a%20downstairs%2C%20with%209%20spots%20for%20posters%20upstairs%20and%206%20spots%20downstairs.%20We%20place%20our%20posters%20in%20order%20of%20release%20date%2C%20with%20it%20divided%20between%20the%20floors.%3CBR%20%2F%3E%3CBR%20%2F%3ENow%2C%20dividing%20the%20list%20into%20two%20separate%20lists%20with%20the%20release%20date%20in%20mind%20shouldn%E2%80%99t%20be%20much%20of%20a%20problem.%20However%2C%20I%20want%20a%20film%20to%20be%20removed%20from%20the%20list%20if%20the%20release%20date%20has%20passed%2C%20move%20the%20others%20up%20one%20spot%2C%20and%20the%20next%20unused%20poster%20from%20the%20main%20list%20to%20fill%20the%20empty%20spot%20at%20the%20bottom.%3CBR%20%2F%3E%3CBR%20%2F%3EAgain%2C%20not%20too%20difficult%2C%20but%20I%20want%20the%20two%20lists%20to%20be%20separate%2C%20so%20once%20a%20movie%20has%20been%20placed%20in%20one%20list%2C%20it%20keeps%20moving%20up%20in%20that%20same%20list.%20It%20can%E2%80%99t%20jump%20to%20the%20other%20list.%3CBR%20%2F%3E%3CBR%20%2F%3EAny%20tips%20on%20the%20best%20way%20to%20do%20this%3F%3CBR%20%2F%3E%3CBR%20%2F%3EThanks.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2581265%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2581967%22%20slang%3D%22en-US%22%3ERe%3A%20Dividing%20cells%20into%20two%20lists%20and%20keeping%20lists%20constant%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2581967%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1111279%22%20target%3D%22_blank%22%3E%40hellstadius%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eyour%20go%20to%20solution%20would%20be%20power%20query%20with%20release%20date%20as%20the%20parameter.%26nbsp%3B%20Please%20share%20your%20workbook%20so%20we%20can%20find%20out%20where%20it%20needs%20fine%20tuning%20and%20create%20queries%20for%20each%20list.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Echeers%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2582226%22%20slang%3D%22en-US%22%3ERe%3A%20Dividing%20cells%20into%20two%20lists%20and%20keeping%20lists%20constant%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2582226%22%20slang%3D%22en-US%22%3EHi!%20Thanks%20for%20the%20response.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20already%20used%20power%20query%20to%20filter%20out%20any%20movie%20we%20don%E2%80%99t%20have%20a%20poster%20for%20and%20sorted%20them%20in%20the%20correct%20order.%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20only%20thing%20left%20to%20do%20is%20to%20divide%20them%20into%20the%20separate%20lists.%20I%20don%E2%80%99t%20know%20how%20to%20do%20it%20since%20if%20I%20reference%20a%20cell%20in%20the%20list%2C%20once%20a%20release%20date%20is%20passed%2C%20all%20the%20cells%20will%20change.%3CBR%20%2F%3E%3CBR%20%2F%3EI%E2%80%99m%20afraid%20I%20can%E2%80%99t%20attach%20the%20file%20since%20it%E2%80%99s%20for%20internal%20use%20only%20and%20therefore%20confidential.%3CBR%20%2F%3E%3CBR%20%2F%3EBut%20basically%2C%20I%20reference%20a%20large%20.xls%20file%20that%20shows%20every%20movie%20for%20every%20cinema.%20I%20filter%20out%20any%20passed%20premiere%2C%20and%20any%20premiere%20more%20than%20six%20months%20in%20the%20future.%20It%20shows%20that%20in%20a%20sheet%20where%20I%E2%80%99ve%20added%20another%20column%20where%20us%20who%20work%20here%20can%20add%20a%20check%20to%20any%20poster%20we%20have%2C%20and%20then%20using%20another%20power%20query%20I%20filter%20out%20any%20row%20without%20that%20check.%3CBR%20%2F%3E%3CBR%20%2F%3ENow%20in%20a%20second%20sheet%20I%20have%20a%20list%20of%2025%20films%20in%20order%20of%20release%20date%2C%20automatically%20set%20up%20to%20remove%20any%20movie%20that%20passes%20their%20release%20date.%3CBR%20%2F%3E%3CBR%20%2F%3EHopefully%20that%E2%80%99s%20enough%20information%20to%20guide%20me%20in%20the%20correct%20direction.%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20not%2C%20thanks%20for%20your%20time.%3C%2FLINGO-BODY%3E
New Contributor
Hello.

I’m using Excel 2016 for PC, and I’m trying to create a guide to putting up posters at the cinema I’m working at.

I have a long list of coming premieres and have filtered it to only show films that we have the poster for.

The cinema is divided into an upstairs and a downstairs, with 9 spots for posters upstairs and 6 spots downstairs. We place our posters in order of release date, with it divided between the floors.

Now, dividing the list into two separate lists with the release date in mind shouldn’t be much of a problem. However, I want a film to be removed from the list if the release date has passed, move the others up one spot, and the next unused poster from the main list to fill the empty spot at the bottom.

Again, not too difficult, but I want the two lists to be separate, so once a movie has been placed in one list, it keeps moving up in that same list. It can’t jump to the other list.

Any tips on the best way to do this?

Thanks.
5 Replies

@hellstadius 

 

your go to solution would be power query with release date as the parameter.  Please share your workbook so we can find out where it needs fine tuning and create queries for each list.

 

cheers

Hi! Thanks for the response.

I have already used power query to filter out any movie we don’t have a poster for and sorted them in the correct order.

The only thing left to do is to divide them into the separate lists. I don’t know how to do it since if I reference a cell in the list, once a release date is passed, all the cells will change.

I’m afraid I can’t attach the file since it’s for internal use only and therefore confidential.

But basically, I reference a large .xls file that shows every movie for every cinema. I filter out any passed premiere, and any premiere more than six months in the future. It shows that in a sheet where I’ve added another column where us who work here can add a check to any poster we have, and then using another power query I filter out any row without that check.

Now in a second sheet I have a list of 25 films in order of release date, automatically set up to remove any movie that passes their release date.

Hopefully that’s enough information to guide me in the correct direction.

If not, thanks for your time.

@hellstadius 

 

I dont need the movie titles.  you can anonymize the structure of the spreadsheet to show it you can use movie1 movie2 movie3..etc

for the locations u can use location1 location2 etc

 

In excel the structure of geography of the data dictates what function one can use.

 

without that intel your guess is as good as mine

 

you know the structure

 

and i'm familiar with the excel functions.

 

so what will it be, your choice.

 

cheers

think of it this way
If i give you a grid street map without street names, and I tell you the address of the building to get to, how will you get there
Hi.

The problem is that I’m not as well educated in Excel as I really need to be.

The reference sheet is around 7000 cells, and I don’t have the know how to be able to effectively change large numbers of data before I attach it, and I’d probably mess up the power query if I try to reference the new sheet.

So if I can’t get help without providing any attachments, I guess I’ll have to figure it out myself.

Thanks so much for the help, though.