Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 83
Default hate to double post but have not really got a good answer, more lo

I have quesiton that has has not really been answered.
I think the quesiton lies more into how to structure my table array.

I have a table array that is based on an agent #. from there I use vlookup
formulas
to popluate all the various info that goes along with the agent #.
OK here is the problem, for accounting purposes when the agent # is paid, an
office code (that is part of the table array) get s paid as well.
Ok so... here is the problem, we change people to differeent office codes at
times.

If I had the array stuctured were the agent # was duplicated for example
177721 BOB Jones 401895 1-1-2008
177721 BOB Jones 401912 6-1-2009

Ok there is production date column in data sheet.
How can I stucture this were based off the agent # and the date
it will produce the correct office code.

Please advise

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default hate to double post but have not really got a good answer, more lo

Try this array formula** :

G1 = 177721
H1 = 6-1-2009

=INDEX(D1:D10,MATCH(1,(A1:A10=G1)*(E1:E10=H1),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Returns: 401912

--
Biff
Microsoft Excel MVP


" wrote in message
...
I have quesiton that has has not really been answered.
I think the quesiton lies more into how to structure my table array.

I have a table array that is based on an agent #. from there I use
vlookup
formulas
to popluate all the various info that goes along with the agent #.
OK here is the problem, for accounting purposes when the agent # is paid,
an
office code (that is part of the table array) get s paid as well.
Ok so... here is the problem, we change people to differeent office codes
at
times.

If I had the array stuctured were the agent # was duplicated for example
177721 BOB Jones 401895 1-1-2008
177721 BOB Jones 401912 6-1-2009

Ok there is production date column in data sheet.
How can I stucture this were based off the agent # and the date
it will produce the correct office code.

Please advise



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default hate to double post but have not really got a good answer, more lo

You **might** be able to use this normally entered version **if** the office
code is always a numeric value.

=SUMPRODUCT(--(A1:A10=G1),--(E1:E10=H1),D1:D10)

The other array version is more flexible as it handles any data type.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try this array formula** :

G1 = 177721
H1 = 6-1-2009

=INDEX(D1:D10,MATCH(1,(A1:A10=G1)*(E1:E10=H1),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT key then hit ENTER.

Returns: 401912

--
Biff
Microsoft Excel MVP


" wrote in message
...
I have quesiton that has has not really been answered.
I think the quesiton lies more into how to structure my table array.

I have a table array that is based on an agent #. from there I use
vlookup
formulas
to popluate all the various info that goes along with the agent #.
OK here is the problem, for accounting purposes when the agent # is
paid, an
office code (that is part of the table array) get s paid as well.
Ok so... here is the problem, we change people to differeent office codes
at
times.

If I had the array stuctured were the agent # was duplicated for example
177721 BOB Jones 401895 1-1-2008
177721 BOB Jones 401912 6-1-2009

Ok there is production date column in data sheet.
How can I stucture this were based off the agent # and the date
it will produce the correct office code.

Please advise





  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 83
Default hate to double post but have not really got a good answer, mor

The H1 column dates are random. I tried using a formula very
similar to this and would not reconize random dates.
I have been trying to do some type of if( < less than function, but
cannot get it to jive.
Will this formula MATCH the date to date and return a values or will
will it work like if the date is the office code date it will produce the
correct
response, please advsie
ie
what would happen is the date were 2-28-2008
would it produce the code in associated with that random date in time

177721 BOB Jones 401895 1-1-2008
177721 BOB Jones 401912 6-1-2008
"T. Valko" wrote:

Try this array formula** :

G1 = 177721
H1 = 6-1-2009

=INDEX(D1:D10,MATCH(1,(A1:A10=G1)*(E1:E10=H1),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Returns: 401912

--
Biff
Microsoft Excel MVP


" wrote in message
...
I have quesiton that has has not really been answered.
I think the quesiton lies more into how to structure my table array.

I have a table array that is based on an agent #. from there I use
vlookup
formulas
to popluate all the various info that goes along with the agent #.
OK here is the problem, for accounting purposes when the agent # is paid,
an
office code (that is part of the table array) get s paid as well.
Ok so... here is the problem, we change people to differeent office codes
at
times.

If I had the array stuctured were the agent # was duplicated for example
177721 BOB Jones 401895 1-1-2008
177721 BOB Jones 401912 6-1-2009

Ok there is production date column in data sheet.
How can I stucture this were based off the agent # and the date
it will produce the correct office code.

Please advise




  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,346
Default hate to double post but have not really got a good answer, mor

Hi,

You problem is going to be that you really aren't looking at a specific date
but a range of dates, so you are going to need to create another table which
associates dates and agent #. This will only work if this isn't completely
random.

Suppose you set up a table with Agent #'s in column L, Start Date in M and
End Date in N and Office Code in O
=SUMPRODUCT(--(A1=L1:L10),--(B1=M1:M10),--(B1<=N1:N10),O1:O10)

Where the agent # is in A1 and the date in B1
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


" wrote:

The H1 column dates are random. I tried using a formula very
similar to this and would not reconize random dates.
I have been trying to do some type of if( < less than function, but
cannot get it to jive.
Will this formula MATCH the date to date and return a values or will
will it work like if the date is the office code date it will produce the
correct
response, please advsie
ie
what would happen is the date were 2-28-2008
would it produce the code in associated with that random date in time

177721 BOB Jones 401895 1-1-2008
177721 BOB Jones 401912 6-1-2008
"T. Valko" wrote:

Try this array formula** :

G1 = 177721
H1 = 6-1-2009

=INDEX(D1:D10,MATCH(1,(A1:A10=G1)*(E1:E10=H1),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Returns: 401912

--
Biff
Microsoft Excel MVP


" wrote in message
...
I have quesiton that has has not really been answered.
I think the quesiton lies more into how to structure my table array.

I have a table array that is based on an agent #. from there I use
vlookup
formulas
to popluate all the various info that goes along with the agent #.
OK here is the problem, for accounting purposes when the agent # is paid,
an
office code (that is part of the table array) get s paid as well.
Ok so... here is the problem, we change people to differeent office codes
at
times.

If I had the array stuctured were the agent # was duplicated for example
177721 BOB Jones 401895 1-1-2008
177721 BOB Jones 401912 6-1-2009

Ok there is production date column in data sheet.
How can I stucture this were based off the agent # and the date
it will produce the correct office code.

Please advise






  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 83
Default hate to double post but have not really got a good answer, mor


so would I have a two entry on each agent #, and sort by agent #
or does sorting even matter

ie
agent # start end code
1234 1-1-2008 5-31-2008 4567
1234 6-1-2008 12-31-2008 8910
and then the next agetn and so on,
And then to make it even more complex, do I need to have an ending
date???? or should I carry the entry way out and the adjust it to suit.

Please advise




"Shane Devenshire" wrote:

Hi,

You problem is going to be that you really aren't looking at a specific date
but a range of dates, so you are going to need to create another table which
associates dates and agent #. This will only work if this isn't completely
random.

Suppose you set up a table with Agent #'s in column L, Start Date in M and
End Date in N and Office Code in O
=SUMPRODUCT(--(A1=L1:L10),--(B1=M1:M10),--(B1<=N1:N10),O1:O10)

Where the agent # is in A1 and the date in B1
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


" wrote:

The H1 column dates are random. I tried using a formula very
similar to this and would not reconize random dates.
I have been trying to do some type of if( < less than function, but
cannot get it to jive.
Will this formula MATCH the date to date and return a values or will
will it work like if the date is the office code date it will produce the
correct
response, please advsie
ie
what would happen is the date were 2-28-2008
would it produce the code in associated with that random date in time

177721 BOB Jones 401895 1-1-2008
177721 BOB Jones 401912 6-1-2008
"T. Valko" wrote:

Try this array formula** :

G1 = 177721
H1 = 6-1-2009

=INDEX(D1:D10,MATCH(1,(A1:A10=G1)*(E1:E10=H1),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Returns: 401912

--
Biff
Microsoft Excel MVP


" wrote in message
...
I have quesiton that has has not really been answered.
I think the quesiton lies more into how to structure my table array.

I have a table array that is based on an agent #. from there I use
vlookup
formulas
to popluate all the various info that goes along with the agent #.
OK here is the problem, for accounting purposes when the agent # is paid,
an
office code (that is part of the table array) get s paid as well.
Ok so... here is the problem, we change people to differeent office codes
at
times.

If I had the array stuctured were the agent # was duplicated for example
177721 BOB Jones 401895 1-1-2008
177721 BOB Jones 401912 6-1-2009

Ok there is production date column in data sheet.
How can I stucture this were based off the agent # and the date
it will produce the correct office code.

Please advise




  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default hate to double post but have not really got a good answer, mor

Can you better explain what you date matching criteria is?

--
Biff
Microsoft Excel MVP


" wrote in message
...

so would I have a two entry on each agent #, and sort by agent #
or does sorting even matter

ie
agent # start end code
1234 1-1-2008 5-31-2008 4567
1234 6-1-2008 12-31-2008 8910
and then the next agetn and so on,
And then to make it even more complex, do I need to have an ending
date???? or should I carry the entry way out and the adjust it to suit.

Please advise




"Shane Devenshire" wrote:

Hi,

You problem is going to be that you really aren't looking at a specific
date
but a range of dates, so you are going to need to create another table
which
associates dates and agent #. This will only work if this isn't
completely
random.

Suppose you set up a table with Agent #'s in column L, Start Date in M
and
End Date in N and Office Code in O
=SUMPRODUCT(--(A1=L1:L10),--(B1=M1:M10),--(B1<=N1:N10),O1:O10)

Where the agent # is in A1 and the date in B1
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


" wrote:

The H1 column dates are random. I tried using a formula very
similar to this and would not reconize random dates.
I have been trying to do some type of if( < less than function, but
cannot get it to jive.
Will this formula MATCH the date to date and return a values or will
will it work like if the date is the office code date it will produce
the
correct
response, please advsie
ie
what would happen is the date were 2-28-2008
would it produce the code in associated with that random date in time

177721 BOB Jones 401895 1-1-2008
177721 BOB Jones 401912 6-1-2008
"T. Valko" wrote:

Try this array formula** :

G1 = 177721
H1 = 6-1-2009

=INDEX(D1:D10,MATCH(1,(A1:A10=G1)*(E1:E10=H1),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and
the SHIFT
key then hit ENTER.

Returns: 401912

--
Biff
Microsoft Excel MVP


" wrote in
message
...
I have quesiton that has has not really been answered.
I think the quesiton lies more into how to structure my table
array.

I have a table array that is based on an agent #. from there I use
vlookup
formulas
to popluate all the various info that goes along with the agent #.
OK here is the problem, for accounting purposes when the agent #
is paid,
an
office code (that is part of the table array) get s paid as well.
Ok so... here is the problem, we change people to differeent office
codes
at
times.

If I had the array stuctured were the agent # was duplicated for
example
177721 BOB Jones 401895 1-1-2008
177721 BOB Jones 401912 6-1-2009

Ok there is production date column in data sheet.
How can I stucture this were based off the agent # and the date
it will produce the correct office code.

Please advise






  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 83
Default hate to double post but have not really got a good answer, mor

OK the basic way it works is the agent # gets paid for some form
of production, when the agent # get paid a portion of the payment goes
to the office code,
The agent # will never change, the office code is more like a manager
hierachy.
So lets say BOB started on
JAN 1 2008, his agent # is 1234 and is under office code 4567
so if he did somehting that would generate lets say $100.00
$50.00 would go to the agent # and 50.00 would go to the office code.

So on 5-15 we decide to close the office code 4567 and start a new profit
code 8910

OK so to make it easy.
The column would be
a: Agent #
b: writting date
c: office code.

I need the correct office code to fall the in the range of dates the agent #
acctually
dated the produciton.
was profiled to that code
agent # start end code
1234 1-1-2008 5-31-2008 4567
1234 6-1-2008 12-31-2008 8910

so If the writting date were 2-25, or 5-09 the office code produced would
be 4567
if the writting date were 6-09 or 9-27 the off code would procude 8910
SO the writting date would random, the agent # will stay unique

"T. Valko" wrote:

Can you better explain what you date matching criteria is?

--
Biff
Microsoft Excel MVP


" wrote in message
...

so would I have a two entry on each agent #, and sort by agent #
or does sorting even matter

ie
agent # start end code
1234 1-1-2008 5-31-2008 4567
1234 6-1-2008 12-31-2008 8910
and then the next agetn and so on,
And then to make it even more complex, do I need to have an ending
date???? or should I carry the entry way out and the adjust it to suit.

Please advise




"Shane Devenshire" wrote:

Hi,

You problem is going to be that you really aren't looking at a specific
date
but a range of dates, so you are going to need to create another table
which
associates dates and agent #. This will only work if this isn't
completely
random.

Suppose you set up a table with Agent #'s in column L, Start Date in M
and
End Date in N and Office Code in O
=SUMPRODUCT(--(A1=L1:L10),--(B1=M1:M10),--(B1<=N1:N10),O1:O10)

Where the agent # is in A1 and the date in B1
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


" wrote:

The H1 column dates are random. I tried using a formula very
similar to this and would not reconize random dates.
I have been trying to do some type of if( < less than function, but
cannot get it to jive.
Will this formula MATCH the date to date and return a values or will
will it work like if the date is the office code date it will produce
the
correct
response, please advsie
ie
what would happen is the date were 2-28-2008
would it produce the code in associated with that random date in time

177721 BOB Jones 401895 1-1-2008
177721 BOB Jones 401912 6-1-2008
"T. Valko" wrote:

Try this array formula** :

G1 = 177721
H1 = 6-1-2009

=INDEX(D1:D10,MATCH(1,(A1:A10=G1)*(E1:E10=H1),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and
the SHIFT
key then hit ENTER.

Returns: 401912

--
Biff
Microsoft Excel MVP


" wrote in
message
...
I have quesiton that has has not really been answered.
I think the quesiton lies more into how to structure my table
array.

I have a table array that is based on an agent #. from there I use
vlookup
formulas
to popluate all the various info that goes along with the agent #.
OK here is the problem, for accounting purposes when the agent #
is paid,
an
office code (that is part of the table array) get s paid as well.
Ok so... here is the problem, we change people to differeent office
codes
at
times.

If I had the array stuctured were the agent # was duplicated for
example
177721 BOB Jones 401895 1-1-2008
177721 BOB Jones 401912 6-1-2009

Ok there is production date column in data sheet.
How can I stucture this were based off the agent # and the date
it will produce the correct office code.

Please advise







  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default hate to double post but have not really got a good answer, mor

Ok, if you have a date range then:

G1 = agent lookup number
H1 = start date
I1 = end date

Array entered** :

=INDEX(D1:D10,MATCH(1,(A1:A10=G1)*(B1:B10<=H1)*(C1 :C10=I1),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


" wrote in message
...
OK the basic way it works is the agent # gets paid for some form
of production, when the agent # get paid a portion of the payment goes
to the office code,
The agent # will never change, the office code is more like a manager
hierachy.
So lets say BOB started on
JAN 1 2008, his agent # is 1234 and is under office code 4567
so if he did somehting that would generate lets say $100.00
$50.00 would go to the agent # and 50.00 would go to the office code.

So on 5-15 we decide to close the office code 4567 and start a new profit
code 8910

OK so to make it easy.
The column would be
a: Agent #
b: writting date
c: office code.

I need the correct office code to fall the in the range of dates the agent
#
acctually
dated the produciton.
was profiled to that code
agent # start end code
1234 1-1-2008 5-31-2008 4567
1234 6-1-2008 12-31-2008 8910

so If the writting date were 2-25, or 5-09 the office code produced would
be 4567
if the writting date were 6-09 or 9-27 the off code would procude 8910
SO the writting date would random, the agent # will stay unique

"T. Valko" wrote:

Can you better explain what you date matching criteria is?

--
Biff
Microsoft Excel MVP


" wrote in
message
...

so would I have a two entry on each agent #, and sort by agent #
or does sorting even matter

ie
agent # start end code
1234 1-1-2008 5-31-2008 4567
1234 6-1-2008 12-31-2008 8910
and then the next agetn and so on,
And then to make it even more complex, do I need to have an ending
date???? or should I carry the entry way out and the adjust it to suit.

Please advise




"Shane Devenshire" wrote:

Hi,

You problem is going to be that you really aren't looking at a
specific
date
but a range of dates, so you are going to need to create another table
which
associates dates and agent #. This will only work if this isn't
completely
random.

Suppose you set up a table with Agent #'s in column L, Start Date in M
and
End Date in N and Office Code in O
=SUMPRODUCT(--(A1=L1:L10),--(B1=M1:M10),--(B1<=N1:N10),O1:O10)

Where the agent # is in A1 and the date in B1
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


" wrote:

The H1 column dates are random. I tried using a formula very
similar to this and would not reconize random dates.
I have been trying to do some type of if( < less than function, but
cannot get it to jive.
Will this formula MATCH the date to date and return a values or will
will it work like if the date is the office code date it will
produce
the
correct
response, please advsie
ie
what would happen is the date were 2-28-2008
would it produce the code in associated with that random date in
time

177721 BOB Jones 401895 1-1-2008
177721 BOB Jones 401912 6-1-2008
"T. Valko" wrote:

Try this array formula** :

G1 = 177721
H1 = 6-1-2009

=INDEX(D1:D10,MATCH(1,(A1:A10=G1)*(E1:E10=H1),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and
the SHIFT
key then hit ENTER.

Returns: 401912

--
Biff
Microsoft Excel MVP


" wrote in
message
...
I have quesiton that has has not really been answered.
I think the quesiton lies more into how to structure my table
array.

I have a table array that is based on an agent #. from there I
use
vlookup
formulas
to popluate all the various info that goes along with the agent
#.
OK here is the problem, for accounting purposes when the agent
#
is paid,
an
office code (that is part of the table array) get s paid as
well.
Ok so... here is the problem, we change people to differeent
office
codes
at
times.

If I had the array stuctured were the agent # was duplicated for
example
177721 BOB Jones 401895
1-1-2008
177721 BOB Jones 401912
6-1-2009

Ok there is production date column in data sheet.
How can I stucture this were based off the agent # and the date
it will produce the correct office code.

Please advise









Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Good post Darknight7 Excel Worksheet Functions 1 May 31st 08 01:54 PM
How much I hate the ribbon ion Excel Discussion (Misc queries) 5 November 28th 07 03:23 PM
Worksheet looks good in print, not so good on-screen Betsy Excel Discussion (Misc queries) 6 February 9th 07 03:16 AM
Can someone answer to my post please BristolBreeze Excel Discussion (Misc queries) 1 March 23rd 06 01:09 PM
Why is the answer to my formula double what it should be? Hayley B Excel Discussion (Misc queries) 1 July 19th 05 11:05 AM


All times are GMT +1. The time now is 12:58 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"