Automatic cell filling

[Valens500]

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

[#2]

@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.