Fancy Lookup

  • Thread starter Thread starter Chad Portman
  • Start date Start date
C

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.
 
hi, Bernd !
I suggest to consider a pivot table.

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

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
Click to expand...

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

(just curious...)
TIA,
hector.
 
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/...)
...
Click to expand...

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 ?
...
Click to expand...

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
 
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.
Click to expand...

(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 :-)
Click to expand...

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.
 
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
 
Back
Top