Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Lounger
    Join Date
    Feb 2005
    Location
    Florida, USA
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Difficulties using Vlookup/Match/Index (2000)

    Hi loungers,

    I'm having difficulties how to use Vlookup/Match/Index to resolve my problem.

    Here is what I'm trying to accomplish:
    Every month I want to check lost customers and new customers. I want to present the results in a certain way. The attached workbook resembles the scenario.

    Columns A and B represent the current situation while columns D and E the desired results. Column A is a listing of the current existing customer numbers in a sorted order. Column B is a listing from a different source of all customers, unsorted.

    I want to show the results in 2 columns matched by cell-to-cell as per the following:
    I would like column D to show in a sorted order the customers I have and if there is a new one, to offset while entering a cell with "New" and continue to the next match.

    Similarly, columns E should show "lost" where I've lost a customer, offsetting to the next match.

    I tried the aforementioned formulas and couldn't get the results I wanted. Also, I thought a Macro combination will streamline the execution.

    I will appreciate any help!

    Thank you, -Tivoli0
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Difficulties using Vlookup/Match/Index (2000)

    Someone will probably come up with a clever solution, but I don't know how to do this using formulas. I wrote the following macro to create the lists you want:

    Sub CreateLists()
    Dim r As Long
    ' Modify these constants as needed
    Const r1 = 8 ' First row
    Const c1 = "A" ' First column
    Const c2 = "B" ' Second column
    Const intColor = 6 ' Yellow

    Application.ScreenUpdating = False
    Range(Cells(r1, c2), Cells(Rows.Count, c2).End(xlUp)).Sort _
    Key1:=Cells(r1, c2), Header:=xlNo
    r = r1
    Do While Not (Cells(r, c1) = "" And Cells(r, c2) = "")
    If Cells(r, c1) < Cells(r, c2) Then
    Cells(r, c2).Insert Shift:=xlShiftDown
    Cells(r, c2) = "Lost"
    Cells(r, c2).Interior.ColorIndex = intColor
    ElseIf Cells(r, c1) > Cells(r, c2) Then
    Cells(r, c1).Insert Shift:=xlShiftDown
    Cells(r, c1) = "New"
    Cells(r, c1).Interior.ColorIndex = intColor
    End If
    r = r + 1
    Loop
    Application.ScreenUpdating = True
    End Sub

    See the attached version.
    Attached Files Attached Files

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Difficulties using Vlookup/Match/Index (2000)

    I am not sure it can be done with formulas. This is the method I used (which is different than Hans').

    Steve

    <pre>Option Explicit
    Sub ExampleCode()
    Dim rCurrent As Range
    Dim rNew As Range
    Dim vNew() As Variant
    Dim iNewCt As Integer
    Dim rCell As Range
    Dim colUnique As New Collection
    Dim i As Integer
    Dim j As Integer
    Dim vTemp1 As Variant
    Dim vTemp2 As Variant

    Application.ScreenUpdating = False
    With ActiveSheet
    Set rCurrent = .Range(.Range("A8"), _
    .Cells(.Cells.Rows.Count, 1).End(xlUp))
    Set rNew = .Range(.Range("B8"), _
    .Cells(.Cells.Rows.Count, 2).End(xlUp))
    End With
    iNewCt = rNew.Cells.Count
    ReDim vNew(1 To iNewCt)
    On Error Resume Next
    For Each rCell In rCurrent
    colUnique.Add rCell.Value, CStr(rCell.Value)
    Next
    i = 0
    For Each rCell In rNew
    i = i + 1
    vNew(i) = rCell
    colUnique.Add rCell.Value, CStr(rCell.Value)
    Next
    On Error GoTo 0
    For i = 1 To colUnique.Count - 1
    For j = i + 1 To colUnique.Count
    If colUnique(i) > colUnique(j) Then
    vTemp1 = colUnique(i)
    vTemp2 = colUnique(j)
    colUnique.Add vTemp1, before:=j
    colUnique.Add vTemp2, before:=i
    colUnique.Remove i + 1
    colUnique.Remove j + 1
    End If
    Next
    Next

    For i = 1 To colUnique.Count
    If rCurrent.Cells(i) <> colUnique(i) Then
    rCurrent.Cells(i).Insert
    rCurrent.Cells(i) = "New"
    rCurrent.Cells(i).Interior.Color = vbYellow
    End If
    j = 0
    On Error Resume Next
    j = Application.WorksheetFunction. _
    Match(colUnique(i), vNew, 0)
    On Error GoTo 0
    If j = 0 Then
    rNew.Cells(i) = "Lost"
    rNew.Cells(i).Interior.Color = vbYellow
    Else
    rNew.Cells(i) = colUnique(i)
    If i > iNewCt Then
    rNew.Cells(i).Interior.Color = _
    rNew.Cells(1).Interior.Color
    End If
    End If
    Next

    Range("A1") = "After"
    Range("B5") = "Sorted"
    Range("A5:B5").Copy Range("A6:B6")
    Range("A6:B6") = "Offset"
    Application.ScreenUpdating = True
    MsgBox "Done"

    Set rCell = Nothing
    Set rCurrent = Nothing
    Set rNew = Nothing
    End Sub</pre>


  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Difficulties using Vlookup/Match/Index (2000)

    Here is a way with formulas, though it is not pretty and it requires 4 intermediate columns. I don't hold much hope that the 4 intermidiate columns could be made into arrays...

    Perhaps there is a different scheme....
    [Personally, I think Hans' code is probably the best bet...]

    Steve
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Difficulties using Vlookup/Match/Index (2000)

    I didn't think it was possible using formulas, so thanks, even if using a macro is probably easier here.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Difficulties using Vlookup/Match/Index (2000)

    I tried some things, gave up and went the macro route, then after thinking some more, came up with the convoluted method. I couldn't come up with any Array schemes to make the intermediate columns not be required (though even if I could make them an array, I am not sure the mega-formula could handle it)

    The macro route is easier and I like the simplicity of yours is better than the route I chose. I decided to still post mine (after seeing yours) since some of the techniques used may be useful to someone for other tasks

    Steve

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Difficulties using Vlookup/Match/Index (2000)

    Your macro is a bit more complicated than mine, but it demonstrates some very useful techniques, so it's definitely worthwile to see it.

  8. #8
    Lounger
    Join Date
    Nov 2001
    Location
    Calgary, Alberta, Canada
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Difficulties using Vlookup/Match/Index (2000)

    Attached is a solution using only 2 columns, as required.

    Each column requires a fairly complex array formula which essentially tests for the next customer in the sequence from either column and then displays either the number (if it is is in both source columns) or 'New' or 'Lost' if it is in only 1.

    The formula also contains an extra if statement to test if the maximum customer number has already been reached. This would allow for an output table to built that does not need to be the exact size. Further power could be added to this solution by using dynamic names for the data in columns A and B, this would ensure that once created the formulae would always include ALL customer numbers in the calculation.

    I hope this helps
    Attached Files Attached Files

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Difficulties using Vlookup/Match/Index (2000)

    Thank you.

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Difficulties using Vlookup/Match/Index (2000)

    <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>Impressive. Much simpler than my formulas and no intermeidates. Thanks

    Steve

  11. #11
    Lounger
    Join Date
    Feb 2005
    Location
    Florida, USA
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Difficulties using Vlookup/Match/Index (2000)

    I am back...

    Thanks for the replies!!!

    I've been experimenting and trying all the ways to achieve what I was looking for.
    It's amazing what varieties exist.

    In applying the different techniques and principles to further the experimentation,
    all worked great - as long as I manipulated numbers. However, I had problems working with strings.

    The attached worksheet demonstrate what I mean.

    I am really thankful for your input and was able to learn from you and apply the acquired knowledge.
    I hope you will be able to assist me and suggest a resolution to the attached problem.

    Thank you!

    Tivoli0
    Attached Files Attached Files

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Difficulties using Vlookup/Match/Index (2000)

    This version of the macro I posted earlier should work with text values:
    <code>
    Sub CreateLists()
    Dim r As Long
    ' Modify these constants as needed
    Const r1 = 4 ' First row
    Const c1 = "A" ' First column
    Const c2 = "B" ' Second column

    Application.ScreenUpdating = False
    Range(Cells(r1, c1), Cells(Rows.Count, c1).End(xlUp)).Sort _
    Key1:=Cells(r1, c1), Header:=xlNo
    Range(Cells(r1, c2), Cells(Rows.Count, c2).End(xlUp)).Sort _
    Key1:=Cells(r1, c2), Header:=xlNo
    r = r1
    Do While Not (Cells(r, c1) = "" And Cells(r, c2) = "")
    If Cells(r, c1) < Cells(r, c2) And Not Cells(r, c1) = "" Then
    Cells(r, c2).Insert Shift:=xlShiftDown
    ElseIf Cells(r, c1) > Cells(r, c2) And Not Cells(r, c2) = "" Then
    Cells(r, c1).Insert Shift:=xlShiftDown
    End If
    r = r + 1
    Loop
    Application.ScreenUpdating = True
    End Sub</code>

  13. #13
    Lounger
    Join Date
    Nov 2001
    Location
    Calgary, Alberta, Canada
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Difficulties using Vlookup/Match/Index (2000)

    My current formula approach cannot help because it is limited to sorting numeric values. It may be possible to adapt but that would be far more complex than any VBA solution would ever be.

  14. #14
    Lounger
    Join Date
    Jul 2006
    Location
    New York City, New York, USA
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Difficulties using Vlookup/Match/Index (2000)

    To jump back to the original problem (no text), I think you can do it with non-array formulas making use of SMALL and COUNT and some expanding ranges. Not as elegant as the array or as clear and logical as Steve's formulas, but I figured I'd add it anyway.
    Best,
    Attached Files Attached Files

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Difficulties using Vlookup/Match/Index (2000)

    I get a message about circular references when I open your workbook, and if I press F2 then Enter in any of the formula cells, the result changes to 0...

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •