# Thread: Difficulties using Vlookup/Match/Index (2000)

1. ## 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

2. ## 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 _
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.

3. ## 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
Next
i = 0
For Each rCell In rNew
i = i + 1
vNew(i) = rCell
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.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. ## 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

5. ## 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. ## 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. ## 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. ## 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

Thank you.

10. ## 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. ## 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

12. ## 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 _
Range(Cells(r1, c2), Cells(Rows.Count, c2).End(xlUp)).Sort _
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. ## 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. ## 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,

15. ## 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 Last

#### Posting Permissions

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