From a table (Sheet1) I want to fetch data based on two columns and three rows using formulas or VBA that checks until the first empty row or first empty column. Criteria is in rows 1:3 and columns A:B. There is always only one combination for certain parameters, e.g. Jack, 01.01.96-31.12.96, 1 (column A) and 6 (Column B) appears only once and at intersection of it is number 2.
What do I have to do in order to get output in column C on Sheet2 based on cells B1:B3 Sheet2 and A5:B24 on Sheet2?
For Sheet1 and given input on Sheet2, output in column C Sheet2 should look like in the picture:
Excel Workbook | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | |||
1 | Object | Glenn | Jack | John | Ross | Melinda | Ralph | Ed | Melinda | John | Jack | Jack | |||
2 | From | 01.02.95. | 01.02.95. | 01.02.95. | 01.02.95. | 01.02.95. | 01.02.95. | 02.04.97. | 01.03.95. | 01.01.96. | 01.01.96. | 01.01.97. | |||
3 | To | 31.12.95. | 31.12.95. | 31.12.95. | 31.12.95. | 31.12.95. | 31.12.95. | 18.09.98. | 31.11.95 | 31.12.96. | 31.12.96. | 30.06.97. | |||
4 | SUM 1 | SUM | 60 | 368 | 144 | 45 | 108 | 23 | 249 | 17 | 8 | 8 | 5 | ||
5 | SUM 2 | SUM | 492 | 269 | 32 | 137 | 544 | 8 | 803 | 11 | 15 | 40 | 17 | ||
6 | SUM 3 | SUM | 237 | 682 | 64 | 23 | 163 | 31 | 404 | 15 | 10 | 5 | 6 | ||
7 | SUM 4 | SUM | 661 | 888 | 215 | 70 | 571 | 46 | 104 | 9 | 1 | 14 | 13 | ||
8 | SUM 5 | SUM | 399 | 492 | 44 | 54 | 134 | 7 | 54 | 8 | 8 | 32 | 19 | ||
9 | SUM 6 | SUM | 87 | 68 | 91 | 25 | 30 | 33 | 458 | 1 | 9 | 9 | 10 | ||
10 | SUM 7 | SUM | 607 | 389 | 76 | 32 | 399 | 52 | 4 | 13 | 17 | 4 | 22 | ||
11 | SUM 8 | SUM | 355 | 123 | 324 | 13 | 12 | 1 | 25 | 124 | 36 | 4 | 21 | ||
12 | SUM 9 | SUM | 78 | 115 | 42 | 32 | 61 | 48 | 716 | 2 | 1 | 4 | 1 | ||
13 | 1 | 1 | 20 | 13 | 1 | 1 | 17 | 4 | 5 | 1 | 0 | 1 | 1 | ||
14 | 1 | 2 | 11 | 5 | 5 | 1 | 3 | 1 | 22 | 1 | 0 | 1 | 0 | ||
15 | 1 | 3 | 1 | 14 | 3 | 1 | 9 | 4 | 18 | 1 | 0 | 1 | 1 | ||
16 | 1 | 4 | 21 | 5 | 3 | 1 | 11 | 6 | 28 | 0 | 0 | 2 | 1 | ||
17 | 1 | 5 | 7 | 18 | 2 | 1 | 11 | 3 | 23 | 0 | 0 | 1 | 1 | ||
18 | 1 | 6 | 22 | 16 | 3 | 3 | 8 | 1 | 34 | 0 | 1 | 2 | 0 | ||
Sheet1 |
What do I have to do in order to get output in column C on Sheet2 based on cells B1:B3 Sheet2 and A5:B24 on Sheet2?
For Sheet1 and given input on Sheet2, output in column C Sheet2 should look like in the picture:
Excel Workbook | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | Person | Jack | |||
2 | From | 01.01.96. | |||
3 | To | 31.12.96. | |||
4 | Column1 | To | Value | ||
5 | SUM 8 | SUM | 4 | ||
6 | SUM 9 | SUM | 4 | ||
7 | 1 | 1 | 1 | ||
8 | 1 | 2 | 1 | ||
9 | 1 | 3 | 1 | ||
10 | 1 | 4 | 2 | ||
11 | 1 | 5 | 1 | ||
12 | 1 | 6 | 2 | ||
13 | 1 | 7 | 1 | ||
14 | 1 | 8 | 2 | ||
15 | 1 | 9 | 1 | ||
16 | 1 | 10 | 1 | ||
17 | 1 | 11 | 0 | ||
18 | 1 | 12 | 0 | ||
19 | 1 | 13 | 1 | ||
20 | 1 | 14 | 1 | ||
21 | 1 | 15 | 2 | ||
22 | 1 | 16 | 2 | ||
23 | 1 | 17 | 2 | ||
24 | 1 | 18 | 0 | ||
Sheet2 |