Copy values from one sheet to another when column contents match

Question

I am looking for a similar formula, as here: How to copy data from sheet1 to sheet2 with a condition in Excel

I am using:

=IF(EXACT(Sheet1!B4,Sheet2!A7),Sheet1!A4)

only I want to add the condition that if column B of sheet1 doesn't have the value that I am looking for it will look at the next row in column B. If this matches then the value of that row in column A would be the value copied.

Thanks

Answer 1

The below sample could help you to copy values(row wise) from one sheet to another sheet based on matching column in From Sheet.

Sub TodaysActions()

Dim listSheetRange As Range
'Sheet to copy data From
Dim listSheet As Worksheet
'Sheet to copy data To
Dim actionSheet As Worksheet

Set listSheetRange = Worksheets("List").UsedRange
Set listSheet = Worksheets("List")
Set actionSheet = Worksheets("Action")

'Clear the To Sheet
actionSheet.UsedRange.Clear

'Row 1 of From Sheet contains the data to match
'Copy Header Row i.e Row 2 of From Sheet
listSheet.Rows(2).Copy Destination:=actionSheet.Rows(1)

currentActionRow = 2

For i = 3 To listSheetRange.Rows.Count
    'Comparision Condition
    If InStr(listSheetRange.Cells(i, 1), listSheetRange.Cells(1, 3)) Then
        listSheet.Rows(i).Copy Destination:=actionSheet.Rows(currentActionRow)
        currentActionRow = currentActionRow + 1
    End If
Next i

'hide any unwanted columns
actionSheet.Columns(1).Hidden = 1
actionSheet.Activate

End Sub

Answer 2

It seems clear that no one is going to offer you a formula solution. Certainly I would not know how to solve your problem with formulae.

You have not defined the format of either your source or your destination sheet. However, I had some code which I was able to hack around to match possible formats.

On the left of the image below is my source sheet. Note that column C contains a date which I have formatted as "ddd dd" because I find that a convenient format for this type of list. On the right is a print image of the output. The column widths, borders and cell merging are as set by the macro.

The sequence of station names is set by an array within the macro. I have three stations but this is arbitrary. The start date, start time, end time and end date in the output table are set by the earliest and latest values in the source table.

The original validation in the macro does not match your requirement so I have deleted it. You will need to add your own.

The macro does not notice that Angela has two stations at 12:00 on Tuesday. It does notice that source rows 13 and 18 overlap previous entries and reports these errors.

The code below includes comments explaining what it is doing but not why or how. I hope this gives you some ideas. Come back with questions if necessary.

Option Explicit

  Type typStationBooking
    NamePerson As String
    NameStation As String
    BookDate As Date
    BookTimeStart As Long       ' Time in minutes  540 = 9:00
    BookTimeEnd As Long         ' Time in minutes  900 = 15:00
  End Type
