VLOOKUP IS NOT Enough

S

Shams

I have tried applying many formulas suggested which are posted but in vain, I
have got partial results but not complete. Let me explain my proble.

I am working on two different files let us say Name1 and Name2

Name 1 Data:

Invoice Number INV AMT
ARSINHUBHU107/2527 23585
ARSINHUBHU107/2527 36739
ARSINHUBHU107/2528 7199
ARSINHUBHU107/2529 11792
ARSINHUBHU107/2530 11792
ARSINHUBHU107/2530 12246
ARSINHUBHU107/2531 24492
ARSINHUBHU107/2532 11792
ARSINHUBHU107/2539 12246
ARSINHUBHU107/2540 8398
ARSINHUBHU107/2540 8789
ARSINHUBHU107/2541 11792
ARSINHUBHU107/2544 12246
ARSINHUBHU107/2544 7199
ARSINHUBHU107/2545 12246
ARSINHUBHU107/2545 7199
ARSINHUBHU107/2550 18215
ARSINHUBHU107/2550 12246
ARSINHUBHU107/2550 7199
ARSINHUBHU107/2551 11792
ARSINHUBHU107/2605 44720


Name 2 Data
Invoice Number ReceiptNo
ARSINHUBHU107/2527 ARMCNHUBHU107/520
ARSINHUBHU107/2527 RAHUB20070700061-00001
ARSINHUBHU107/2528 ARMCNHUBHU107/541
ARSINHUBHU107/2528 RAHUB20070700061-00001
ARSINHUBHU107/2529 RAHUB20070700061-00001
ARSINHUBHU107/2530 RAHUB20070700061-00001
ARSINHUBHU107/2531 RAHUB20070700061-00001
ARSINHUBHU107/2532 RAHUB20070700061-00001
ARSINHUBHU107/2539 RAHUB20070700061-00001
ARSINHUBHU107/2540 RAHUB20070700061-00001
ARSINHUBHU107/2541 RAHUB20070700061-00001
ARSINHUBHU107/2544 RAHUB20070700061-00001
ARSINHUBHU107/2545 RAHUB20070700061-00001
ARSINHUBHU107/2550 RAHUB20070700061-00001
ARSINHUBHU107/2551 RAHUB20070700061-00001
ARSINHUBHU107/2551 RAHUB20070700061-00002
ARSINHUBHU107/2604 RAHUB20070700001-00001
ARSINHUBHU107/2605 RAHUB20070700001-00001
ARSINHUBHU107/2606 RAHUB20070700001-00001
ARSINHUBHU107/2607 RAHUB20070700001-00001
ARSINHUBHU107/2608 RAHUB20070700001-00001
ARSINHUBHU107/2612 RAHUB20070700061-00002
ARSINHUBHU107/2617 RAHUB20070700001-00001
ARSINHUBHU107/2619 RAHUB20070700126-00002
ARSINHUBHU107/2620 RAHUB20070700126-00002
ARSINHUBHU107/2620 RAHUB20070700133-00001
ARSINHUBHU107/2634 RAHUB20070700133-00001
ARSINHUBHU107/2638 RAHUB20070700026-00001
ARSINHUBHU107/2639 RAHUB20070700133-00001
ARSINHUBHU107/2664 RAHUB20070700090-00001
ARSINHUBHU107/2682 RAHUB20070700001-00001



This is a big file and is not always sorted
The result which I wish to get is the following.


Invoice Number INV AMT ReceiptNo
ARSINHUBHU107/2527 23585 ARMCNHUBHU107/520
ARSINHUBHU107/2527 36739 RAHUB20070700061-00001
ARSINHUBHU107/2528 7199 ARMCNHUBHU107/541
ARSINHUBHU107/2528 7199 RAHUB20070700061-00001


Is it possible? I hope so ( It is not that easy as it looks)

This is a real challenge, all the Excel experts out there, kindly help.
Thanks in advance

Cheers
Sharmila……
 
S

Shams

Here Amt is not the important factor, If the invoice numbers and receipt
numbers are matched then my problem will be solved.

If you notice the data, it is bi directional, in the first instance one
invoice number is repeated twice but it has 2 different matches, in the
second instance invoice number appears only once in first sheet, but in
reality it has two matches. I dont know how far you will be able to get the
right mix, but it will help greatly if it does. The report has over 100 such
records in total.

kindly help
 
T

Toppers

Try this macro:

Invoice data on Sheet1, Receipt data on Sheet2, results on Sheet3.

Option Explicit

Sub Invoice2Receipt()

Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim irow As Long, orow As Long
Dim Lastrow As Long
Dim col As Integer
Dim n As Integer
Dim nr As Integer
Dim nv As Integer
Dim nvp As Integer
Dim nn As Integer

Dim invnum As Variant

Dim x() As String
Dim inva(100, 2) As String
Dim receipt(100) As String

Dim rnga1 As Range, rnga2 As Range, cell As Range
Dim c
Dim firstaddress As String

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
Set ws3 = Worksheets("Sheet3")

col = 1
orow = 1

With ws2
Lastrow = .Cells(Rows.Count, col).End(xlUp).Row
Set rnga2 = .Range("a2:A" & Lastrow)
End With

ws3.Cells(1, 1).Resize(1, 3) = Array("Invoice number", "Amount", "Receipt
Number")

With ws1

Lastrow = .Cells(Rows.Count, col).End(xlUp).Row
Set rnga1 = .Range("a2:A" & Lastrow)

nvp = 0
ReDim Preserve x(nvp)
' x(1) = rnga1(1)

For Each cell In rnga1
invnum = Application.Match(cell, x, 0)
If IsError(invnum) Then
nv = 0
' Find all invoices for this invoice number
With rnga1
Set c = .Find(cell, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
' store invoice number and amount
nv = nv + 1
inva(nv, 1) = c.Value
inva(nv, 2) = c.Offset(0, 1)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
End With
' find all receipts for this invoice number
With rnga2
nr = 0
Set c = .Find(cell, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
'store receipt numbers
nr = nr + 1
receipt(nr) = c.Offset(0, 1)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
End With
With ws3
nn = 0
For n = 1 To nv
orow = orow + 1
nn = nn + 1
.Cells(orow, 1) = inva(nn, 1)
.Cells(orow, 2) = inva(nn, 2)
.Cells(orow, 3) = receipt(n)
Next
For n = nn + 1 To nr
orow = orow + 1
.Cells(orow, 1) = inva(nn, 1)
.Cells(orow, 2) = inva(nn, 2)
.Cells(orow, 3) = receipt(n)
Next
End With
nvp = nvp + 1
ReDim Preserve x(nvp)
x(nvp) = cell
End If
Next cell

End With
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Outlook crashes with Outlook connector 2002 0
Outlook connector crashes outlook 0

Top