Looking to match up duplicate names and data and maybe even subtract the values

[epark1]

Hi there,

I have recently started to maintain a spreadsheet at work that carries caseloads. I've been using conditional formatting and a few other formulas to make it more accurate and easier to maintain. I've had to do a lot of searching to find the info I've used so far, but I can't seem to find anything for the next step.

I have a list of names with corresponding caseloads. I then have a short list of duplicates, each with a value that I need to subtract from the caseload. As of right now I am manually lining up the duplicates and subtracting. Best case scenario would be a way to find the duplicates and subtract, giving me one column with the final numbers. Worst case scenario, a way to move the data so that the duplicates and their values line up with the names and values in the full caseload report.

Basically it looks like this:
Teacher 1 10 Teacher 2 5
Teacher 2 8 Teacher 3 7
Teacher 3 12
Teacher 4 6

Best case scenario result would be:
Teacher 1 10
Teacher 2 3
Teacher 3 5
Teacher 4 6

If that's too complicated then even this would be a great help:
Teacher 1 10
Teacher 2 8 Teacher 2 5
Teacher 3 12 Teacher 3 7
Teacher 4 6

Sorry for being so long, I'm just trying to include all pertinent data and make sure I state clearly what I hope to be able to do.

TIA
E =0)

 

[skorpionkz]

How your data is presented?

Excel 2010
	A	B	C	D
1	Teacher 1	10	Teacher 2	5
2	Teacher 2	8	Teacher 3	7
3	Teacher 3	12
4	Teacher 4	6
Sheet1

OR

Excel 2010
	A
1	Teacher 1 10 Teacher 2 5
2	Teacher 2 8 Teacher 3 7
3	Teacher 3 12
4	Teacher 4 6
Sheet2

 

[epark1]

Sorry, it's like sheet 1. Names and values in different columns. Didn't take the time to get a real sample from my sheet. Probably should have.

How your data is presented?

Excel 2010

	A	B	C	D
1	Teacher 1	10	Teacher 2	5
2	Teacher 2	8	Teacher 3	7
3	Teacher 3	12
4	Teacher 4	6

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

OR

Excel 2010

	A
1	Teacher 1 10 Teacher 2 5
2	Teacher 2 8 Teacher 3 7
3	Teacher 3 12
4	Teacher 4 6

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

 

[epark1]

Oh, for goodness sakes, I've got it figured out. I was able to use a pivot table to filter the data to be what I wanted.

Thanks so much to any and all who took a minute to look at this.

 

[skorpionkz]

I finished the VBA code for this but never mind then and good luck

EDIT:
Just in case you would want to try this solution

Sub FormatData()

Dim wsR As Worksheet, wsC As Worksheet
Dim lrow As Long, lrow_wsR As Long
Dim iLoad1 As Integer, iLoad2 As Integer
Dim cel As Range

Set wsC = ActiveSheet
Set wsR = Sheets("Results")

lrow = Cells(1000, 1).End(xlUp).Row
Range(Cells(1, 1), Cells(lrow, 1)).Copy Destination:=wsR.Cells(1, 1)
lrow = Cells(1000, 3).End(xlUp).Row
lrow_wsR = wsR.Cells(1000, 1).End(xlUp).Row + 1
Range(Cells(2, 3), Cells(lrow, 3)).Copy Destination:=wsR.Cells(lrow_wsR, 1)

lrow_wsR = wsR.Cells(1000, 1).End(xlUp).Row + 1
wsR.Range(wsR.Cells(1, 1), wsR.Cells(lrow_wsR, 1)).RemoveDuplicates Columns:=1, Header:=xlYes

lrow_wsR = wsR.Cells(1000, 1).End(xlUp).Row

For Each cel In wsR.Range(wsR.Cells(2, 1), wsR.Cells(lrow_wsR, 1))
    On Error Resume Next
    iLoad1 = Empty
    iLoad2 = Empty
    iLoad1 = Application.WorksheetFunction.VLookup(cel.Value, Range(Columns(1), Columns(2)), 2, 0)
    iLoad2 = Application.WorksheetFunction.VLookup(cel.Value, Range(Columns(3), Columns(4)), 2, 0)
    On Error GoTo 0
    cel.Offset(0, 1) = iLoad1 - iLoad2
Next cel

End Sub

 

[epark1]

Thank you so much. I'm just starting to learn about VBA codes, so I'm going to keep this and use it.

Have a great day!