Excel Crew: Help - Comparing two rows of Data

Discussion in 'OT Technology' started by zecro, Jul 17, 2008.

  1. zecro

    zecro OT Supporter

    Joined:
    Oct 31, 2005
    Messages:
    1,164
    Likes Received:
    0
    Location:
    Boston, MA
    I have the following column headings in a ssheet:

    SECTOR ID
    ORIG
    DEST
    1ST FLT TYPE
    NSL
    MONDAY
    TUESDAY
    WEDNESDAY
    THURSDAY
    FRIDAY
    SATURDAY
    SUNDAY
    STA
    MPSTDT
    MPENDDT

    Rows are classified as CH or FS - they are one after the other.

    What Im trying to do is look thru the spreadsheet - if the sector ids are the same I need to look at Monday thru Saturday (Monday thru Saturday have numeric values in them). If the values for Monday or Tuesday or Wednesday etc. match than I need to delete all the rows with similar sector ids and concantenate NSLs into one cell and the FS into one row. The number of sector ids varies - it could be 4 similar ones or 6 etc.

    Any ideas on how to do this? I'll appreciate any help.

    Raw:

    SECTOR ID ORIG DEST 1ST FLT TYPE NSL MONDAY TUESDAY WEDNESDAY
    AUSMIA02 AUS MIA AA2282 CH 1 3,22 3,22 3,22
    AUSMIA02 AUS MIA AA2282 FS 1 1,06 1,06 1,06
    AUSMIA02 AUS MIA AA2282 CH 2 3,22 3,22 3,22
    AUSMIA02 AUS MIA AA2282 FS 2 1,06 1,06 1,06
    BDLJFK02 BDL JFK 1T2330 CH 1 2,77 2,77 2,77
    BDLJFK02 BDL JFK 1T2330 FS 1 0,00 0,00 0,00
    BDLJFK02 BDL JFK 1T2330 CH 2 2,77 2,77 2,77
    BDLJFK02 BDL JFK 1T2330 FS 2 0,00 0,00 0,00
    BDLJFK02 BDL JFK 1T2330 CH 9 2,77 2,77 2,77
    BDLJFK02 BDL JFK 1T2330 FS 9 0,00 0,00 0,00
    BDLJFK02 BDL JFK 1T2330 CH 16 2,77 2,77 2,77
    BDLJFK02 BDL JFK 1T2330 FS 16 0,00 0,00 0,00
    BDLJFK02 BDL JFK 1T2330 CH 17 2,77 2,77 2,77
    BDLJFK02 BDL JFK 1T2330 FS 17 0,00 0,00 0,00

    Finished:

    SECTOR ID ORIG DEST 1ST FLT TYPE NSL CHG FS
    AUSMIA02 AUS MIA AA2282 CH 1,2 3,22 1.06
    BDLJFK02 BDL JFK 1T2330 CH 1,2,9,16,17 2,77 2,77
     
    Last edited: Jul 17, 2008
  2. EkriirkE

    EkriirkE Zika Xenu OT Supporter

    Joined:
    Jan 11, 2004
    Messages:
    14,799
    Likes Received:
    0
    Location:
    Dublin & San Francisco, CA
    if you have your headings in row 1,
    Sector ID=A...WEDS=I
    1. First, set up an auto filter
    2. Filter for and delete all 'CH' rows
    3. Show all, put this formula in a new column (J): =A2=A3
    4. put this formula in yet another column (K): =IF(A2=A1,K1 & "," & F2,F2)
    5. filter column J for 'FALSE'

    That's your resultset, column K will be your new NSL
     
  3. EkriirkE

    EkriirkE Zika Xenu OT Supporter

    Joined:
    Jan 11, 2004
    Messages:
    14,799
    Likes Received:
    0
    Location:
    Dublin & San Francisco, CA
    copy/paste special:values to a new sheet for it as-is
     
  4. zecro

    zecro OT Supporter

    Joined:
    Oct 31, 2005
    Messages:
    1,164
    Likes Received:
    0
    Location:
    Boston, MA
    Getting a "Name" error. I dont want to delete the CH rows - I want to keep those but move the FS values up to the CH rows.
     
  5. EkriirkE

    EkriirkE Zika Xenu OT Supporter

    Joined:
    Jan 11, 2004
    Messages:
    14,799
    Likes Received:
    0
    Location:
    Dublin & San Francisco, CA
    Then, without deleting anything...
    autofilter
    J2: =AND(A2=A3,A3<>A4,E2="CH")
    K2: =IF(E2="CH",IF(A2=A1,K1&","&F2,F2),K1)
    filter J for "TRUE", K has new NSL


    where are you getting the name error?
    [​IMG]
    http://www.ekriirke.com/OT/zecro.xls
     
  6. zecro

    zecro OT Supporter

    Joined:
    Oct 31, 2005
    Messages:
    1,164
    Likes Received:
    0
    Location:
    Boston, MA
    Got it - thanks so much!!

    2 questions:

    1. If I add columns Thurs thru Saturday - I just need to change out the cell references to make the formula read correctly - right?
    2. How do I automatically delete the FS row and move the value from FS into a cell in the CH row?
     
  7. EkriirkE

    EkriirkE Zika Xenu OT Supporter

    Joined:
    Jan 11, 2004
    Messages:
    14,799
    Likes Received:
    0
    Location:
    Dublin & San Francisco, CA
    n/p

    1) yeh
    2) im not sure of a way to automatically delete rows without scripting or filtering and deleting (you can create a macro to do that though). The same goes for overwriting that value (replacing the original value with a calculated value based on that same cell).

    I'd just record a macro to add the formulas, filder, delete and paste-special
    Someone else may have a better idea?
     
  8. zecro

    zecro OT Supporter

    Joined:
    Oct 31, 2005
    Messages:
    1,164
    Likes Received:
    0
    Location:
    Boston, MA
    Got it - thanks again.

    I ended up going with this:

    Sub test()
    With Sheets("Raw Data")
    a = Range("a1").CurrentRegion.Resize(,7).Value
    ReDim b(1 To UBound(a,1), 1 To 8)
    With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For i = 2 To UBound(a,1)
    If a(i,5) = "CH" Then
    If Not .exists(a(i,1)) Then
    n = n + 1 : .add a(i,1), n
    For ii = 1 To 7 : b(n,ii) = a(i,ii) : Next
    Else
    b(.item(a(i,1)), 6) = b(.item(a(i,1)), 6) & "," & a(i,6)
    End If
    ElseIf a(i,5) = "FS" Then
    If .exists(a(i,1)) Then b(.item(a(i,1)), 8) = a(i, 7)
    End If
    Next
    End With
    End With
    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets("Result").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    Sheets.Add.Name = "Result"
    With Sheets("Result").Range("a1")
    .Resize(,8).Value = [{"SECTOR ID","ORIG","DEST","1ST
    FLT","TYPE","NSL","CHG","FS"}]
    .Offset(1).Resize(n,8).Value = b
    End With
    End Sub
     

Share This Page