VB help on work project

[thelazyturttle]

Hello Everyone,

I'll let you know I'm in accounting. I don't know a significant amount about VB but i've done a few projects that have at least given me a bit to work with. I need help with 1 part of my current project. Every Quarter I receive dozens of spreadsheets that I need to analyze.

• Each spreadsheet has the following data points shown below.
• Each spreadsheet has a random amount of people. Some 2-3 people, some with 1-2 thousand people.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]LName
[/TD]
[TD]FName
[/TD]
[TD]MI
[/TD]
[TD]SSN
[/TD]
[TD]PSDate
[/TD]
[TD]PPDate
[/TD]
[TD]PD
[/TD]
[TD]$A[/TD]
[TD]$B[/TD]
[TD]$C[/TD]
[TD]$D
[/TD]
[TD]E
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
The ideal outcome would be as follows.

1. Combine all spreadsheets into one workbook. (I have a working macro that combines them all by making each file it's own sheet) So my current result is one excel workbook, with roughly 28 sheets.
2. I need a new sheet that has 1 row dedicated to every account possible within every single one of those 28 sheets. I was thinking maybe pull the first 4 columns of every sheet and add them into a new sheet. Then remove duplicates? Since I have the SSN, I always have a unique identifier?
3. Then I need to have the sum of $A, $B and then E. So my result would look like this.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]SSN
[/TD]
[TD]LName
[/TD]
[TD]FName
[/TD]
[TD]$A
[/TD]
[TD]$B
[/TD]
[TD]E
[/TD]
[/TR]
</tbody>[/TABLE]

1. Basically, if Bob shows up in 3 sheets with 500 in $A, 50 for $B and 20 for E. It would look like this.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Smith
[/TD]
[TD]Bob[/TD]
[TD]111-11-1111
[/TD]
[TD]1500
[/TD]
[TD]150
[/TD]
[TD]60
[/TD]
[/TR]
</tbody>[/TABLE]

Sorry if this is a lot. I'm trying to learn VB but I can't seem to combine the sheets into one while simultaneously removing duplicates and summing the values of each sheet's amounts for those specific columns.

Thank you for any help,
Turttle

 

[thelazyturttle]

Sorry for the formatting, I'm typing this up at work and it's chaotic today.

Thanks in advance for dealing with this cluster of a question