“Merge†data from 2 Excel files

[aw]

Existing I have file A & B.

File A is the summary (pivot table summary from other sources)
File B is the invoices master to store all invoice information.

What I hope to obtain is to generate file C (listed below).

3 Criteria needs :
a). File C should select only data from file A for outstanding amt not
equal to zero (0).
b). File C’s information should be refreshable based on information from
file A & B stored in diff. location.
c). Field “Age (days) – calculation field†is equal to number of days
outstanding. i.e. today() – inv date

As this report should be generated daily, I don’t want to prepare this file
manually.

I am now thinking SQL + pivot table + macro to perform this task. Could you
please let me some hints / ways to solve this problem.

Thanks a lot!!

======================================
File A (Amt outstanding)

invoice debtor outstanding amt
IV004 ABC 140
IV002 BQE 0
IV001 DEF 60
IV003 ABC 0
IV005 ABC 20

File B (invoice master)

invoice inv date
IV004 2007/05/02
IV002 2007/07/09
IV001 2007/11/03
IV003 2007/08/02
IV005 2007/09/11


File C (merge from file A & B)

debtor invoice inv date AGE (days) outstanding amt
ABC IV004 2007/05/02 236 140
ABC IV005 2007/09/11 104 20
DEF IV001 2007/11/03 51 60

 

[yshridhar]

Try this
Sheet 1
Invoice Debtor Amt
IV004 ABC 140
IV002 BQE 0
IV001 DEF 60
IV003 ABC 0
IV005 ABC 20

Sheet 2
Invoice Date
IV004 02/05/2007
IV002 09/07/2007
IV001 03/11/2007
IV003 02/08/2007
IV005 11/09/2007

Sheet 3
Debtor Invoice Date Age Amt
ABC IV004 02/05/2007 236 140
DEF IV001 03/11/2007 51 60
ABC IV005 11/09/2007 104 20

sheet 3!
A2=IF(ROWS(Sheet1!$1:1)<=COUNTIF(Sheet1!$C$2:$C$10,">0"),INDEX(Sheet1!$B$2:$B$10,SMALL(IF(Sheet1!$C$2:$C$10>0,ROW(Sheet1!$B$2:$B$10)-MIN(ROW(Sheet1!$B$2:$B$10))+1),ROWS(Sheet1!$1:1))),"")

B2=iF(ROWS(Sheet1!$1:1)<=COUNTIF(Sheet1!$C$2:$C$10,">0"),INDEX(Sheet1!$A$2:$A$10,SMALL(IF(Sheet1!$C$2:$C$10>0,ROW(Sheet1!$B$2:$B$10)-MIN(ROW(Sheet1!$B$2:$B$10))+1),ROWS(Sheet1!$1:1))),"")
:

C2
=SUMPRODUCT(--(Sheet1!$B$2:$B$6=$A2)*--(Sheet1!$A$2:$A$6=$B2),Sheet2!$B$2:$B$6)

E2=SUMPRODUCT(--(Sheet1!$B$2:$B$6=$A2)*--(Sheet1!$A$2:$A$6=$B2),Sheet1!$C$2:$C$6)
A2, B2 are array forumlas. You have to enter by ctrl+shift+enter not just
enter.
The result is not on "age" field. Hope somebody will give a better way.
with regards
sridhar

 

[Joel]

Here is a macro. An Invoicxe of 0 will pop up with a message saying Invoice
is not found. You can eliminate this if you want.

Sub Daily_Report()

'Get Book C first open row
With ThisWorkbook.ActiveSheet
'add headers if necessary
If Range("A1") = "" Then
Range("A1") = "debtor"
Range("B1") = "invoice"
Range("C1") = "inv date"
Range("D1") = "AGE (days)"
Range("E1") = "outstanding amt"
End If
Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
BkCNewRow = Lastrow + 1
End With

Set fs = CreateObject("Scripting.FileSystemObject")

BookAName = Application _
.GetOpenFilename("Excel Files (*.xls), *.xls")
If BookAName = False Then
MsgBox ("Terminating Macro")
Exit Sub
End If
BookBName = Application _
.GetOpenFilename("Excel Files (*.xls), *.xls")
If BookBName = False Then
MsgBox ("Terminating Macro")
Exit Sub
End If


'Get Book A data
Workbooks.Open Filename:=BookAName
BkARowCount = 2

With ActiveWorkbook.ActiveSheet
Do While .Range("A" & BkARowCount) <> ""
AmountA = .Range("C" & BkARowCount)
If AmountA > 0 Then
InvoiceA = .Range("A" & BkARowCount)
DebtorA = .Range("B" & BkARowCount)

With ThisWorkbook.ActiveSheet
'check if Invoice exists
BkCRowCount = 2
Found = False
Do While .Range("A" & BkCRowCount) <> ""
InvoiceC = .Range("B" & BkCRowCount)
DebtorC = .Range("A" & BkCRowCount)
If InvoiceA = InvoiceC And _
DebtorA = DebtorC Then

Found = True
Exit Do
End If
BkCRowCount = BkCRowCount + 1
Loop
If Found = True Then
response = MsgBox( _
"Invoice Found, Do you want to update?", _
vbYesNo)
If response = vbYes Then
.Range("B" & BkCRowCount) = InvoiceA
.Range("A" & BkCRowCount) = DebtorA
.Range("E" & BkCRowCount) = AmountA
End If
Else
.Range("B" & BkCNewRow) = InvoiceA
.Range("A" & BkCNewRow) = DebtorA
.Range("E" & BkCNewRow) = AmountA
BkCNewRow = BkCNewRow + 1
End If
End With
End If
BkARowCount = BkARowCount + 1
Loop

ActiveWorkbook.Close
End With

'Get Book B data
Workbooks.Open Filename:=BookBName
BkBRowCount = 2

With ActiveWorkbook.ActiveSheet
Do While .Range("A" & BkBRowCount) <> ""
InvoiceB = .Range("A" & BkBRowCount)
DateB = .Range("B" & BkBRowCount)

With ThisWorkbook.ActiveSheet
'check if Invoice exists
Set c = .Columns("B:B").Find(what:=InvoiceB, _
LookIn:=xlValues)
If c Is Nothing Then
MsgBox ("Did not find Invoice : " & InvoiceB)
Else
c.Offset(0, 1) = DateB
c.Offset(0, 2) = Date - DateB
c.Offset(0, 2).NumberFormat = "0"
End If
End With
BkBRowCount = BkBRowCount + 1
Loop
ActiveWorkbook.Close
End With
End Sub