Loop through two tables to create an output

drop05

Active Member
Joined
Mar 23, 2021
Messages
285
Office Version
  1. 365
Platform
  1. Windows
Hello, i am trying to find a way to take these two tables I have on two sheets and create an output. In the tab called "Lead" is X amounts of codes that i am entering. In tab "PH" there is a table where to pull from using only columns B, and E.
I am trying to loop through from tab Lead from the first code, in cell A4 down to the last row, not a7 more can be added, and loop through the list in PH that many times so X, collecting the info from column B and E and putting them into the tab called "Output" starting at row 3 down, but also collecting that first code from the lead tab and putting it in the output tab as well, with column D in the output tab always being "G", then going to the next one collecting that code and the same two columns info from PH and putting it after that first pasting, and so on. I have shown an example of the output in the output tab.

Thank you in advance!

Book2
ABCD
1
2
3Codes
412345
554312
698765
776543
8
9
Lead


Book2
ABCDEF
1UserNameUser IDLevelSign offType
2Jane Doejd.ID1PrepSO1
3John Deerjd1.ID2R1SO2
4James Gunnjg.ID3R2SO3
5
6
7
PH
Cell Formulas
RangeFormula
E2:E4E2=IFS(D2="Prep","SO1",D2="R1","SO2",D2="R2","SO3")


Book2
ABCDE
1UsernameTypeCodeActivity
2
3jd.IDSO112345G
4jd1.IDSO212345G
5jg.IDSO312345G
6jd.IDSO154312G
7jd1.IDSO254312G
8jg.IDSO354312G
9jd.IDSO198765G
10jd1.IDSO298765G
11jg.IDSO398765G
12jd.IDSO176543G
13jd1.IDSO276543G
14jg.IDSO376543G
15
16
17
Output


I had some code i was playing around with to start it but not sure if i am on the right track and getting stuck

VBA Code:
Dim lr&, i&, j&, k&, rngName, rngID

If shtCompID.Range("A2") = Empty Then
    MsgBox "Please enter Component Code(s)."
    Exit Sub
Else

    With shtFilteredUsers
        lr = .Cells(Rows.Count, "A").End(xlUp).Row
        rngName = .Range("A3:A" & lr)
    End With
    
    With shtCompID
        lr = .Cells(Rows.Count, "A").End(xlUp).Row
        rngID = .Range("A2:A" & lr)
    End With
        For j = 1 To UBound(rngID)
            For i = 1 To UBound(rngName)
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Where does Activity: G information come from?
column D in the output tab always being "G"

@drop05
Assuming ..
  • Table on 'Lead' is called tblLead (adjust code to suit)
  • Table on PH is called tblPH (adjust code to suit)
  • Sheet 'Output' exists but is empty
.. then try this with a copy of your workbook.

VBA Code:
Sub drop05()
  Dim a As Variant, b As Variant, c As Variant
  Dim i As Long, j As Long, k As Long
  
  a = Range("tblLead[Codes]").Value
  b = Range("tblPH").Value
  ReDim c(1 To UBound(a) * UBound(b), 1 To 4)
  For i = 1 To UBound(a)
    For j = 1 To UBound(b)
      k = k + 1
      c(k, 1) = b(j, 2): c(k, 2) = b(j, 5): c(k, 3) = a(i, 1): c(k, 4) = "G"
    Next j
  Next i
  With Sheets("Output").Range("A3:D3")
    .Resize(UBound(c)).Value = c
    .Offset(-2).Value = Array("Username", "Type", "Code", "Activity")
  End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top