Please help with formula

[Andries]

Hi

Please help me with a formula. I have two tables...the lookup data is in the first table (colums A,B and C...this data is obviously only a small part of the actual data)

Excel Workbook
	A	B	C
2	BLOCK/ROW/SEAT	SUBJECT CODE	O/B
3	w2-81-1	MNP20YT	*
4	w2-81-2	MNP20YT	*
5	w2-81-3	PTL311T	*
6	w2-81-4	PTL311T	*
7	w2-81-5	AMF40AT	*
8	w2-81-6	BMN23BD	*
9	w2-81-7	DLM501T	O/B
10	w2-81-8	DLM501T	O/B
11	w2-81-9	DLM501T	O/B
12	w2-81-10	DLM501T	O/B
13	w2-82-1	DLM501T	O/B
14	w2-82-2	DLM501T	O/B
15	w2-82-3	DLM501T	O/B
16	w2-82-4	DLM501T	O/B
17	w2-82-5	DLM501T	O/B
18	w2-82-6	DLM501T	O/B
19	w2-82-7	DLM501T	O/B
20	w2-82-8	DLM501T	O/B
21	w2-82-9	DLM501T	O/B
22	w2-82-10	DLM501T	O/B
23	w2-83-1	DLM501T	O/B
24	w2-83-2	DLM501T	O/B
25	w2-83-3	DLM501T	O/B
26	w2-83-4	DLM501T	O/B
27	w2-83-5	DLM501T	O/B
28	w2-83-6	DLM501T	O/B
29	w2-83-7	DLM501T	O/B
30	w2-83-8	DLM501T	O/B
31	w2-83-9	DLM501T	O/B
32	w2-83-10	DLM501T	O/B
data2

The second table must have the lookup formula to return the subject code in Column I and K and M. The problem that I have is if the subject code has an "O/B" next to it in Column C it must start in a new block

Excel Workbook
	H	I	J	K	L	M
2	BLOCK/ROW/SEAT	SUBJECT CODE	BLOCK/ROW/SEAT	SUBJECT CODE	BLOCK/ROW/SEAT	SUBJECT CODE
3	w2-81-1	*	w2-82-1	*	w2-83-1	*
4	w2-81-2	*	w2-82-2	*	w2-83-2	*
5	w2-81-3	*	w2-82-3	*	w2-83-3	*
6	w2-81-4	*	w2-82-4	*	w2-83-4	*
7	w2-81-5	*	w2-82-5	*	w2-83-5	*
8	w2-81-6	*	w2-82-6	*	w2-83-6	*
9	w2-81-7	*	w2-82-7	*	w2-83-7	*
10	w2-81-8	*	w2-82-8	*	w2-83-8	*
11	w2-81-9	*	w2-82-9	*	w2-83-9	*
12	w2-81-10	*	w2-82-10	*	w2-83-10	*
data2

Please look at the last table...it must be populated like this

Excel Workbook
	H	I	J	K	L	M
14	BLOCK/ROW/SEAT	SUBJECT CODE	BLOCK/ROW/SEAT	SUBJECT CODE	BLOCK/ROW/SEAT	SUBJECT CODE
15	w2-81-1	MNP20YT	w2-82-1	DLM501T - O/B	w2-83-1	DLM501T - O/B
16	w2-81-2	MNP20YT	w2-82-2	DLM501T - O/B	w2-83-2	DLM501T - O/B
17	w2-81-3	PTL311T	w2-82-3	DLM501T - O/B	w2-83-3	DLM501T - O/B
18	w2-81-4	PTL311T	w2-82-4	DLM501T - O/B	w2-83-4	DLM501T - O/B
19	w2-81-5	AMF40AT	w2-82-5	DLM501T - O/B	w2-83-5	DLM501T - O/B
20	w2-81-6	BMN23BD	w2-82-6	DLM501T - O/B	w2-83-6	DLM501T - O/B
21	w2-81-7	*	w2-82-7	DLM501T - O/B	w2-83-7	DLM501T - O/B
22	w2-81-8	*	w2-82-8	DLM501T - O/B	w2-83-8	DLM501T - O/B
23	w2-81-9	*	w2-82-9	DLM501T - O/B	w2-83-9	DLM501T - O/B
24	w2-81-10	*	w2-82-10	DLM501T - O/B	w2-83-10	DLM501T - O/B
data2

It is currently looking like this which is not correct and it must look like the above table

Excel Workbook
	H	I	J	K	L	M
