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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]6[/TD]
[TD="align: center"]Column E[/TD]
[TD="align: center"]Column F[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Column AL[/TD]
[TD="align: center"]Column AM[/TD]
[TD="align: center"]Column AN[/TD]
[TD="align: center"]Column AO[/TD]
[TD="align: center"]Column AP[/TD]
[TD="align: center"]Column AQ[/TD]
[TD="align: center"]Column AR[/TD]
[TD="align: center"]Column AS[/TD]
[TD="align: center"]AT[/TD]
[TD="align: center"]Column AX[/TD]
[TD="align: center"]BH[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]No. Of Movies[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]Movie Genre[/TD]
[TD="align: center"]Movie Genre2[/TD]
[TD="align: center"]Movie Genre3[/TD]
[TD="align: center"]Movie Genre4[/TD]
[TD="align: center"]Movie Genre5[/TD]
[TD="align: center"]Movie Genre6[/TD]
[TD="align: center"]Movie Genre7[/TD]
[TD="align: center"]Movie Pict.[/TD]
[TD="align: center"]No. Of Movies2[/TD]
[TD="align: center"]Belongs to[/TD]
[TD="align: center"]Column92[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]5-Jan-01[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Action, Drama, Thriller, Crime, [/TD]
[TD="align: center"]Action[/TD]
[TD="align: center"]Drama[/TD]
[TD="align: center"]Thriller[/TD]
[TD="align: center"]Crime[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Act-[/TD]
[TD="align: center"]0100-[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Act-0100-5-Jan-01[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]16-Jan-98[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Action, Crime, Drama[/TD]
[TD="align: center"]Action[/TD]
[TD="align: center"]Crime[/TD]
[TD="align: center"]Drama[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Act-[/TD]
[TD="align: center"]0100-[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Act-0100-01-Jan-16[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]15-Mar-96[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Action, Adventure[/TD]
[TD="align: center"]Action[/TD]
[TD="align: center"]Adventure[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Act-[/TD]
[TD="align: center"]0200-[/TD]
[TD="align: center"]Kurt Russell Collection (Executive Decision / Unlawful Entry)[/TD]
[TD="align: center"]Act-0200-03/15/1996[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]26-Jun-92[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Action, Adventure, Crime, Drama, Thriller[/TD]
[TD="align: center"]Action[/TD]
[TD="align: center"]Adventure[/TD]
[TD="align: center"]Crime[/TD]
[TD="align: center"]Drama[/TD]
[TD="align: center"]Thriller[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Act-[/TD]
[TD="align: center"]0200-[/TD]
[TD="align: center"]Kurt Russell Collection (Executive Decision / Unlawful Entry)[/TD]
[TD="align: center"]Act-0200-06/26/1992[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]24-May-02[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Action, Adventure, Drama, Mystery, Thriller[/TD]
[TD="align: center"]Action[/TD]
[TD="align: center"]Adventure[/TD]
[TD="align: center"]Drama[/TD]
[TD="align: center"] Mystery[/TD]
[TD="align: center"] Thriller[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Act-[/TD]
[TD="align: center"]0201-[/TD]
[TD="align: center"]Devil's Advocate / Insomnia (Double Feature)[/TD]
[TD="align: center"]Act-0201-05/24/2002[/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]17-Oct-97[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Action, Adventure, Drama, Mystery, Thriller[/TD]
[TD="align: center"]Action[/TD]
[TD="align: center"]Adventure[/TD]
[TD="align: center"]Drama[/TD]
[TD="align: center"] Mystery[/TD]
[TD="align: center"] Thriller[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Act-[/TD]
[TD="align: center"]0201-[/TD]
[TD="align: center"]Devil's Advocate / Insomnia (Double Feature)[/TD]
[TD="align: center"]Act-0201-10/17/1997[/TD]

[TD="align: center"]14[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Crap[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]28-Jan-00[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Drama, Mystery, Thriller[/TD]
[TD="align: center"]Drama[/TD]
[TD="align: center"]Mystery[/TD]
[TD="align: center"]Thriller[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Drama-[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]16[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5-Feb-10[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Drama, Music, Romance[/TD]
[TD="align: center"]Drama[/TD]
[TD="align: center"]Music[/TD]
[TD="align: center"]Romance[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Drama-[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]17[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]War, Drama, [/TD]
[TD="align: center"]War[/TD]
[TD="align: center"]Drama[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]War-[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]18[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]War, WWII, History, Drama[/TD]
[TD="align: center"]War[/TD]
[TD="align: center"]WWII[/TD]
[TD="align: center"]History[/TD]
[TD="align: center"]Drama[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]War-[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]19[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]War, Biography, History, Drama, Documentary[/TD]
[TD="align: center"]War[/TD]
[TD="align: center"]Biography[/TD]
[TD="align: center"]History[/TD]
[TD="align: center"]Drama[/TD]
[TD="align: center"]Documentary[/TD]
[TD="align: center"][/TD]
[TD="align: center"]War-[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</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