Difficulties using Vlookup/Match/Index (2000)

Topic

New Reply

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

Reply | Quote

Replies

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, 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.

Reply | Quote

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

Steve

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Thank you.

Reply | Quote

Impressive. Much simpler than my formulas and no intermeidates. Thanks

Steve

Reply | Quote

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

Reply | Quote

This version of the macro I posted earlier should work with text values:

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, c2) = "" Then
Cells(r, c1).Insert Shift:=xlShiftDown
End If
r = r + 1
Loop
Application.ScreenUpdating = True
End Sub

Reply | Quote

Hello Hans,

Thanks for your reply!

I tested it and it worked like a charm. I tried to modify it for a yet another task and had difficulties. I just coudn’t figre out which way to go. I guess I have to strugle with the code.

I attached a sample worksheet. If you can guide me it will be wonderful as this is the last step I need to overcome to finish a project.

THANK YOU!

-tivoli0

Reply | Quote

This is a completely different task – it doesn’t have to do anything with sorting data. You can use simple VLOOKUP formulas: in F8,

=VLOOKUP($E8,$A$8:$C$12,2,FALSE)

and in G8

=VLOOKUP($E8,$A$8:$C$12,3,FALSE)

then fill down.

Reply | Quote

hello Hans,

Yes, I know it’s a completely different task which I had tried to accomplish on my own.
It seems I got blacked out from the previous posts. . .

Anyway, your reply was on target and thanks to you, and the others who replied, I was able to accomplished what I set forth.

Thanks a million!

-Tivoli0

Reply | Quote

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.

Reply | Quote

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,

Reply | Quote

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…

Reply | Quote

Interesting. It is a little circular – cells refer to themselves as parts of ranges in counts, etc.; perhaps I should have mentioned that — I work at a place where everyone works with iteration on so I forget about the other possibility sometimes. The attachment does work for me as soon as iteration is turned on, though.
Best,

Reply | Quote

The problem is that the calculation and iteration settings in Excel are determined by the first workbook that you open, they remain the same during that session unless you change them explicitly.
So if I open your workbook while Excel is not active, your settings will be applied and the formulas work correctly.
But if a workbook is already open with the default settings (automatic calculation, no iteration), opening your workbook throws up the circular reference message.

Reply | Quote