# Macro to compare comma delimited data 2 dimensional arrays?

#### Will from London

##### Board Regular
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

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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
ABCDEFGHI
1Table1:Changes
2NameWeightHeightHairNameWeightHeightHair
3Anne95BrownBob
4Bob136BlackAnne From5to6FromBrowntoGreen
5Charlie115BlondDavidNewNewNew
6CharlieFrom11to13From5to8
7Table2:
8NameWeightHeightHair
9Bob136Black
10Anne96Green
11David78Purple
12Charlie138Blond
Sheet1

HTH

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

Replies
5
Views
447
Replies
4
Views
2K
Replies
7
Views
588
Replies
2
Views
461
Replies
2
Views
2K

1,202,987
Messages
6,052,938
Members
444,616
Latest member
novit19089

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back