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
Dim i As Long
Dim k As Long
Dim j As Long
Dim maxrow As Long
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
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.