Thanks for looking at this Dick. I would love to get this figured out, both
for myself, and for a colleague who asked me for help with this thing (this
is part three of three; the first two issues have been resolved). So, Im
helping out a colleague who has a pretty large workbook that does all kinds
of things. All of the columns have data; I just picked out the ones that are
problematic...thats why it probably looks nutty...it actually does make
sense. It makes perfect sense to me, and I came up with an Access solution
in about 5 minutes, but Im stumped as to how to do it in Excel. Its really
a relationship problem; Access is perfect for this kind of thing. I use both
Access and Excel; everyone else in my office uses Excel only. I wouldnt
expect them to learn Access and this is really the last piece of the puzzle,
so I wouldnt advocate switching to Access at this point (everything else is
in Excel and works fine). Sorry to circumvent the issue, just trying to give
some background.
Anyway, we are looking at many different advertising agencies on Sheet2 (as
well as many other things but right now I am just interested in the data in
columnA). In Sheet1, we are looking at names of sales reps, which are in
columnA, and advertising agencies, which are in ColumnE (some match the
agencies in Sheet2 and some dont). In Sheet3, we have the sales reps, which
are in ColumnC (some of the names match those in Sheet1 and some dont). The
objective is to fine the matching advertising agencies in Sheet2 and Sheet1,
match these to the sales rep names on Sheet1, and then match these sales rep
names with those sales rep names on Sheet3, and when there are matching sales
rep names, sum all values in ColumnV of Sheet3. The sales reps names appear
only once on Sheet3, and this is a major problem (thats why I cant use the
built in Excel functions).
Essentially, I want to look at a name on Sheet2, such as €˜BarNone (there
will be lots of advertiser names), see if there is a match on Sheet1. I can
tell that there are matches; two advertisers named €˜BarNone on Sheet2 and
four advertisers named €˜BarNone on Sheet1. On Sheet1, I can tell that Tommy
is the sales rep that handles the €˜BarNone account. I then look at ColumnV
of Sheet1, and see that Tommy has 1200 + 1800 + 1500 + 100 in revenue from
€˜BarNone so I want to sum this and place the result in Sheet3, ColumnM. On
Sheet3, cell M2, I would like to see 4600.
Does that make sense? I believe this is possible, Ive never done it in
Excel though, and I dont even really know where to begin. If anyone can see
the logic, as I do, and give me a UDF for this thing, I would be most
appreciative!!
Thanks,
Ryan---
--
RyGuy
"Dick Kusleika" wrote:
On Sun, 15 Feb 2009 13:48:01 -0800, ryguy7272
wrote:
Basically, I am trying to come up with a way to match Account Names in
Column A of Sheet2 with Account Names in Column E of Sheet1. If there is a
match there, then match names in Column C of Sheet1 with names in Column C of
Sheet3, and sum the values, in Column V of Sheet1, that match this name!
RyGuy: I don't get it. Walk me through the Jeff, Andy, and Timmy
calculations. I don't see how Sheet2 relates to the other sheets. There is
a "match" for every agency, in that every agency on sheet2 exists on sheet1,
but so what? I think if you step through the logic on those three reps, we
can get this figured out.
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com