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

 

[Peter_SSs]

Hi Peter

It must look like TABLE 2

Thank you.

This suggestion uses a helper cell to keep the column F formula a bit shorter. I have used cell I2 for this. Formula in F2 is copied down. You may have to adjust the range in the F2 formula before copying as I'm not sure whether your data goes to row 200 or beyond.

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

If you don't want to use the helper cell then the F2 formula would need to be:

=IF(ROWS(F$2:F2)<=COUNTBLANK(C$2:C$11),B2,IF(ROWS(F$2:F2)<=10,"",INDEX($B$2:$B$200,ROWS(F$2:F2)-10+COUNTBLANK(C$2:C$11))))

 

[Andries]

Hi Peter

It is perfect and working for real this time. Thanks for putting up with me.

Regards,

Dries

 

[Peter_SSs]

It is perfect and working for real this time. Thanks for putting up with me.

No problem, it just took a long time to show what you wanted rather that what you didn't want.

Just one further thing, if you were to use another helper column like column H below, the formulas would be considerably simpler and require less calculation resources. The helper column(s) could be hidden after they have been filled in if you don't like their visual impact.

Column H is just filled with 1, 2, 3 etc from row 2 down. The new formula for F2 is shown below my screen shot.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	TABLE 1				TABLE 2
2	w2-81-1	A			w2-81-1	A		1	6
3	w2-81-2	B			w2-81-2	B		2
4	w2-81-3	C			w2-81-3	C		3
5	w2-81-4	D			w2-81-4	D		4
6	w2-81-5	E			w2-81-5	E		5
7	w2-81-6	F			w2-81-6	F		6
8	w2-81-7	G	O/B		w2-81-7			7
9	w2-81-8	H	O/B		w2-81-8			8
10	w2-81-9	I	O/B		w2-81-9			9
11	w2-81-10	J	O/B		w2-81-10			10
12	w2-82-1	K	O/B		w2-82-1	G		11
13	w2-82-2	L	O/B		w2-82-2	H		12
14	w2-82-3	M	O/B		w2-82-3	I		13
15	w2-82-4	N	O/B		w2-82-4	J		14
16	w2-82-5	O	O/B		w2-82-5	K		15
17	w2-82-6	P	O/B		w2-82-6	L		16
data2

 

[Andries]

Hi Peter

What if the column C's value is variable...meaning that "O/B" will not always start in Cell C8
but it can start anywhere else in column C?

 

[Peter_SSs]

Hi Peter

What if the column C's value is variable...meaning that "O/B" will not always start in Cell C8
but it can start anywhere else in column C?

I don't know what the consequence of that is. What results would you expect if it started, for example, in

a) C3?

b) C14?

 

[Andries]

Hi Peter

If "O/B" start in C3 then the formula works fine by placing/starting it in the next set but when it starts in C14 it should then place it in the next set that follows (F18)

Currently it is doing this:

Excel Workbook
	A	B	C	D	E	F
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			w2-81-7	G
9	w2-81-8	H			w2-81-8	H
10	w2-81-9	I			w2-81-9	I
11	w2-81-10	J			w2-81-10	J
12	w2-82-1	K			w2-82-1	K
13	w2-82-2	L			w2-82-2	L
14	w2-82-3	M			w2-82-3	M
15	w2-82-4	N	O/B		w2-82-4	Q
16	w2-82-5	O	O/B		w2-82-5	R
17	w2-82-6	P	O/B		w2-82-6	S
18	w2-83-1	Q	O/B		w2-83-1	T
19	w2-83-2	R	O/B		w2-83-2	U
20	w2-83-3	S	O/B		w2-83-3	V
21	w2-83-4	T	O/B		w2-83-4	W
22	w2-83-5	U	O/B		w2-83-5	X
23	w2-83-6	V	O/B		w2-83-6	Y
24	w2-83-7	W	O/B		w2-83-7	Z
25	w2-83-8	X	O/B		w2-83-8	0
26	w2-83-9	Y	O/B		w2-83-9	0
27	w2-83-10	Z	O/B		w2-83-10	0
data2

 

[Peter_SSs]

It appears from your last screen shot that the 'sets' are no longer uniform in size and are only identified by colour. If that is the case I don't see how a formula could do what you want, at least without helper columns that would need manual entry to identify the start of each 'set'. Please confirm whether the 'sets' are uniform size or not.

(Once again you appear to have shown the result you don't want, not the result you do want )

Also, the formula you have shown in your last post is not the formula I provided in post #21. That formula would have produced this result (which I know is still not what you want)

Excel Workbook
	A	B	C	D	E	F
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			w2-81-7	G
9	w2-81-8	H			w2-81-8	H
10	w2-81-9	I			w2-81-9	I
11	w2-81-10	J			w2-81-10	J
12	w2-82-1	K			w2-82-1	K
13	w2-82-2	L			w2-82-2	L
14	w2-82-3	M			w2-82-3	M
15	w2-82-4	N	O/B		w2-82-4	N
16	w2-82-5	O	O/B		w2-82-5	O
17	w2-82-6	P	O/B		w2-82-6	P
18	w2-83-1	Q	O/B		w2-83-1	Q
19	w2-83-2	R	O/B		w2-83-2	R
20	w2-83-3	S	O/B		w2-83-3	S
21	w2-83-4	T	O/B		w2-83-4	T
22	w2-83-5	U	O/B		w2-83-5	U
23	w2-83-6	V	O/B		w2-83-6	V
24	w2-83-7	W	O/B		w2-83-7	W
25	w2-83-8	X	O/B		w2-83-8	X
26	w2-83-9	Y	O/B		w2-83-9	Y
27	w2-83-10	Z	O/B		w2-83-10	Z
data2 32 (2)