Find the difference in two CSV files

[Sir Regarow]

Hi!

I hope someone can help me. I need some directions or a solution t
find the difference in two CSV files. The structure of the files look
like this:
Location, Time, Product level 1, Product level 2, Product level 3
Company, Value, Volume

File 1:
Denmark, 2001/2002, Ostomy care, Bags, 1 piece, Company 1, 20000
30000
Denmark, 2001/2002, Ostomy care, Bags, 1 piece, Company 2, 10000
25000
Denmark, 2001/2002, Ostomy care, Bags, 1 piece, Company 3, 5000, 15000
Denmark, 2002/2003, Ostomy care, Bags, 1 piece, Company 1, 20000
30000
Denmark, 2002/2003, Ostomy care, Bags, 1 piece, Company 2, 5000, 10000

File 2:
Denmark, 2001/2002, Ostomy care, Bags, 1 piece, Company 1, 10000
25000
Denmark, 2001/2002, Ostomy care, Bags, 1 piece, Company 2, 40000
35000
Denmark, 2001/2002, Ostomy care, Bags, 1 piece, Company 3, 56000, 2000
Denmark, 2002/2003, Ostomy care, Bags, 1 piece, Company 1, 10000
30000
Denmark, 2002/2003, Ostomy care, Bags, 1 piece, Company 2, 4000, 10000

What I need is the difference in total for Location, Time and Produc
level 1

Thanks in advance

 

[Tom Ogilvy]

read in the first (the base values from which changes will be calculated)

Read in the second and change Value/Volumne to negative

append the altered data to the bottom of the first

Use a pivot table to build the diferences for Location, Time, Product Level
1

 

[shockley]

This will do the job without having to open a workbook for each file. I have
named the files "01.csv" and "02.csv".

Kind of a complex way of doing it, but it runs cleaner and faster than using
Excel to open the files.

HTH,
Shockley

Public Const FilesDir As String = "C:\Documents and
Settings\shockley\Desktop\"
Sub Tester()
Cells.ClearContents
arrFiles = Array("01.csv", "02.csv")
Dim arrValueType As Variant
For k = 0 To 1
For j = 1 To 3
x = 0
ReDim arrValueType(1 To 2, 1 To 1)

Open FilesDir & arrFiles(k) For Input As 1
Do
Increment = True
Line Input #1, f1
P1 = InStr(1, f1, ",")
P2 = InStr(P1 + 1, f1, ",")
P3 = InStr(P2 + 1, f1, ",")

Select Case j
Case Is = 1
CommaPos = P1
strLen = P1 - 1
Case Is = 2
CommaPos = P2
strLen = P2 - P1 - 2
Case Is = 3
CommaPos = P3
strLen = P3 - P2 - 2
End Select

ValueType = Mid(f1, CommaPos - strLen, strLen)
If x = 0 Then
arrValueType(1, 1) = ValueType
arrValueType(2, 1) = 1
x = x + 1
Else
For i = LBound(arrValueType, 2) To UBound(arrValueType, 2)
If ValueType = arrValueType(1, i) Then
arrValueType(2, i) = arrValueType(2, i) + 1
Increment = False
Exit For
End If
Next i
If Increment = True Then
ReDim Preserve arrValueType(1 To 2, 1 To UBound(arrValueType, 2)
+ 1)
arrValueType(1, UBound(arrValueType, 2)) = ValueType
z = arrValueType(2, UBound(arrValueType, 2))
arrValueType(2, UBound(arrValueType, 2)) = z + 1
End If
sTest = UBound(arrValueType, 1)
sTest = UBound(arrValueType, 2)
End If

Loop Until EOF(1)
Close (1)
If k = 0 Then
For i = LBound(arrValueType, 2) To UBound(arrValueType, 2)
Cells(i, 2 * j - 1) = arrValueType(1, i)
Cells(i, 2 * j) = arrValueType(2, i)
Next i
Else
For i = LBound(arrValueType, 2) To UBound(arrValueType, 2)
Set rng = Columns(2 * j - 1).Find(arrValueType(1, i))
If rng Is Nothing Then
LastRow = Cells(Rows.Count, 2 * j - 1).End(xlUp).Row
Cells(LastRow, 2 * j - 1) = arrValueType(1, i)
Cells(LastRow, 2 * j) = -1 * arrValueType(2, i)
Else
Cells(rng.Row, 2 * j) = _
Cells(rng.Row, 2 * j) - arrValueType(2, i)
End If
Next i
End If
Erase arrValueType
Next j
Next k
End Sub