Return Multiple Values

[Ameryjackson]

Hello, I am having great difficulty in producing table 3 from tables 1 and 2. I have had some success using INDEX and MATCH functions, however they are limited to one result and will only return the first result. Any help is greatly appreciated. Thanks in advance.

ID	Meters Total
A	5
B	8
C	10

<tbody>
</tbody>

Table 1

ID	Depth From	Depth To	Code
A	1	2	Quartz
B	4	6	Bones
B	7	8	Bones
C	4	7	Roots
A	2	4	Quartz
C	9	10	Quartz

<tbody>
</tbody>

Table 2

ID	Depth From	Depth To	Code
A	0	1
A	1	2	Quartz
A	2	3	Quartz
A	3	4	Quartz
A	4	5
B	0	1
B	1	2
B	2	3
B	3	4
B	4	5	Bones
B	5	6	Bones
B	6	7
B	7	8	Bones
C	0	1
C	1	2
C	2	3
C	3	4
C	4	5	Roots
C	5	6	Roots
C	6	7	Roots
C	7	8
C	8	9
C	9	10	Quartz

<tbody>
</tbody>

Table 3

 

[MickG]

If VBA is acceptable then try this:-
With Table 1 starting "A1", and Table2 starting "D1" then the results Start "I1".

[COLOR="Navy"]Sub[/COLOR] MG16Oct38
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Ray(), n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
ReDim Preserve Ray(1 To 4, 1 To 1)
Ray(1, 1) = "ID": Ray(2, 1) = "Depth From": Ray(3, 1) = "Depth To": Ray(4, 1) = "Code"
c = 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]For[/COLOR] n = 1 To Dn.Offset(, 1)
        c = c + 1
        ReDim Preserve Ray(1 To 4, 1 To c)
        Ray(1, c) = Dn.Value
        Ray(2, c) = n - 1
        Ray(3, c) = n
    [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("D2"), Range("D" & Rows.Count).End(xlUp))
    [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
        .CompareMode = vbTextCompare
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
            [COLOR="Navy"]For[/COLOR] Ac = 1 To Dn.Offset(, 2).Value - Dn.Offset(, 1).Value
                txt = Dn.Value & Dn.Offset(, 1).Value + Ac - 1 & Dn.Offset(, 1).Value + Ac
            
                    [COLOR="Navy"]If[/COLOR] Not .exists(txt) [COLOR="Navy"]Then[/COLOR]
                         .Add txt, Dn.Offset(, 3).Value
                    [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Next[/COLOR] Ac
        [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] n = 2 To UBound(Ray, 2)
    txt = Ray(1, n) & Ray(2, n) & Ray(3, n)
        [COLOR="Navy"]If[/COLOR] .exists(txt) [COLOR="Navy"]Then[/COLOR] Ray(4, n) = .Item(txt)
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] Range("I1").Resize(c, 4)
    .Value = Application.Transpose(Ray)
    .Borders.Weight = 2
    .Columns.AutoFit
[COLOR="Navy"]End[/COLOR] With

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Zubair Muhammad]

Hi @ Ameryjackson

You need an array formula to get the codes in Table3

Assuming you Table2 starts from Cells A1 (Headings in row1)

Put this formula in the Table 3 Code Column and PRESS CTRL+SHIFT+ENTER

=IFERROR(INDEX($D$2:$D$7,MATCH(1,(A17=$A$2:$A$7)*(B17>=$B$2:$B$7)*(C17<=$C$2:$C$7),0),1),"")

DONT MISS CTRL+SHIFT+ENTER

 

[Zubair Muhammad]

 

[Marcelo Branco]

A solution using formulas

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

1​

ID​

Meters Total​

ID​

Depth From​

Depth To​

Code​

ID​

Depth From​

Depth To​

Code​

Helper​

2​

A​

5​

A​

1​

2​

Quartz​

A​

0​

1​

0​

3​

B​

8​

B​

4​

6​

Bones​

A​

1​

2​

Quartz​

5​

4​

C​

10​

B​

7​

8​

Bones​

A​

2​

3​

Quartz​

13​

5​

C​

4​

7​

Roots​

A​

3​

4​

Quartz​

23​

6​

A​

2​

4​

Quartz​

A​

4​

5​

7​

C​

9​

10​

Quartz​

B​

0​

1​

8​

B​

1​

2​

9​

B​

2​

3​

10​

B​

3​

4​

11​

B​

4​

5​

Bones​

12​

B​

5​

6​

Bones​

13​

B​

6​

7​

14​

B​

7​

8​

Bones​

15​

C​

0​

1​

16​

C​

1​

2​

17​

C​

2​

3​

18​

C​

3​

4​

19​

C​

4​

5​

Roots​

20​

C​

5​

6​

Roots​

21​

C​

6​

7​

Roots​

22​

C​

7​

8​

23​

C​

8​

9​

24​

C​

9​

10​

Quartz​

25​

<tbody>
</tbody>

Helper column (to make the formulas simpler) - gray area
Formula in N2 copied down
=SUM(B$1:B1)

Formula in I2 copied down
=IF(ROWS(I$2:I2)>N$5,"",INDEX(A$2:A$4,MATCH(ROWS(I$2:I2)-1,N$2:N$4)))

Formula in J2 copied down
=IF(I2="","",COUNTIF(I$2:I2,I2)-1)

Formula in K2 copied down
=IF(J2="","",J2+1)

Formula in L2 copied down
=IF(J2="","",IF(COUNTIFS(D$2:D$7,I2,E$2:E$7,"<="&J2,F$2:F$7,">="&K2),INDEX(G$2:G$7,MATCH(I2,D$2:D$7,0)),""))

Hope this helps

M.