Extract Values From Cells Based On Corresponding Cell Value

[fiberboysa]

Hi Dear Allz,
I am trying to extract values from preceding rows based on a value which is given in next columns.

Like in following example, in cell A2 Cell value is 10 (Highlighted in Green) the corresponding value in cell D2 is 3 (Highlighted in Red) so the value required in Cell E2 is combination of following three rows of Cell A2 i.e. values of cells A3, A4 and A5 which are A-1111 B-2222 C-3333 respectively. And in case of Cell A6 the corresponding value in cell D6 is 2 so the required value in cell E6 will be D-4444 E-5555.

The data continuous like this and formula or VBA code will fill my requirement in column E. Kindly let me know if you need further clarification. Thanks in advance...

[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]CLMN 1[/TD]
[TD]CLMN 2[/TD]
[TD]CLMN 3[/TD]
[TD]CLMN 4[/TD]
[TD]CLMN 5 (Required Formula/VBA)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]A-1111 B-2222 C-3333[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A-1111[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]B-2222[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]C-3333[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]4444 5555[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]4444[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]5555[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]30[/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]F-6666[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]F-6666 7777 H-8888 9999[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]7777[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]H-8888[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]9999[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

You're welcome & thanks for the feedback

 

[fiberboysa]

Dear Fluff,
There is one more request...
What if I want SLR# in the next line instead of seprated by a space? Like this...

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]A[/TD]
[TD="width: 64"]B[/TD]
[TD="width: 64"]C[/TD]
[TD="width: 64"]D[/TD]
[TD="width: 64"]E[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]CLMN 1[/TD]
[TD]CLMN 2[/TD]
[TD]CLMN 3[/TD]
[TD]CLMN 4[/TD]
[TD]CLMN 5 (Required Formula/VBA)[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD]A-1111[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]SLR# A-1111[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]B-2222[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]SLR# B-2222[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]C-3333[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]SLR# C-3333[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4444[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]SLR# 4444[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5555[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]SLR# 5555[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]SLR# F-6666[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]F-6666[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]SLR# 7777[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7777[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]H-8888[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD]SLR# H-8888[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]9999[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD]ABCXYZ 12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD]SLR# 9999[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I have tried the following but its not working...

Function fiberboysa(Rng As Range, Rws As Long) As String
   Dim i As Long
   Dim Cl As Range
   
   For Each Cl In Rng
      If Left(Cl, 4) = "SLR#" Then
         fiberboysa = fiberboysa & Cl.Value
         ActiveCell.Offset(1, 0).Select
         i = i + 1
         If i = Rws Then Exit For
      End If
   Next Cl
   fiberboysa = Trim(Replace(fiberboysa, "SLR#", ""))
End Function

Function fiberboysa(Rng As Range, Rws As Long) As String
Dim i As Long
Dim Cl As Range

For Each Cl In Rng
If Left(Cl, 4) = "SLR#" Then
fiberboysa = fiberboysa & Cl.Value
Application.SendKeys "{ENTER}"
i = i + 1
If i = Rws Then Exit For
End If
Next Cl
fiberboysa = Trim(Replace(fiberboysa, "SLR#", ""))
End Function

 

[Fluff]

You would be better of with a formula for that.
As that is beyond my knowledge of formulae, you had better start a new thread.