Results 1 to 15 of 20

20080209, 09:55 #1
 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 celltocell 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

20080209, 14:11 #2
 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.

20080209, 15:05 #3
 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>

20080209, 17:20 #4
 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

20080209, 17:35 #5
 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.

20080209, 20:31 #6
 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 megaformula 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

20080209, 21:53 #7
 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.

20080211, 06:46 #8
 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

20080211, 08:43 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Difficulties using Vlookup/Match/Index (2000)
Thank you.

20080211, 13:05 #10
 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

20080225, 20:50 #11
 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

20080225, 21:58 #12
 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>

20080226, 19:17 #13
 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.

20080226, 23:44 #14
 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 nonarray 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,

20080226, 23:59 #15
 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...