Forum Discussion

Valens500's avatar
Valens500
Copper Contributor
Jul 18, 2022

Automatic cell filling

Hi all,

 

I would like to automatic fill data in columns based on data present in other cells , for example I have a long list of foods ( assume 100) and different buyers (assume 2, John & Jack), for every food in the long list I assign each food to John or Jack using a dedicated column, then I want to fill automatically two separate columns one for John and one  for Jack which list only the foods assigned to John or Jack. Does someone knows how to do it

BI & Data Analysis
excel
Formulas and Functions

1 Reply

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jul 18, 2022

    Valens500 

    Sub JohnJack()

Dim i As Long
Dim k As Long
Dim j As Long
Dim maxrow As Long

Range("E4:F1000").Clear

maxrow = Cells(Rows.Count, 2).End(xlUp).Row

k = 4
j = 4

For i = 4 To maxrow

Select Case Cells(i, 3).Value

Case Is = "John"

Cells(k, 5).Value = Cells(i, 2).Value
k = k + 1

Case Is = "Jack"

Cells(j, 6).Value = Cells(i, 2).Value
j = j + 1

End Select

Next i

End Sub

    Maybe with these lines of code. In the attached file you can click the button in cell H2 to run the macro.

     

    An alternative could be the FILTER function if you work with Office365 or 2021.