Sub ListByNameToListByStation()

  Dim ColDataCrnt As Long
  Dim DateCrnt As Date
  Dim DateLatest As Date
  Dim DateEarliest As Date
  Dim Found As Boolean
  Dim InxBookCrnt As Long
  Dim InxBookMax As Long
  Dim InxStatCrnt As Long
  Dim NumRowsPerDay As Long
  Dim NumStations As Long
  Dim NumTimeSlots As Long
  Dim Occupied As Boolean
  Dim RowDataCrnt As Long
  Dim RowDataDayFirst As Long
  Dim RowDataLast As Long
  Dim RowDataTimeSlot As Long
  Dim StationBooking() As typStationBooking
  Dim StationName() As Variant
  Dim SheetDest As String
  Dim SheetSrc As String
  Dim TimeCrnt As Long
  Dim TimeEarliest As Long
  Dim TimeLatest As Long
  Dim TimeInterval As Long

  ' Names of stations in desired column sequence.  Names must match
  ' those used in worksheet Source.  LBound = 0
  StationName = Array("Station2", "Station3", "Station1")

  SheetDest = "Dest"      ' ) Change to your
  SheetSrc = "Source"     ' ) sheet names

  DateEarliest = -1
  DateLatest = -1

  TimeInterval = 30       ' ) Values in minutes.  Change as necessary
  TimeEarliest = -1
  TimeLatest = -1

  With Sheets(SheetSrc)

    ' First Last used row
    RowDataLast = .Cells(Rows.Count, "A").End(xlUp).Row

    ' Reserve space for rows 2 to RowLast
    ReDim StationBooking(1 To RowDataLast - 1)

    InxBookMax = 0     ' No current entries

    ' Load data from Sheet1 table into array
    For RowDataCrnt = 2 To RowDataLast
      ' ### The source data should be checked:
      ' *  Person name non-blank
      ' *  Station name matches value in StationName()
      ' *  Day is date in range DateFirst to DateLast
      ' *  Start and End times are times in range TimeFirst to
      '    TimeLast+TimeInteval with Start time before End time
      '    and both are of the form TimeStart + N*TimeInterval
      '    where is a positive integer

      InxBookMax = InxBookMax + 1
      StationBooking(InxBookMax).NamePerson = .Cells(RowDataCrnt, 1).Value
      StationBooking(InxBookMax).NameStation = .Cells(RowDataCrnt, 2).Value
      StationBooking(InxBookMax).BookDate = .Cells(RowDataCrnt, 3).Value
      StationBooking(InxBookMax).BookTimeStart = _
            Hour(.Cells(RowDataCrnt, 4).Value) * 60 + _
                                     Minute(.Cells(RowDataCrnt, 4).Value)
      StationBooking(InxBookMax).BookTimeEnd = _
            Hour(.Cells(RowDataCrnt, 5).Value) * 60 + _
                                     Minute(.Cells(RowDataCrnt, 5).Value)
      If DateEarliest = -1 Then
        DateEarliest = StationBooking(InxBookMax).BookDate
        DateLatest = StationBooking(InxBookMax).BookDate
      Else
        If DateEarliest > StationBooking(InxBookMax).BookDate Then
          DateEarliest = StationBooking(InxBookMax).BookDate
        End If
        If DateLatest < StationBooking(InxBookMax).BookDate Then
          DateLatest = StationBooking(InxBookMax).BookDate
        End If
      End If
      If TimeEarliest = -1 Then
        TimeEarliest = StationBooking(InxBookMax).BookTimeStart
        TimeLatest = StationBooking(InxBookMax).BookTimeEnd
      Else
        If TimeEarliest > StationBooking(InxBookMax).BookTimeStart Then
          TimeEarliest = StationBooking(InxBookMax).BookTimeStart
        End If
        If TimeLatest < StationBooking(InxBookMax).BookTimeEnd Then
          TimeLatest = StationBooking(InxBookMax).BookTimeEnd
        End If
      End If
    Next

  End With

  With Sheets(SheetDest)

    ' Lay out destination sheet
    ' Format per day
    ' Row 1 : Date
    ' Row 2 : Station names
    ' Row 3+: One row per time interval from TimeEarliest to
    '                                       TimeLatest + TimeInteval
    ' Row N : Blank row
    ' Col 1 : Time
    ' Col 2+: Station name

    ' Delete current contents
    .Cells.EntireRow.Delete

    NumRowsPerDay = (TimeLatest - TimeEarliest) / TimeInterval + 3
    NumStations = UBound(StationName) + 1

    ' Set column widths
    .Columns(1).ColumnWidth = 6
    For ColDataCrnt = 2 To NumStations + 1
      .Columns(ColDataCrnt).ColumnWidth = 14
    Next

    RowDataCrnt = 1
    DateCrnt = DateEarliest
    Do While DateCrnt <= DateLatest
      RowDataDayFirst = RowDataCrnt
      .Range(.Cells(RowDataCrnt, 1), .Cells(RowDataCrnt, 1 + NumStations)).Merge
      With .Cells(RowDataCrnt, 1)
        .HorizontalAlignment = xlCenter
        .NumberFormat = "dddd d mmmm"
        .Value = DateCrnt
      End With
      RowDataCrnt = RowDataCrnt + 1
      InxStatCrnt = 0
      For ColDataCrnt = 2 To NumStations + 1
        .Cells(RowDataCrnt, ColDataCrnt).Value = StationName(InxStatCrnt)
        InxStatCrnt = InxStatCrnt + 1
      Next
      RowDataCrnt = RowDataCrnt + 1
      TimeCrnt = TimeEarliest
      Do While TimeCrnt < TimeLatest
        With .Cells(RowDataCrnt, 1)
          .NumberFormat = "hh:mm"
          .Value = DateCrnt + TimeSerial(TimeCrnt \ 60, TimeCrnt Mod 60, 0)
        End With
        RowDataCrnt = RowDataCrnt + 1
        TimeCrnt = TimeCrnt + TimeInterval
      Loop
      With .Range(.Cells(RowDataDayFirst, 1), _
                  .Cells(RowDataCrnt - 1, NumStations + 1))
        With .Borders(xlEdgeLeft)
          .LineStyle = xlContinuous
          .Weight = xlThin
          .Color = RGB(192, 192, 192)
        End With
        With .Borders(xlEdgeTop)
          .LineStyle = xlContinuous
          .Weight = xlThin
          .Color = RGB(192, 192, 192)
        End With
        With .Borders(xlEdgeBottom)
          .LineStyle = xlContinuous
          .Weight = xlThin
          .Color = RGB(192, 192, 192)
        End With
        With .Borders(xlEdgeRight)
          .LineStyle = xlContinuous
          .Weight = xlThin
          .Color = RGB(192, 192, 192)
        End With
        With .Borders(xlInsideVertical)
          .LineStyle = xlContinuous
          .Weight = xlThin
          .Color = RGB(192, 192, 192)
        End With
        With .Borders(xlInsideHorizontal)
          .LineStyle = xlContinuous
          .Weight = xlThin
          .Color = RGB(192, 192, 192)
        End With
      End With
      RowDataCrnt = RowDataCrnt + 1
      DateCrnt = DateSerial(Year(DateCrnt), Month(DateCrnt), Day(DateCrnt) + 1)
    Loop

    ' Now place each entry in StationBooking  in the appropriate cell(s)

    For InxBookCrnt = 1 To InxBookMax
      'Debug.Assert InxBookCrnt <> 17
      DateCrnt = StationBooking(InxBookCrnt).BookDate
      RowDataDayFirst = (DateCrnt - DateEarliest) * NumRowsPerDay + 1
      TimeCrnt = StationBooking(InxBookCrnt).BookTimeStart
      RowDataTimeSlot = RowDataDayFirst + 2 + _
                                      (TimeCrnt - TimeEarliest) / TimeInterval
      NumTimeSlots = (StationBooking(InxBookCrnt).BookTimeEnd - TimeCrnt) _
                                                                 / TimeInterval
      Found = False
      For InxStatCrnt = 0 To UBound(StationName)
        If StationBooking(InxBookCrnt).NameStation = _
                                                   StationName(InxStatCrnt) Then
          Found = True
          Exit For
        End If
      Next
      If Not Found Then
        MsgBox ("Row " & InxBookCrnt + 1 & " of worksheet " & SheetSrc & _
                "contains an unknown station name")
      Else
        ColDataCrnt = InxStatCrnt + 2
        ' Check space for this entry is not already occupied
        Occupied = False
        For RowDataCrnt = RowDataTimeSlot To RowDataTimeSlot + NumTimeSlots - 1
          If .Cells(RowDataCrnt, ColDataCrnt) <> "" Then
            Occupied = True
            Exit For
          End If
        Next
        If Not Occupied Then
          If Range(.Cells(RowDataTimeSlot, ColDataCrnt), _
                   .Cells(RowDataTimeSlot + NumTimeSlots - 1, _
                                        ColDataCrnt)).MergeCells Then
            Occupied = True
          End If
        End If
        If Occupied Then
          MsgBox ("Row " & InxBookCrnt + 1 & " of worksheet " & SheetSrc & _
                   " overlaps a previous entry")
        Else
          ' Entire slot is free
          .Cells(RowDataTimeSlot, ColDataCrnt).Value = _
                                     StationBooking(InxBookCrnt).NamePerson
          If NumTimeSlots > 1 Then
            With .Range(.Cells(RowDataTimeSlot, ColDataCrnt), _
                        .Cells(RowDataTimeSlot + NumTimeSlots - 1, ColDataCrnt))
              .Merge
              .WrapText = True
              .VerticalAlignment = xlCenter
             End With
          End If
        End If
      End If
    Next

  End With

End Sub