Align two spreadsheets with multiple columns of data

[absbrian]

OK, total newbie here, trying to do a bit of comparison work on two huge spreadsheets.

What I have is two spreadsheets, each with 7 columns of data (for the example below I deleted the 7th column as it contained a bit of sensitive data).

Between the spreadsheets, there are many lines that are the same, but at different points throughout a line has been added or removed. These spreadsheets are several thousand lines long.

What I'm looking to do is align them (either next to each other, or somehow insert blank rows in one where a line has been added/removed in the other) based on the value in the fourth column, NAME. Obviously keeping all the data from each individual line connected with all the other data in that line in the results.

This is step one, so that I can then use a nice handy tool like ExcelDiff to compare all the data and quickly point out where the values/data in one line differ from those in the corresponding line in the other spreadsheet. (Ultimately I just need to compare everything in the two spreadsheets based on the field NAME - so if there's a better/simpler way to do this with two monster spreadsheets, I'm open to it, like I said I'm a newbie at this)

I've copied a sample of the very beginning of both spreadsheets below.



CODE GROUPNAME NAME VALUE FACTORYDEFAULT
1 20 X CONTAINERRECORDING N N
2 20 X STORENONUNIQUEPERLOCATION Y Y
3 20 X USETREATMENTCHECK 0 0
4 20 X WEBSERVICEURL . .
5 20 BNL specific settings CONTRACTCHGMINRVPRC 0 0
6 20 BNL specific settings DETAILEDDELIVERYNOTE N N
7 20 BNL specific settings EMBLEMSTOCK Y Y
8 20 BNL specific settings EXPDIRAVENANCE C:\ C:\
9 20 BNL specific settings EXPDIRDOCUWARE C:\X\EXP C:\X\EXP
10 20 BNL specific settings EXPDIRROTAFORM C:\X\EXP C:\X\EXP
11 20 BNL specific settings EXPFILENAMEAVENANCE Avenance Avenance
12 20 BNL specific settings EXPFILENAMEROTAFORM ROTAFORM ROTAFORM
13 20 BNL specific settings IMPDIRARTIKEL C:\ C:\
14 20 BNL specific settings IMPDIRPAYMENTAXAPTA C:\ C:\
15 20 BNL specific settings LOSTREVENUEPERCAFTER 50 50
16 20 BNL specific settings LOSTREVENUEPERCBEFORE 20 20
17 20 BNL specific settings OPTIMAREFRESHRATE 0 0
18 20 BNL specific settings RAGAFTERREPLACEMENT 0 0
19 20 BNL specific settings REPLACEMENTRAGREASON - -
20 20 BNL specific settings REPLACEMENTREASONCODE - -
21 20 BNL specific settings REPLPRICEIMPORTFACTOR 1.4 1.4
22 20 BNL specific settings RESVALUEAFTER - -
23 20 BNL specific settings RESVALUEBEFORE - -
24 20 BNL specific settings SALESPRICEIMPORTFACTOR 2.0 2.0
25 20 BNL specific settings USEWEARERGARMENTSBUDGET N N
26 20 Billing ACCEPTGIROFORMAT 0 0
27 20 Billing ALLOWCUSTSPECIFICPRICE Y Y
28 20 Billing ALLOWMANUALINVOICEDATE Y Y
29 20 Billing ARAGINGMETHOD 1 1
30 20 Billing ARTRANSFORZEROINVOICE N N
31 20 Billing ASCIIPRINTDIRECTINVOICE N Y




CODE GROUPNAME NAME VALUE FACTORYDEFAULT
1 20 X CONTAINERRECORDING N N
2 20 X DIRECTSTOPCHANGE 0 0
3 20 X STORENONUNIQUEPERLOCATION Y Y
4 20 X USETREATMENTCHECK 0 0
5 20 X WEBSERVICEURL . .
6 20 App Settings CUSTOMERSERVICEEMAIL -
7 20 App Settings CUSTOMERSERVICEPHONE -
8 20 App Settings ORDERAPPGRACETIME 5 5
9 20 App Settings ORDERAPPINADVANCEVISITS 6 6
10 20 BNL specific settings CONTRACTCHGMINRVPRC 0 0
11 20 BNL specific settings DETAILEDDELIVERYNOTE N N
12 20 BNL specific settings EMBLEMSTOCK Y Y
13 20 BNL specific settings EXPDIRAVENANCE C:\ C:\
14 20 BNL specific settings EXPDIRDOCUWARE C:\X\EXP C:\X\EXP
15 20 BNL specific settings EXPDIRROTAFORM C:\X\EXP C:\X\EXP
16 20 BNL specific settings EXPFILENAMEAVENANCE Avenance Avenance
17 20 BNL specific settings EXPFILENAMEROTAFORM ROTAFORM ROTAFORM
18 20 BNL specific settings IMPDIRARTIKEL C:\ C:\
19 20 BNL specific settings IMPDIRPAYMENTAXAPTA C:\ C:\
20 20 BNL specific settings LOSTREVENUEPERCAFTER 50 50
21 20 BNL specific settings LOSTREVENUEPERCBEFORE 20 20
22 20 BNL specific settings OPTIMAREFRESHRATE 0 0
23 20 BNL specific settings RAGAFTERREPLACEMENT 0 0
24 20 BNL specific settings REPLACEMENTRAGREASON - -
25 20 BNL specific settings REPLACEMENTREASONCODE - -
26 20 BNL specific settings REPLPRICEIMPORTFACTOR 1.4 1.4
27 20 BNL specific settings RESVALUEAFTER - -
28 20 BNL specific settings RESVALUEBEFORE - -
29 20 BNL specific settings SALESPRICEIMPORTFACTOR 2.0 2.0
30 20 Billing ABUSEANDLOSSSOILCHARGING 0 0
31 20 Billing ACCEPTGIROFORMAT 0 0

[aydeegee]

Hi absbrian

Welcome to the forum.

I have a routine that may be useful. The routine requires that a column be identified to sort each set of data. This column may then be used to match.

For your data, I believe that your matching column needs to be the concatenation of several columns. I'm not sure which ones, so I have used all seven. (This may need revisiting later)

I have used the routine on your data, but then reset it so that you can run the macro.

I have put a few instructions that you may find useful.

If you have not used macros before, you may have to adjust your macro security settings. Go to File > options > Trust Centre > Trust Centre Settings > Macro settings.

Let me know what you think.

Regards
Alastair