extracting data from multiple cells and combining into one

[Lizard Of Confusion]

this is part of a book it seams the big problem is figuring out column E, it is a number of movies are in the pack i need E to some how convert when 1 is put into it it will put out in another cell 0100- . this will later be combined with other data on sheet coming from other columns so that when it lands into columns it looks like Act-0100-5-Jan-01 <-- a sorting number code whatever. back to main problem 1= 0100- i dont think is hard and all 1 s can be 0100- but when a 2 is entered the first one can be 0200- which will really be 2 of them 1 for each movie the next 2 entered needs to put out two 0201- s this numbering keeps the disc together for their set Act-0200 is a 2 Kurt Russell movie pack Act-0201- is another movie pack of 2 West-5000 is 50 western movie collection only one of these dose anybody have a clue as to where to even start with trying to make this thing figure out how when or why to change its number from 0200- after making 2 of them to 0201- there is another column in sheet that is listed as "belongs to" this basically the name of the set can this be used some way ?

any help with trying to figure this out is much appreciated and thank you in advance


Excel 2012

	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
6	Column E	Column F		Column AL	Column AM	Column AN	Column AO	Column AP	Column AQ	Column AR	Column AS	AT	Column AX	BH
7	No. Of Movies	Date	Name	Movie Genre	Movie Genre2	Movie Genre3	Movie Genre4	Movie Genre5	Movie Genre6	Movie Genre7	Movie Pict.	No. Of Movies2	Belongs to	Column92
8	1	5-Jan-01		Action, Drama, Thriller, Crime,	Action	Drama	Thriller	Crime			Act-	0100-		Act-0100-5-Jan-01
9	1	16-Jan-98		Action, Crime, Drama	Action	Crime	Drama				Act-	0100-		Act-0100-01-Jan-16
10	2	15-Mar-96		Action, Adventure	Action	Adventure					Act-	0200-	Kurt Russell Collection (Executive Decision / Unlawful Entry)	Act-0200-03/15/1996
11	2	26-Jun-92		Action, Adventure, Crime, Drama, Thriller	Action	Adventure	Crime	Drama	Thriller		Act-	0200-	Kurt Russell Collection (Executive Decision / Unlawful Entry)	Act-0200-06/26/1992
12	2	24-May-02		Action, Adventure, Drama, Mystery, Thriller	Action	Adventure	Drama	Mystery	Thriller		Act-	0201-	Devil's Advocate / Insomnia (Double Feature)	Act-0201-05/24/2002
13	2	17-Oct-97		Action, Adventure, Drama, Mystery, Thriller	Action	Adventure	Drama	Mystery	Thriller		Act-	0201-	Devil's Advocate / Insomnia (Double Feature)	Act-0201-10/17/1997
14											Crap
15	3	28-Jan-00		Drama, Mystery, Thriller	Drama	Mystery	Thriller				Drama-
16	4	5-Feb-10		Drama, Music, Romance	Drama	Music	Romance				Drama-
17	5			War, Drama,	War	Drama					War-
18	6			War, WWII, History, Drama	War	WWII	History	Drama			War-
19	8			War, Biography, History, Drama, Documentary	War	Biography	History	Drama	Documentary		War-

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet5​

 

[Lizard Of Confusion]

let me know if this screen shot needs reposting i dont know why it came out like it did i did same as i done several others

 

[Lizard Of Confusion]

is there a way to when 1 is entered into E .....0100- comes up into AT once and when 2 is entered into E it puts a second 2 into E Plus 0200- twice into AT but if 2 is ever put into E again it puts 0201- instead of the 0200- so that it counts up for the sets the No.s in AT need to count up per set only two 0200- and two 0201- then two 203- ect i maybe thinking this wrong

any help with trying to figure this out is much appreciated and thank you in advance

 

[Lizard Of Confusion]

maybe everything needs to be based on the "belongs to" column where 0200- is supposed to be listed twice and ( Kurt Russell Collection (Executive Decision / Unlawful Entry) is listed twice and 0201- is with ( Devil's Advocate.... ) i feel like this is possible and once seeing the solution it will prob be a thing of why didnt i see that before thing

 

[Lizard Of Confusion]

maybe some sort of index count if thing on "belongs to" the purpose of the "0200-" number was to keep movies together based on genre and date film was released multi movie pacts brought the problem of wanting to keep them together more than genre and film release date the "0200-" could have been dvd pack name but the dvd pack name can be quite long

maybe everything needs to be based on the "belongs to" column where 0200- is supposed to be listed twice and ( Kurt Russell Collection (Executive Decision / Unlawful Entry) is listed twice and 0201- is with ( Devil's Advocate.... ) i feel like this is possible and once seeing the solution it will prob be a thing of why didnt i see that before thing

 

[Lizard Of Confusion]

solved took all day and thanks to The Shaman Cat , gaz_chops , MarcelBeug for helping

=COUNTIF($AX$8:$AX$1004, AX8) on "belongs to" some date data figureing

=IF(AS8="Crap","zzzzz",AS8&AZ8&"-"&BA8&"-"&TEXT(F8,"dd-mmm-yy"))

=TEXT(AY8,"00")

now column can sort and keep groups together Act-01-35515-18-Aug-95