14	BLOCK/ROW/SEAT	SUBJECT CODE	BLOCK/ROW/SEAT	SUBJECT CODE	BLOCK/ROW/SEAT	SUBJECT CODE
15	w2-81-1	MNP20YT	w2-82-1	DLM501T - O/B	w2-83-1	DLM501T - O/B
16	w2-81-2	MNP20YT	w2-82-2	DLM501T - O/B	w2-83-2	DLM501T - O/B
17	w2-81-3	PTL311T	w2-82-3	DLM501T - O/B	w2-83-3	DLM501T - O/B
18	w2-81-4	PTL311T	w2-82-4	DLM501T - O/B	w2-83-4	DLM501T - O/B
19	w2-81-5	AMF40AT	w2-82-5	DLM501T - O/B	w2-83-5	DLM501T - O/B
20	w2-81-6	BMN23BD	w2-82-6	DLM501T - O/B	w2-83-6	DLM501T - O/B
21	w2-81-7	DLM501T - O/B	w2-82-7	DLM501T - O/B	w2-83-7	DLM501T - O/B
22	w2-81-8	DLM501T - O/B	w2-82-8	DLM501T - O/B	w2-83-8	DLM501T - O/B
23	w2-81-9	DLM501T - O/B	w2-82-9	DLM501T - O/B	w2-83-9	DLM501T - O/B
24	w2-81-10	DLM501T - O/B	w2-82-10	DLM501T - O/B	w2-83-10	DLM501T - O/B
data2

Thank you in advance
Dries

 

[Andries]

Hi Peter

But what about the values in Cell B9 - B12? the function skips it and it is supose to carry on with returning the values after it "Skipped" the 4 cells?

 

[Peter_SSs]

But what about the values in Cell B9 - B12? the function skips it and it is supose to carry on with returning the values after it "Skipped" the 4 cells?

So..

(If not please make up some new dummy data that has all different values down column B so we can see exactly where they are turning up in column F)

 

[Andries]

Ok lets try it like this...I think you will now see what the problem is

Excel Workbook
	A	B	C	D	E	F	G
2	BLOCK/ROW/SEAT	SUBJECT CODE	STATUS		BLOCK/ROW/SEAT	SUBJECT CODE	STATUS
3	w2-81-1	MNP20YT			w2-81-1	MNP20YT
4	w2-81-2	MNP20YT			w2-81-2	MNP20YT
5	w2-81-3	PTL311T			w2-81-3	PTL311T
6	w2-81-4	PTL311T			w2-81-4	PTL311T
7	w2-81-5	AMF40AT			w2-81-5	AMF40AT
8	w2-81-6	BMN23BD			w2-81-6	BMN23BD
9	w2-81-7	A	O/B		w2-81-7
10	w2-81-8	B	O/B		w2-81-8
11	w2-81-9	C	O/B		w2-81-9
12	w2-81-10	D	O/B		w2-81-10
13	w2-82-1	E	O/B		w2-82-1	E	O/B
14	w2-82-2	F	O/B		w2-82-2	F	O/B
15	w2-82-3	G	O/B		w2-82-3	G	O/B
16	w2-82-4	H	O/B		w2-82-4	H	O/B
17	w2-82-5	I	O/B		w2-82-5	I	O/B
18	w2-82-6	J	O/B		w2-82-6	J	O/B
19	w2-82-7	K	O/B		w2-82-7	K	O/B
20	w2-82-8	L	O/B		w2-82-8	L	O/B
21	w2-82-9	M	O/B		w2-82-9	M	O/B
22	w2-82-10	N	O/B		w2-82-10	N	O/B
23	w2-83-1				w2-83-1	0	O/B
24	w2-83-2				w2-83-2	0	O/B
25	w2-83-3				w2-83-3	0	O/B
26	w2-83-4				w2-83-4	0	O/B
data2

 

[Peter_SSs]

Ok lets try it like this...I think you will now see what the problem is

1. Is this the result you do want, or the result you don't want?

- If it is the result you do want then great, we are finished.

- If it is the result you don't want, please post the result you do want.

2. Did you see my Excel jeanie tip at the bottom of post #4?

 

[Andries]

Ok this not what I am looking for. What about the values A-D in cells B9 - B12...the function needs to continue ....meaning that in column F13 should not start at the value E but from A B C ....

I appreciate you helping me

Dries

 

[Peter_SSs]

Ok this not what I am looking for.

So..

- If it is the result you don't want, please post the result you do want.

And?

2. Did you see my Excel jeanie tip at the bottom of post #4?

 

[Andries]

Hi Peter

Thank you for your help...I will repost my question...and yes I have seen your tip

Have a nice day

 

[Andries]

Hi anybody

Is there somebody that can assist me. Please look at the previous postings to familiarize yourself

 

[Peter_SSs]

Is there somebody that can assist me.

I believe that I can but you still haven't posted the results that you do want for that new data with A, B, C etc in column B (post #13).

You may not be able to do it with a formula (after all that is what you are asking for help with) but can't you just manually type the results you want in to column F and post the screen shot?

 

[Andries]

Hi Peter

It must look like TABLE 2

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