1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

VBA search two columns same time and get data

Discussion in 'Business Applications' started by Deletedmember566433, Feb 10, 2010.

Mark Solved
Thread Status:
Not open for further replies.
Advertisement
  1. Deletedmember566433

    Deletedmember566433 Guest Thread Starter

    Hello,

    I have a really big problem in excel that I don't know how to do it.
    In my workbook, I have 4 imported columns from txt files. They are located in: Z, AA, AB and AC.
    • Z & AA contain the first and last name. This can't contain any numbers.
    • AB & AC contain the arrival/departure hours. This can't contain any words.

    The list of names is made as the folowing:
    • the unique names always start from the top
    • the dupplicated names always start after the unique list.

    There can't be more than two instances of the same name and the list is contiguous. Based on that, I need to find only the dupplicated names that have one arrival and one departure hour in cells AB and AC. So, the first matched name should have AC blank and the dupplicate will have AB blank. I will concatenate the names if it's easier to do the macro but I'd rather not because the workbook itself is packed with loads of formulas and it's really making it difficult to calculate.
    An example has been attached containg the expected result. The workbook has 57 sheets and they all use the same reference: Z, AA, AB and AC from row 17 and down.

    Thank you for reading and for your intent to help. I appreaciated.
    Alex
     

    Attached Files:

  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    No doubt someone will have solved this before I can even have a proper "go".

    That said, the very first thing any code would (AFAI can see) need to do would be: sort Z17 region (in the example case Z17:AC21) by column AB so that all blanks in AB (departure "records") shift to the bottom.

    So the first question is: does the original order of the data need to be preserved?
     
  3. Deletedmember566433

    Deletedmember566433 Guest Thread Starter

    Hi Bomb! No, the order doesn't matter. Thanks for the help.
     
  4. Jimmy the Hand

    Jimmy the Hand

    Joined:
    Jul 28, 2006
    Messages:
    1,223
    Hi

    Sorry for not being able to put it simpler. I think this will do, however.
    Code:
    Sub Test()
        Dim Names As Range, ResultDestination As Range, FirstBlank As Range
        Dim c As Range, Hit As Boolean, i As Long, j As Long
        Dim Deps(), Arrs()
        
        Set ResultDestination = Range("AE17")
        Set FirstBlank = Range("AB17:AC" & Rows.Count).SpecialCells(xlCellTypeBlanks).Range("A1")
        Set Names = Intersect(FirstBlank.EntireRow, Range("Z:Z"))
        Set Names = Range(Names, Names.End(xlDown))
        ReDim Deps(1 To Application.WorksheetFunction.Count(Names.Offset(, 2)))
        ReDim Arrs(1 To Application.WorksheetFunction.Count(Names.Offset(, 3)))
        
        For Each c In Names
            If c.Offset(, 2) <> "" Then
                i = i + 1
                Deps(i) = c.Row
            End If
            If c.Offset(, 3) <> "" Then
                j = j + 1
                Arrs(j) = c.Row
            End If
        Next
            
        ResultDestination.Resize(UBound(Deps), 4).ClearContents
        For i = 1 To UBound(Deps)
            Hit = False
            For j = 1 To UBound(Arrs)
                If Range("Z" & Deps(i)) & Range("AA" & Deps(i)) = Range("Z" & Arrs(j)) & Range("AA" & Arrs(j)) Then
                    Hit = True
                    Exit For
                End If
            Next
            If Hit Then
                With ResultDestination.Resize(UBound(Deps)).SpecialCells(xlCellTypeBlanks).Range("A1")
                    .Value = Range("Z" & Deps(i))
                    .Offset(, 1) = Range("AA" & Deps(i))
                    .Offset(, 2) = Range("AB" & Deps(i))
                    .Offset(, 3) = Range("AC" & Arrs(j))
                End With
            End If
        Next
    End Sub
     
  5. Deletedmember566433

    Deletedmember566433 Guest Thread Starter

    Hi Jimmy. Thanks for your help.
    I tested the code and works fine but has some few issues:
    1. Only get the data from the dupplicates if the name exists twice and IF it exists with one entry (AB) or one exit (AC). So, only match the dupplicates with incomplete entry/exit points. Exclude dupplicates with:

    &#8226;same name and two entry points
    &#8226;same name and two exit points
    &#8226;same name with both entry/exit hours (in the same row).

    If, for example, there are people with both entry and exit points (same row) and there is a dupplicate of the same name with only one entry/exit point then ignore that person. And also, ignore the unique names with both entry/exit points (same row).

    Hope I explained well and sorry for the long message. I won't be able to replay anymore today because I'll be away. I'll return tomorrow. Thanks goes to everybody.
     
  6. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    To be honest I'm struggling to understand (forget post #2 for a start, I'm pretty sure that was along the wrong lines).

    The list of names is made as the folowing:
    &#8226; the unique names always start from the top
    &#8226; the dupplicated names always start after the unique list.


    The unique names & the duplicated names. Does this mean that in the example, the uniques are in Z17:AA19 & the duplicates are in Z20:AA21?

    I will concatenate the names if it's easier to do the macro but I'd rather not ...

    For now I have -- at least until there's a clearer picture. Code below will copy the Z17:AC21 data to Sheet2, then concatenate the names in (ultimately) column A and identify the first row (if any) for duplicates as I understand it.

    If that's along the right lines, please confirm.

    Sub test()
    Sheets("Sheet1").Select
    Range("Z17").CurrentRegion.Copy Sheets("Sheet2").Range("C1")
    Sheets("Sheet2").Select
    x = Range("C" & Rows.Count).End(xlUp).Row
    Range("B1:B" & x).FormulaR1C1 = "=RC[1]&"" ""&RC[2]"
    Range("A1:A" & x).FormulaR1C1 = "=COUNTIF(R1C2:RC[1],RC[1])"
    Res = Application.Match(2, Columns(1), 0)
    Columns(1).Delete
    Columns(1).Value = Columns(1).Value
    If IsError(Res) Then
    Range("A:E").EntireColumn.Delete
    MsgBox "No dupes."
    Else
    MsgBox "Dupes start at row " & Res & "."
    End If
    End Sub
     
  7. Jimmy the Hand

    Jimmy the Hand

    Joined:
    Jul 28, 2006
    Messages:
    1,223
    Yeah, I don't get it either. Maybe my English is not good enough. So I figure there are these possibilities:

    1. Unique name, where both Departure and Arrival are given, in the same row.
    2. Duplicate name, where both Departure and Arrival are given, but in different rows.
    3. Duplicate name, where there are two Departures and no Arrival
    4. Duplicate name, where there is no Departure and two Arrivals
    5. Duplicate name, where there are two Departures and one Arrival
    6. Duplicate name, where there are one Departure and two Arrivals

    Is that correct so far? If yes, please explain, what you want to do with each.

    Jimmy
     
  8. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Meanwhile here's a method to identify the first dupe (if any) "virtually". I used (a variation of) it in the past for something similar, forgot about it till now.

    Sub test2()
    For Each Cell In Range("Z17").CurrentRegion.Resize(, 1)
    If InStr(NameList, Cell & " " & Cell.Offset(, 1)) = 0 Then
    NameList = NameList & Cell & " " & Cell.Offset(, 1) & ";"
    Else
    NameList = NameList & Cell & " " & Cell.Offset(, 1) & ";;"
    End If
    Next Cell
    x = InStr(NameList, ";;")
    If x = 0 Then
    MsgBox "No dupes."
    Else
    NameList = Left(NameList, x - 1)
    NumUniques = Len(NameList) - Len(Replace(NameList, ";", ""))
    MsgBox NumUniques & " rows of uniques."
    End If
    End Sub


    It progressively builds a string of names, adding ";" each time if it's not in the list already or ";;" if it is.

    So for the example it would create (leaving the actual names out) "Person1;Person2;Person3;Person3;;Person1;;".

    From there it's just: find the first ";;" (if any) in the string -- calculate the number of ";"s preceding that -- result = number of rows of uniques (3 in this case).

    ETA: one thing to be wary of though -- however many "Alex Ceed"s there are in the world, there's very many more "John Smith"s. ;)
     
  9. Deletedmember566433

    Deletedmember566433 Guest Thread Starter

    I'm sorry for not describing the problem correctly.

    1. Unique name, where both Departure and Arrival are given, in the same row.
    Ignore them. Unique names, where only one arrival or departure exist must also be ignored.

    3. Duplicate name, where there are two Departures and no Arrival
    Ignore them.

    4. Duplicate name, where there is no Departure and two Arrivals
    Ignore them.

    5. Duplicate name, where there are two Departures and one Arrival
    Ignore them as well.

    6. Duplicate name, where there are one Departure and two Arrivals
    Ignore them as well.


    2. Duplicate name, where both Departure and Arrival are given, but in different rows.
    This what we're looking for.
    If a duplicate name exits, then go to the first instance of that name and search if it contains the arrival hour in that row AND NOT the departure. If, on that first instance, it finds a departure time then ignore that name.
    If the first instance contains the arrival hour and the second instance of that name contains the departure hour then concatenate column AA with Z and place it in column AE. Then get the arrival hour and departure hours and place them in AF and AG.

    This code must run on all sheets except the first two: Sheet1 and Sheet2.
    Hoping that, at least now, it's understandable. Again, accept my apology as I'm having a really bad week.
     
  10. Deletedmember566433

    Deletedmember566433 Guest Thread Starter

    Been a long time since somebody replied in this thread... Any update on this or is it hopeless?
     
  11. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    The answers to Jimmy's specific questions were posted. He's more "VBA proper" adept than me, so I took that as my cue to bow out.

    I'll have another look this PM.

    Edit

    I have to return to the beginning. :(

    "the unique names always start from the top"

    So in your example that's Z17:AA19, because that's the first time each of those names appear in the list. Those are the "uniques", any below are duplicates, correct?

    If so, how come "Alex Ceed - 07:09 - 16:03" is in the results? The "Alex Ceed - 07:09" part comes from row 17, which is part of the "uniques".
     
  12. Deletedmember566433

    Deletedmember566433 Guest Thread Starter

    Sorry man, I didn't see your edited post.

    1st question: That is correct. z17:z19 are unique values.

    2nd question: "Alex Ceed" is in the results because it is a duplicate, it starts with the arrival hour and the final duplicate of "Alex Ceed" ends with the departure hour.
    Please see my attachment. It fully shows what the macro should do in order to get the results. And please use the information in post #9 in order to exclude unwanted results.
    Thanks man for still wanting to do this. Don't get me wrong, I really need this and I can't figure out how to it without vba.
     

    Attached Files:

  13. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Well ... I wrote something. :D

    It's got an "If/Then" with 5 conditions (i.e. 4 "And"s) in it. :eek:

    "Dyllan Afler has two arrivals and one departure."

    Yes, but he gets eliminated by my criteria on account of the surnames being spelled differently.

    All the same, run test & you'll get the results you want.

    Which brings me to: are those where Name count <> 2 also to be eliminated?
     

    Attached Files:

  14. Deletedmember566433

    Deletedmember566433 Guest Thread Starter

    Yes, but he gets eliminated by my criteria on account of the surnames being spelled differently.
    Correct.

    ... are those where Name count <> 2 also to be eliminated?
    Yes, eleiminated them. And could you also make it run on all sheets that contain data in columns Z to AC (from row 17 and down).?

    Once the results are made, would it be possible for the code to create another list located at AJ:AK based on unique names that contain the same date? I uploaded an example of how it should work.

    I know you probably hate me for all these changes but it just occured to me. Thanks so much for what you've done so far. I already tested your code in the real workbook and works fine.
     

    Attached Files:

  15. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    "This code must run on all sheets except the first two: Sheet1 and Sheet2."

    Are they actually called "Sheet1" and "Sheet2"? (not just the first 2 sheets in the book, position-wise?)

    "unique names that contain the same date"

    I'll have to check your latest version. :D

    EDIT.

    "Yes, eleiminated them. And could you also make it run on all sheets that contain data in columns Z to AC (from row 17 and down).?"

    I added:

    If WorksheetFunction.CountIf(Columns(35), Cell.Value) <> 2 Then
    Cell.Offset(, 1) = 2


    to eliminate any Name count <> 2.

    And added:

    If InStr("Sheet1Sheet2", Sheet.Name) = 0 Then
    If Sheet.Range("Z17") <> "" Then


    to filter out "Sheet1", "Sheet2", and any sheet where Z17 = "".
     

    Attached Files:

  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/901729

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice