B
Bishop
First, I would just like to say you guys are a priceless resource. I was
handed a fairly large project at work that's written in VBA, which I do not
know (I'm teaching myself while doing this project, reading several books on
it, etc) and EVERY question I've brought here someone knows the answer to.
So officially, Thank you very much for sharing the wealth.
Now to my question. I currently have a VBA program that takes a spreadsheet
with a list of X reps (each having 5-300 adjustments), sorts out the 5
highest transactions for each rep, formats it accordingly and puts it in a
spreadsheet that I built. I need to refine this search a bit and make some
modifications to the original list of X reps. Consider the following sample
data:
Name Acct# Date Amt
John 123 5-Jan 250
John 456 20-Jan 150
John 456 20-Jan 150
John 456 20-Jan 150
John 789 22-Jan 100
John 321 15-Jan 50
John 321 14-Jan 75
John 321 14-Jan 100
John 654 12-Jan 50
John 987 3-Jan 225
Mike 654 5-Jan 30
Mike 654 5-Jan 150
Mike 654 6-Jan 200
Mike 987 9-Jan 75
Mike 456 18-Jan 100
Mike 789 23-Jan 250
Mike 987 9-Jan 100
Mike 456 24-Jan 50
Mike 321 31-Jan 200
Mike 135 31-Jan 50
Mike 135 31-Jan 100
Mike 987 9-Jan 100
Mike 987 9-Jan 100
Mike 987 8-Jan 75
Under my current sorting the top 5 transactions for these 2 reps would be:
John 123 5-Jan 250
John 987 3-Jan 225
John 456 20-Jan 150
John 456 20-Jan 150
John 456 20-Jan 150
Mike 789 23-Jan 250
Mike 654 6-Jan 200
Mike 321 31-Jan 200
Mike 654 5-Jan 150
Mike 456 18-Jan 100
This is presenting 2 problems. The first problem is that it lists duplicate
accounts which is redundant. John, for example, is showing 3 adjustments for
account 456 on 20-Jan. This is really part of the second problem which is
I'm not really getting the 5 highest transactions. For accounts with
multiple adjustments on the same day that counts as a SINGLE adjustment all
totalled. John, for example, issued a total of $450 to account 456. In the
case of John it just happen to work out that account 456 shows up in his top
5. But because it's taking up 2 additional spots in the top 5 I'm not seeing
2 more accounts I could be reviewing. And in the case of Mike he issued a
total of $375 to account 987, which is the highest transaction, but I would
never see it because it's comprised of a bunch of smaller adjustments that
end up at the bottom of the sort. This is what I NEED my program to do: For
each rep, any account that has more than one transaction on the same day
needs to show up as a single transaction for a total of the individual
transactions. And any duplicate adjustments on the same day need to be
deleted. So, for example, the 3 adjustments John made to account 456 on
20-Jan total $450. I want that to show as 1 adjustment for the total amount:
John 456 20-Jan 450
And the 4 adjustments Mike made to account 987 on 9-Jan total $375. I need
that to show as a single adjustment for the total amount:
Mike 987 9-Jan 375
And any additional adjustments for the same account on the same day need to
be deleted. If you take the same data sample from up top and sort it the way
I've just described this is what it should look like:
John 456 20-Jan 450
John 123 5-Jan 250
John 987 3-Jan 225
John 321 14-Jan 175
John 789 22-Jan 100
John 654 12-Jan 50
John 321 15-Jan 50
Mike 987 9-Jan 375
Mike 789 23-Jan 250
Mike 654 6-Jan 200
Mike 321 31-Jan 200
Mike 654 5-Jan 180
Mike 135 31-Jan 150
Mike 456 18-Jan 100
Mike 987 8-Jan 75
Mike 456 24-Jan 50
And the top 5 transactions for each rep would be:
John 456 20-Jan 450
John 123 5-Jan 250
John 987 3-Jan 225
John 321 14-Jan 175
John 789 22-Jan 100
Mike 987 9-Jan 375
Mike 789 23-Jan 250
Mike 654 6-Jan 200
Mike 321 31-Jan 200
Mike 654 5-Jan 180
Which is clearly very different from the original top 5. Sorry this is so
wordy but I wanted to make sure I explained it clearly. How can I accomplish
this?
handed a fairly large project at work that's written in VBA, which I do not
know (I'm teaching myself while doing this project, reading several books on
it, etc) and EVERY question I've brought here someone knows the answer to.
So officially, Thank you very much for sharing the wealth.
Now to my question. I currently have a VBA program that takes a spreadsheet
with a list of X reps (each having 5-300 adjustments), sorts out the 5
highest transactions for each rep, formats it accordingly and puts it in a
spreadsheet that I built. I need to refine this search a bit and make some
modifications to the original list of X reps. Consider the following sample
data:
Name Acct# Date Amt
John 123 5-Jan 250
John 456 20-Jan 150
John 456 20-Jan 150
John 456 20-Jan 150
John 789 22-Jan 100
John 321 15-Jan 50
John 321 14-Jan 75
John 321 14-Jan 100
John 654 12-Jan 50
John 987 3-Jan 225
Mike 654 5-Jan 30
Mike 654 5-Jan 150
Mike 654 6-Jan 200
Mike 987 9-Jan 75
Mike 456 18-Jan 100
Mike 789 23-Jan 250
Mike 987 9-Jan 100
Mike 456 24-Jan 50
Mike 321 31-Jan 200
Mike 135 31-Jan 50
Mike 135 31-Jan 100
Mike 987 9-Jan 100
Mike 987 9-Jan 100
Mike 987 8-Jan 75
Under my current sorting the top 5 transactions for these 2 reps would be:
John 123 5-Jan 250
John 987 3-Jan 225
John 456 20-Jan 150
John 456 20-Jan 150
John 456 20-Jan 150
Mike 789 23-Jan 250
Mike 654 6-Jan 200
Mike 321 31-Jan 200
Mike 654 5-Jan 150
Mike 456 18-Jan 100
This is presenting 2 problems. The first problem is that it lists duplicate
accounts which is redundant. John, for example, is showing 3 adjustments for
account 456 on 20-Jan. This is really part of the second problem which is
I'm not really getting the 5 highest transactions. For accounts with
multiple adjustments on the same day that counts as a SINGLE adjustment all
totalled. John, for example, issued a total of $450 to account 456. In the
case of John it just happen to work out that account 456 shows up in his top
5. But because it's taking up 2 additional spots in the top 5 I'm not seeing
2 more accounts I could be reviewing. And in the case of Mike he issued a
total of $375 to account 987, which is the highest transaction, but I would
never see it because it's comprised of a bunch of smaller adjustments that
end up at the bottom of the sort. This is what I NEED my program to do: For
each rep, any account that has more than one transaction on the same day
needs to show up as a single transaction for a total of the individual
transactions. And any duplicate adjustments on the same day need to be
deleted. So, for example, the 3 adjustments John made to account 456 on
20-Jan total $450. I want that to show as 1 adjustment for the total amount:
John 456 20-Jan 450
And the 4 adjustments Mike made to account 987 on 9-Jan total $375. I need
that to show as a single adjustment for the total amount:
Mike 987 9-Jan 375
And any additional adjustments for the same account on the same day need to
be deleted. If you take the same data sample from up top and sort it the way
I've just described this is what it should look like:
John 456 20-Jan 450
John 123 5-Jan 250
John 987 3-Jan 225
John 321 14-Jan 175
John 789 22-Jan 100
John 654 12-Jan 50
John 321 15-Jan 50
Mike 987 9-Jan 375
Mike 789 23-Jan 250
Mike 654 6-Jan 200
Mike 321 31-Jan 200
Mike 654 5-Jan 180
Mike 135 31-Jan 150
Mike 456 18-Jan 100
Mike 987 8-Jan 75
Mike 456 24-Jan 50
And the top 5 transactions for each rep would be:
John 456 20-Jan 450
John 123 5-Jan 250
John 987 3-Jan 225
John 321 14-Jan 175
John 789 22-Jan 100
Mike 987 9-Jan 375
Mike 789 23-Jan 250
Mike 654 6-Jan 200
Mike 321 31-Jan 200
Mike 654 5-Jan 180
Which is clearly very different from the original top 5. Sorry this is so
wordy but I wanted to make sure I explained it clearly. How can I accomplish
this?