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]

How about this UDF

Function fiberboysa(Cl As Range, Rws As Long) As String
   fiberboysa = Join(Application.Transpose(Cl.Offset(1).Resize(Rws).Value), " ")
End Function

Used like =fiberboysa(A2,D2)

 

[fiberboysa]

Thanks for reply. I have tried the UDF but its giving me VALUE error where value in column D is 1.

 

[Fluff]

Ok, how about

Function fiberboysa(Cl As Range, Rws As Long) As String
   If Rws = 1 Then
      fiberboysa = Cl.Offset(1).Value
   Else
      fiberboysa = Join(Application.Transpose(Cl.Offset(1).Resize(Rws).Value), " ")
   End If
End Function

 

[Fluff]

This will cater for 0 or empty in col D

Function fiberboysa(Cl As Range, Rws As Long) As String
   Select Case Rws
      Case 1
         fiberboysa = Cl.Offset(1).Value
      Case Is > 1
         fiberboysa = Join(Application.Transpose(Cl.Offset(1).Resize(Rws).Value), " ")
      Case Else
         fiberboysa = ""
   End Select
End Function

 

[fiberboysa]

This will cater for 0 or empty in col D

Function fiberboysa(Cl As Range, Rws As Long) As String
   Select Case Rws
      Case 1
         fiberboysa = Cl.Offset(1).Value
      Case Is > 1
         fiberboysa = Join(Application.Transpose(Cl.Offset(1).Resize(Rws).Value), " ")
      Case Else
         fiberboysa = ""
   End Select
End Function

Thanks dear!!! you saved my day. Have a good life ahead

 

[Fluff]

You're welcome & thanks for the feedback

 

[fiberboysa]

Hi Fluff,
When I was checking my data I found an error. There is one more addition in scenario that it should only take cell value if it starts with a specific character like if cell value starts with "SRL#" then this UDF should take this cell value else it should move to next cell. I hope following table will clarify this further.
[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]SLR# A-1111[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]SLR# B-2222[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]SLR# 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]SLR# 4444[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]SLR# 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]SLR# F-6666[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]F-6666 7777 H-8888 9999[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]SLR# 7777[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]SLR# H-8888[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]ABCXYZ 12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]SLR# 9999[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

You can see that after row 11 the next row i.e. row 12 is empty so UDF should ignore this empty row and in row 14 there is text that do not start with SLR# so it should be ignored as well.

 

[Fluff]

How about

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
         i = i + 1
         If i = Rws Then Exit For
      End If
   Next Cl
   fiberboysa = Trim(Replace(fiberboysa, "SLR#", ""))
End Function

used like
=fiberboysa(A9:A15,D9)

 

[fiberboysa]

Yes!!! that worked!
My data is usually in10,000+ rows. It took time but it worked. I've also changed the range of Cl to a variable so it just search within range rather 10000 rows . Thanks again and have a good day!