Macro to compare comma delimited data 2 dimensional arrays?

[Will from London]

Hi,

Part of what I'm trying to do involves comparing the data in two tables at different time periods. What I would like to do is produce a worksheet that shows a third table with the differences. I imagine that the best solution would be a macro which makes use of two dimensional arrays but I'm not all that sure how to use two dimensional ones! If my inputs were:

Table 1:

Name,Weight,Height,Hair
Anne,9,5,Brown
Bob,13,6,Black
Charlie,11,5,Blond

Table 2:
Name,Weight,Height,Hair
Anne,9,6,Green
Bob,13,6,Black
Charlie,11,7,Blond

Output would be a table:
Name,Height,Hair
Anne,From 5 to 6, From Brown to Green
Charlie,From 5 to 7,

The bottom right cell would be blank becaue the data for Charlie's hair has not altered.

The above example was for illustration; the actual data and tables have the following characteristics:
1. Always comma delimited.
2. First row contains column headers.
3. First column contains row headers.
4. Neither will necessarily be sorted alphabetically.
5. Rows and /or columns may be added/deleted between a time period. If rows or columns have been added or deleted the whole new or deleted row or column should be shown and marked as such in the output. One of the impacts of this is that the number of rows and columns may differ between the two tables.

Any suggestions would be much appreciated,

Will

 

[Greg Truby]

I'm going to assume you can get the data pulled into Excel and can create named ranges for each table...

In this example
A3:D5 is a named range: Table1
A9:D12 is a named range: Table2

F3 and copied down to F4:F6 is:
=INDEX(Table2,ROW()-2,1)

The formula in G3 is:
=IF(ISNA(MATCH($F3,OFFSET(Table1,0,0,,1),0)),"New",IF(INDEX(Table2,ROW()-2,COLUMN()-5)=INDEX(Table1,MATCH($F3,OFFSET(Table1,0,0,,1),0),COLUMN()-5),"","From " & INDEX(Table1,MATCH($F3,OFFSET(Table1,0,0,,1),0),COLUMN()-5) & " to " & INDEX(Table2,ROW()-2,COLUMN()-5)))

This can then be copied to G3:I7

From Brown to Green.xls
	A	B	C	D	E	F	G	H	I
1	Table1:					Changes
2	Name	Weight	Height	Hair		Name	Weight	Height	Hair
3	Anne	9	5	Brown		Bob
4	Bob	13	6	Black		Anne		From5to6	FromBrowntoGreen
5	Charlie	11	5	Blond		David	New	New	New
6						Charlie	From11to13	From5to8
7	Table2:
8	Name	Weight	Height	Hair
9	Bob	13	6	Black
10	Anne	9	6	Green
11	David	7	8	Purple
12	Charlie	13	8	Blond
Sheet1

HTH

 

[Will from London]

Hi,

thanks for that. Unfortunately I didn't make the problem all that clear. I'm specifically after a macro solution because the ultimate aim is to compare sets of two folders each containing about 200 tables. The vast majority of the tables will not have changed from one time period to the next so I can strip these out by date stamps - this I can do. What I then need is a sheet that shows the changes, additions and deletions from one period to the next for each table that has changed. Again I can create the loop so that it works through each table.

The problem that I have is to actually compare the data itself where there is a change in the file date stamp. If the value that is present at the intersection of a row and column header is the same I do not want it to be shown in the output. I can do this in a long winded slightly messy way by copying the two tables into Excel and then working through each header, searching for it in the other table, inserting rows, deleting rows etc but I was wondering if there was a neater way of doing this. I know that it is possible to extract a comma delimited line of data into a one dimensional array and then examine the contents but I was really wondering if it were possible to extract a comma delimited set of data into a two dimensional array and then examine and manipulate the data.

Thanks for any help.

Will