Can someone provide a excel VBA script for comparing concatenated columns plus separate certain data into 2 other column fields?

BudZ 121 Reputation points


We are trying to compare Persons name, project number, and hours in SystemA to the same Persons name, project number, and hours in SystemB.

SystemA has the data all in 3 separate columns and SystemB has the data all in 2 separate columns with the persons name and project number in a column on separate rows and hours in the 2nd column. The data in System B is always Persons name in a row by itself, project number always starting with CSR, a space, and then a 4 digit numeric number in the very next row, with possible other CSR umbers in the next row until a person name begins again.

System A System B
Person Project hours Person/Project hours
Joe Smith CSR 1011 100 Smith Joseph
Pete Jones CSR 1015 200 CSR 1011 - D310 Design 80
Frank Smith CSR 1020 50 CSR 1011 - Code 20
Frank Smith CSR 1015 40 Jones Peter A.
CSR 1015 BA 200
Frank Smith
CSR 1015 Support 20
CSR 1015 BA 20

We would like to get the data in System B like System A For that to happen Person and CSR number would need to separate into 2 columns like system A and add up any duplicate CSR numbers hours directly under that persons name so we are only left with 1 CSR number and its total.

Once this is done, we were thinking the program could concatenate the 3 fields and compare in another column field for matches or not matches.

The data in both systems Person, CSR number and total hours is supposed to be identical and this is what we are trying to ascertain.

Thank You

Office Management
Office Management
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Management: The act or process of organizing, handling, directing or controlling something.
2,061 questions
0 comments No comments
{count} votes