Fancy Lookup

[Chad Portman]

First off this question is not for the feint of heart. I know my way around
Excel pretty good but what I want to do is but very tricky and possibly
impossible but please someone prove me wrong. Here is the deal I have this
table of data from A1:F1000 that I need to retrieve data from. Col A has a
list of Emp. ID’s, Col B is their Dept, Col C is the Date, Col D is hours
worked, Col E is hours scheduled, and Col F is Schedule adherence. This table
includes data for a whole month and is sorted in ascending order of Emp.
ID’s. As such every one of the Emp. ID’s will appear multiple times one for
each day they worked. What I need is a formula to look down Col A for Emp. ID
number 1234 and once it finds those rows which include it I need it to look
down Col C for the Date I tell it to look for (this will be done with another
formula but I got that part no worries). At this point it should be narrowed
down to just one row and from that row I need the formula to report to me
just Col D. So as I said before this is not for the feint of heart. If you
can help me that would be great however with the data being what it is I can
not provide an example I can just explain as best I can. Also I have limited
access to this site so if you could please reply to me at my e-mail which is
(e-mail address removed) and I will reply from there if you want to
leave a post on here for others to view that would be great as I am sure I am
not the only one that needs help with this and doubt I am the first to ask.

 

[Bernd P]

Hello Chad,

I suggest to consider a pivot table.

Or one of my UDFs Pfreq, Sfreq, Pstat:
http://www.sulprobil.com/html/listfreq.html

I would not suggest to use SUMPRODUCT:
http://www.sulprobil.com/html/sumproduct.html

Regards,
Bernd

 

[Héctor Miguel]

hi, Bernd !

I suggest to consider a pivot table.

Or one of my UDFs Pfreq, Sfreq, Pstat:
http://www.sulprobil.com/html/listfreq.html

q1: how these UDF's helps the users to know in advance (i.e.)
how many rows/columns *will be* necessary to *fit* the results ?
(given an scenario of *unknown* possible elements/combinations/...)

I would not suggest to use SUMPRODUCT:
http://www.sulprobil.com/html/sumproduct.html

q2: *what if* you do not need to fill "all the way" (column C) with COUNTIF's functions ?

(just curious...)
TIA,
hector.

 

[Bernd P]

Hello Hector,

...
q1: how these UDF's helps the users to know in advance (i.e.)
      how many rows/columns *will be* necessary to *fit* the results ?
      (given an scenario of *unknown* possible elements/combinations/...)
...

a1: Two passes: Call my functions twice via a surrounding sub. First
run will give you the number of necessary rows. Prepare your output
area then. Second pass will return the data, fitting into the prepared
area.

...
q2: *what if* you do not need to fill "all the way" (column C) with COUNTIF's functions ?
...

a2: If your problem is less complex, the necessary solution might be
less sophisticated. Sumproduct might be a solution sometimes - its
just my thesis that it is being offered too often (and that my UDF's
fill a gap between sumproducts and pivot tables)...decide yourself

Regards,
Bernd

 

[Héctor Miguel]

hi, Bernd !

above all, I really appreciate your time in answer my curiosity
and your UDF's are very useful for themselves
thank you very much

_____

a1: Two passes: Call my functions twice via a surrounding sub.
First run will give you the number of necessary rows.
Prepare your output area then.
Second pass will return the data, fitting into the prepared area.

(I guess...) doing "two passes...", while conditions (elements/combinations/...) varies "on-the-fly"
might cause a change to your assumptions on these UDF's performance: O(n^2) versus O(n) ?

_____

a2: If your problem is less complex, the necessary solution might be less sophisticated.
Sumproduct might be a solution sometimes - its just my thesis that it is being offered too often
(and that my UDF's fill a gap between sumproducts and pivot tables)...decide yourself

playing with same data-table as in your example (column A)...
I used a single array-range to "catch" the unique elements (perhaps another if you need a sorted output)
and then used sumif(... or countif(...

kind regards,
hector.

 

[Bernd P]

Hello Andy,

My thesis is that there is a gap between a reasonable usage of
SUMPRODUCT and of pivot tables which my UDF's fill.

These UDF's are special purpose UDF's. It's not necessary to call them
twice. But: If you need to know their exact number of output rows (to
prepare that output area, for example), you will either need to adjust
these functions or to wrap some functionality around them. I do not
see this as a disadvantage.

If you need to do 30 aggregations (for lets say 30 different names)
with several criteria and if you use SUMPRODUCT, you are kind of
forced to maintain that name list manually (or to increase the
complexity of your worksheet drastically).

That's what you do not need to do with Sfreq (or with my other UDF's).
Its done automatically.

Regards,
Bernd