Hi,

I have 1000s of rows of a DOS dir command line of directories and of files of two releases 1.1 and 1.2 of a product listed in two excel columns and I would like to filter the data based on files extensions and output in a third column information about if a certain file that exist in release 1.1 in column 1 does exist in release 1.2 in column 2 and if it does exist output info on whether it has the same size, version and date and if it does not exist output that info too. Also determine if a certain file in release 1.2 in column 2 exists in release 1.1 in column 1 or if it is new. I would like also to add a separate column that shows the directory where each file resides.

In column 1 and 2, there is always a space between the size of the file and the name of the file and also there is always _ before the version number of the file (e.g., _2.3.320) and a .v, _v or just . before the date of the file (e.g., .v20100426, _v20100426 or .20100426)

The listing of file follows this pattern <File Size> <File Name>_<File Version>.v<File Version Date>.<File Extension> e.g., 112,032 com.test.tools.ps.vct_2.3.321.v20100427_0632.rar

Col1 = Version 1.1 Col2 = Version 1.2 Col3 Col4 = File Directory

112,032 com.test.tools.ps.vct_2.3.321.v20100427.rar 143,076 com.test.tools.ps.vct_2.3.320.v20100426.rar Status of file Directory of file in 1.1 Directory of file in 1.2 .rar
(e.g., exists in 1.1 & 1.2 but different size
or exists in 1.1 & 1.2 but different version & date,
"Exists in 1.1 but not 1.2", "Exists in 1.2 but not 1.1" ...

The goal is to be able to press a button in Excel or a batch file that will automatically run a macro to execute a DOS command line (e.g., C:\Program Files\Product\Rel1.1>dir /s /b /o:s > "C:\Rel1.1_Output.txt and C:\Program Files\Product\Rel1.2>dir /s /b /o:s > "C:\Rel1.2_Output.txt) that lists the content of both the install directories of the two installed releases 1.1 and 1.2 of the product and then import the output of the DIR commands to the Excel spreadsheet into two columns and then run a macro to remove any files that are duplicate in release 1.1 and release 1.2 and then copy to a new sheet files with a certain extension (e.g, .all .rar from 1.1 and 1.2 copied on a .rar sheet, all the .zip from 1.1 and 1.2 copied on a different .zip sheet and so on ...). Finally compare all the .rar from release 1.1 and 1.2 and highlight the differences with color codes and do the same for all the other file extension sheets.


Volume in drive C has no label
Volume Serial Number is 8AE3-BG21

Directory of C:\Program Files\Product1\Rel1.1\config

02/18/2009 03:58 AM 4771 MANIF1_210.v20071008.zip
02/18/2009 03:58 AM 813 properties_12.3.v20190203.rar
2 File(s) 674 bytes

Directory of C:\Program Files\Product1\Rel1.1\Test

02/18/2009 03:29 AM 595 Users_7.0.1.20091012.txt
02/18/2009 03:58 AM 436 pluginconfig_3.4_v20061004.war
2 File(s) 595 bytes

Directory of C:\Program Files\Product1\Rel1.1\ARM

02/18/2009 03:58 AM 813 properties.ear
02/18/2009 03:58 AM 3,149 about.html
02/18/2009 03:58 AM 5,143 config_9.8.v20091012.jar
02/18/2009 03:58 AM 24,55 java_interfaces.jar
4 File(s) 34,470 bytes

Total Files Listed:
13 File(s) 2,759,294,592 bytes
0 Dir(s) 3,449,245 bytes free

Thanks,

jmul