need a formula

[Andries]

Hi
I need a formula that can separate the "closed" from the "open" if it is a "open" it must start in n new set as one can see in column E

Excel Workbook
	A	B	C	D	E
1	SEAT NR	SUBJECT	*	SEAT NR	SUBJECT
2	A1	AAD101C - closed	*	A1	AAD101C - closed
3	A2	AAD101C - closed	*	A2	AAD101C - closed
4	A3	AAD101C - closed	*	A3	AAD101C - closed
5	A4	MEQ211T - closed	*	A4	MEQ211T - closed
6	A5	MEQ211T - closed	*	A5	MEQ211T - closed
7	A6	MEQ211T - closed	*	A6	MEQ211T - closed
8	B1	MEQ211T - closed	*	B1	MEQ211T - closed
9	B2	MEQ211T - closed	*	B2	MEQ211T - closed
10	B3	MEQ211T - closed	*	B3	MEQ211T - closed
11	B4	DLM501T- open	*	B4	*
12	B5	DLM501T- open	*	B5	*
13	B6	DLM501T- open	*	B6	*
14	B7	DLM501T- open	*	B7	*
15	B8	DLM501T- open	*	B8	*
16	B9	DLM501T- open	*	B9	*
17	C1	DLM501T- open	*	C1	DLM501T- open
18	C2	DLM501T- open	*	C2	DLM501T- open
19	C3	DLM501T- open	*	C3	DLM501T- open
20	C4	DLM501T- open	*	C4	DLM501T- open
21	C5	DLM501T- open	*	C5	DLM501T- open
22	C6	DLM501T- open	*	C6	DLM501T- open
23	C7	*	*	C7	DLM501T- open
24	C8	*	*	C8	DLM501T- open
25	C9	*	*	C9	DLM501T- open
26	C10	*	*	C10	DLM501T- open
27	C11	*	*	C11	DLM501T- open
28	C12	*	*	C12	DLM501T- open
29	C13	*	*	C13	*
30	C14	*	*	C14	*
31	C15	*	*	C15	*
Sheet11

 

[baitmaster]

Sorry, I don't get what you are trying to do. To identify which are closed or open, you could use =right([your text string],4)

 

[Andries]

Hi

Ok let me explain it again. I have 2(two) Tables as you can see below. the first table is how it currently is...which is not the way I want it. I have made Table 2 to show how it should look. When you look at the SEAT NR column you will note that each block (A...B..C..D) does not have the same number of seats...I don't know if this will be an issue. What the formula should do is to look in column C if there is any "O/B"'s and if so it should start in a new "block" e.g. B-1.

If it helps in any way please note that data have been sorted so that all "O/B" types are together so they are not scattered all over.

thx

Excel Workbook
	A	B	C	D	E	F	G
3	TABLE 1			*	TABLE 2
4	SEAT NR	SUBJECT	TYPE	*	SEAT NR	SUBJECT	TYPE
5	A-1	A	*	*	A-1	A	*
6	A-2	B	*	*	A-2	B	*
7	A-3	C	*	*	A-3	C	*
8	A-4	D	*	*	A-4	D	*
9	A-5	E	*	*	A-5	E	*
10	A-6	F	*	*	A-6	F	*
11	A-7	G	O/B	*	A-7	*	*
12	A-8	H	O/B	*	A-8	*	*
13	A-9	I	O/B	*	A-9	*	*
14	B-1	J	O/B	*	B-1	G	O/B
15	B-2	K	O/B	*	B-2	H	O/B
16	B-3	L	O/B	*	B-3	I	O/B
17	B-4	M	O/B	*	B-4	J	O/B
18	B-5	N	O/B	*	B-5	K	O/B
19	B-6	O	O/B	*	B-6	L	O/B
20	B-7	P	O/B	*	B-7	M	O/B
21	B-8	Q	O/B	*	B-8	N	O/B
22	B-9	R	O/B	*	B-9	O	O/B
23	B-10	S	O/B	*	B-10	P	O/B
24	B-11	T	O/B	*	B-11	Q	O/B
25	B-12	U	O/B	*	B-12	R	O/B
26	C-1	V	O/B	*	C-1	S	O/B
27	C-2	W	O/B	*	C-2	T	O/B
28	C-3	*	*	*	C-3	U	O/B
29	C-4	*	*	*	C-4	V	O/B
30	C-5	*	*	*	C-5	W	O/B
31	D-1	*	*	*	D-1	*	*
Sheet8