I need to compare two data ranges by three criteria (EID, Code, Option) within one Excel sheet and then in the same sheet, I need to generate results, but with re-aligned data for each range based on the three criteria.
The spreadsheet contains 29 columns and the layout is as follows:
1. Each data range has fourteen headers (EID, Name, Code, Option, Number, PDate, Product, Cost, Cost2, Cost3, Type, Benefit, DDate, EDate).
2. There is data under columns “A” to “N”, column “O” has blanks, then more data under columns “P” to “AC.
I found some excellent VBA written by Jindon here:
and I modified it to accomplish most of what I have indicated above. However, my modified VBA generates the results based on only one criteria (EID). I see Jindon used Scripting Dictionary, but I was not able to figure out how to code for the other two criteria (Code, Option).
I have attached a sample file with the modified VBA. It has four sheets:
A. Data – this is the source raw data
B. Results With 1 Criteria – this contains results after running the modified VBA
C. Desired Results With 3 Criteria – this how the re-aligned data needs to generate after running updated VBA
D. Merged Results With 3 Criteria – if possible, have desired results with 3 criteria merged into this sheet
If someone can help me on how to apply the three criteria it will be greatly appreciated!