Need help/advise on automatically pulling specific data from a spreadsheet...

[Khurgan]

Hello,

I have this spreadsheet at work that I am attempting to add some macros/formulas to so that it does some processes automatically.

I have come up with a few solutions, but I am stuck on one problem at the moment.

I have figured out how to automatically pull out the initials of the salesperson (in the example below, JLS), and have it show a list of all the salespeople, while removing duplicates/blanks. Example on the right of the table below.

I have also figured out how to have it automatically pull out and total all of the deduction amounts, (which are the red numbers in the amount column). Example of this is also shown to the right on the table below.

What I need help with, is this: I want to have it automatically pull out and total the deductions (red numbers) that are associated with each salesperson, and show this next to each salespersons initials in the list to the right. I can't just have it total the amount column, as that would give me an incorrect total. I need just the deductions, for each salesperson.

The problem I am having is that, in the spreadsheet, rows 2 & 3 are merged in all columns, except the narrative column. Added to this is the fact that the deduction amount is not in the same row as the salespersons initials.

A side note on this: The data changes constantly. This spreadsheet is used for each invoice. It is cleared out after each invoice is pasted in and a copy sent to the appropriate salespeople. So the data in the automatically generated lists will be changing every time the sheet is cleared and the information from a new invoice is added. Not sure if that will affect how this problem is solved, but I wanted as much information available as possible.

I am currently stumped...but still trying to figure this out.

Any suggestions would be greatly appreciated.

Date

Inv ID

Task

Initials

Task

Narrative

Code

Units

Rate

Amount

Total Deductions ($):

($14.10)

Salespeople

1

9/5/17

12345678

L123

JLS

Fee

This is what was done for the customer.

U

0.40

$235.00

$94.00

Total Number of Deductions:

1

JLS

2

(Type of Deduction)

GSP

3

This is where the customer states why they feel a deduction is in order.

($14.10)

FDR

<tbody>
</tbody>

Thank you,

 

[FDibbins]

A few things to note...
1. avoid using merged cells if at all possible, they cause all sorts of problems with formulas (as you have found)
2. read 1, above
3. avoid using merg....ok ok, you get the point lol
4. If you have to merge cells or work with merged cells, remember that the ONLY cell that contains any data is the top-left cell. So in the merged cell A1 to E5, only A1 has anything in it, all others are empty/blank
5. with 4 (above) in mind, if your deduction is in the same row as the TOP row of the merged cells, you can still reference it relatively easily. If it is in the row below, we can work around that, too

 

[Khurgan]

Thank you FDibbins.

Unfortunately, the information I am copying into the spreadsheet is preformatted, and it contains the merged cells...so there is not much I can do about that.

The deduction is IN the merged cells. In this example, the deduction ($14.10), is in the merged cells of J2 and J3 (the 1 2 3 on the far left are not in cells, as they are just there for reference in the post).

The problem I am having is pulling out both the Initials, and the deductions assigned to those initials.

 

[Khurgan]

Hello All,

I am still struggling with this one...if anyone has any suggestions...

Thank you in advance.
Khurgan