How to populate a value based on the number of instance in the other table

[mart_mrexcel]

I have a "Company" table and Grade Table, how can I dynamicaly populate the Co. Name based on the number of instances in the grade table? See Example in column E & F.

A	B	C	D	E	F
1				Expected Output
2	Co. Name		Grade	Co. Name	Grade
3	Company1		Gr1	Company1	Gr1
4	Company2		Gr2	Company1	Gr2
				Company2	Gr1
				Company2	Gr2

 

[Legacy 143009]

Hello,

Here is a VBA solution:

Sub test()
  Dim companies As Variant, grades As Variant, results As Variant, i As Long, j As Long
  With Application
  companies = .Transpose(Range("B3:B" & Cells(Rows.Count, "B").End(xlUp).Row)) 'actual data
  grades = .Transpose(Range("D3:D" & Cells(Rows.Count, "D").End(xlUp).Row)) 'actual data
 
  ReDim results(1 To 2, 1 To 2)
  results(1, 1) = Range("B2").Value 'header info
  results(2, 1) = Range("D2").Value 'header info
 
  For i = 1 To UBound(companies)
    For j = 1 To UBound(grades)
      results(1, UBound(results, 2)) = companies(i)
      results(2, UBound(results, 2)) = grades(j)
      ReDim Preserve results(1 To 2, 1 To UBound(results, 2) + 1)
    Next
  Next
  ReDim Preserve results(1 To 2, 1 To UBound(results, 2) - 1)
 
  Range("E3").Resize(UBound(results, 2), UBound(results, 1)).Value = .Transpose(results)
  End With
End Sub

 

[Fluff]

With a formula

Fluff.xlsm
	A	B	C	D	E
1	Co. Name		Grade	Co. Name	Grade
2	Company1		Gr1	Company1	Gr1
3	Company2		Gr2	Company1	Gr2
4	Company3			Company2	Gr1
5				Company2	Gr2
6				Company3	Gr1
7				Company3	Gr2
8
Sheet6

Cell Formulas
Range	Formula
D2:E7	D2	=LET(c,FILTER(A2:A100,A2:A100<>""),g,FILTER(C2:C100,C2:C100<>""),HSTACK(TOCOL(IF(SEQUENCE(,ROWS(g)),c)),TOCOL(IF(SEQUENCE(,ROWS(c)),g),,1)))
Dynamic array formulas.

 

[mart_mrexcel]

Hello,

Here is a VBA solution:

Sub test()
  Dim companies As Variant, grades As Variant, results As Variant, i As Long, j As Long
  With Application
  companies = .Transpose(Range("B3:B" & Cells(Rows.Count, "B").End(xlUp).Row)) 'actual data
  grades = .Transpose(Range("D3:D" & Cells(Rows.Count, "D").End(xlUp).Row)) 'actual data
 
  ReDim results(1 To 2, 1 To 2)
  results(1, 1) = Range("B2").Value 'header info
  results(2, 1) = Range("D2").Value 'header info
 
  For i = 1 To UBound(companies)
    For j = 1 To UBound(grades)
      results(1, UBound(results, 2)) = companies(i)
      results(2, UBound(results, 2)) = grades(j)
      ReDim Preserve results(1 To 2, 1 To UBound(results, 2) + 1)
    Next
  Next
  ReDim Preserve results(1 To 2, 1 To UBound(results, 2) - 1)
 
  Range("E3").Resize(UBound(results, 2), UBound(results, 1)).Value = .Transpose(results)
  End With
End Sub

Thank you

 

[mart_mrexcel]

With a formula

Fluff.xlsm
	A	B	C	D	E
1	Co. Name		Grade	Co. Name	Grade
2	Company1		Gr1	Company1	Gr1
3	Company2		Gr2	Company1	Gr2
4	Company3			Company2	Gr1
5				Company2	Gr2
6				Company3	Gr1
7				Company3	Gr2
8
Sheet6

Cell Formulas
Range	Formula
D2:E7	D2	=LET(c,FILTER(A2:A100,A2:A100<>""),g,FILTER(C2:C100,C2:C100<>""),HSTACK(TOCOL(IF(SEQUENCE(,ROWS(g)),c)),TOCOL(IF(SEQUENCE(,ROWS(c)),g),,1)))
Dynamic array formulas.

Thank you fluff. thats work perfect in terms of formula

 

[Fluff]

Glad we could help & thanks for the feedback.