Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #108130 > unrolled thread

Logical based Horizontal Lookup

Back to article view | Back to microsoft.public.excel.programming

Contents

  Logical based Horizontal Lookup dvpetta@gmail.com - 2015-10-28 07:41 -0700
    Re: Logical based Horizontal Lookup isabelle <i@v.invalid> - 2015-10-28 13:12 -0400
      Re: Logical based Horizontal Lookup isabelle <i@v.invalid> - 2015-10-28 13:21 -0400
        Re: Logical based Horizontal Lookup dvpetta@gmail.com - 2015-10-28 10:29 -0700
    Re: Logical based Horizontal Lookup Claus Busch <claus_busch@t-online.de> - 2015-10-28 18:46 +0100
      Re: Logical based Horizontal Lookup isabelle <i@v.invalid> - 2015-10-28 14:18 -0400
        Re: Logical based Horizontal Lookup GS <gs@v.invalid> - 2015-10-28 14:39 -0400
          Re: Logical based Horizontal Lookup Claus Busch <claus_busch@t-online.de> - 2015-10-28 21:23 +0100
            Re: Logical based Horizontal Lookup GS <gs@v.invalid> - 2015-10-28 17:38 -0400
      Re: Logical based Horizontal Lookup Claus Busch <claus_busch@t-online.de> - 2015-10-28 20:51 +0100
        Re: Logical based Horizontal Lookup dvpetta@gmail.com - 2015-10-29 11:19 -0700
          Re: Logical based Horizontal Lookup Claus Busch <claus_busch@t-online.de> - 2015-10-29 19:33 +0100
            Re: Logical based Horizontal Lookup dvpetta@gmail.com - 2015-10-29 11:42 -0700
      Re: Logical based Horizontal Lookup "Robert Crandal" <noreply2me@yahoo.com> - 2015-11-12 00:14 -0700
        Re: Logical based Horizontal Lookup Claus Busch <claus_busch@t-online.de> - 2015-11-12 08:18 +0100
    Re: Logical based Horizontal Lookup dvpetta@gmail.com - 2015-10-29 08:57 -0700
    Re: Logical based Horizontal Lookup dvpetta@gmail.com - 2016-03-15 10:08 -0700
      Re: Logical based Horizontal Lookup Claus Busch <claus_busch@t-online.de> - 2016-03-16 11:12 +0100
        Re: Logical based Horizontal Lookup Daniel Petta <dvpetta@gmail.com> - 2016-03-16 04:44 -0700
          Re: Logical based Horizontal Lookup Claus Busch <claus_busch@t-online.de> - 2016-03-16 13:12 +0100
            Re: Logical based Horizontal Lookup Daniel Petta <dvpetta@gmail.com> - 2016-03-16 06:15 -0700
              Re: Logical based Horizontal Lookup Claus Busch <claus_busch@t-online.de> - 2016-03-16 14:25 +0100
                Re: Logical based Horizontal Lookup Daniel Petta <dvpetta@gmail.com> - 2016-03-31 09:56 -0700
                  Re: Logical based Horizontal Lookup Claus Busch <claus_busch@t-online.de> - 2016-03-31 19:45 +0200
                    Re: Logical based Horizontal Lookup Claus Busch <claus_busch@t-online.de> - 2016-03-31 20:11 +0200
                      Re: Logical based Horizontal Lookup Claus Busch <claus_busch@t-online.de> - 2016-03-31 20:12 +0200
                        Re: Logical based Horizontal Lookup Daniel Petta <dvpetta@gmail.com> - 2016-04-01 09:14 -0700
                        Re: Logical based Horizontal Lookup Daniel Petta <dvpetta@gmail.com> - 2016-04-12 04:22 -0700
                          Re: Logical based Horizontal Lookup Claus Busch <claus_busch@t-online.de> - 2016-04-12 14:18 +0200

Page 1 of 2  [1] 2  Next page →

#108130 — Logical based Horizontal Lookup

Hello,

Have a bit of an odd but challenging request for the group here!

The objective for this project is to take a details work schedule which shows tasks by half hour increment and automatically calculate a summary schedule for the entire week showing the employee's start and end time.

I have a simple template for one day of the week which I've uploaded for you to understand the results I'm looking for:

https://www.dropbox.com/s/gsfp5tt3uwoh620/schedule%20template.xlsx?dl=0

In rows 1 - 3, this is the detailed schedule that would be input by the user.  They simply enter letters based on the tasks required at the point during their shift.  Row 1 shows the time in half hour increments so when Employee 1 has an X in column W, their shift will start at 10:00am.  For the same employee, the last X they have for that day is in column AN which means that their shift will end at 19:00 (or 7pm).

For Employee 2, the same rules apply but this employee's tasks start in column T which would signify 8:30am and their last X comes in column AK which ends in 17:30.

So this top section is fine and somewhat simple.  

The part I need help with is the bottom section which is the summary, rows 6-10.

I want to automate this section based on the detailed schedule being entered in the section above.  Ideally, via formulas this summary section would automatically update based on the schedule being entered.  The end result would be summary in one cell for the day that shows their start and end time.  I've entered examples of this in the spreadsheet and highlighted this in orange.

I hope that all makes sense.  

I should also mention that I cannot use macros here. I've tried to do some sort of lookup using HLOOKUP and INDEX but unable to figure out how to automate the process.

Thanks in advance for your help!

[toc] | [next] | [standalone]

#108131

hi,

it is an array formula to insert with CTRL+Shift+Enter

=INDEX($A$1:$AX$1,IFERROR(SMALL(IF($C2:$AX2="x",COLUMN($C2:$AX2)),1),""))&":"&IF(ISEVEN(IFERROR(SMALL(IF($C2:$AX2="x",COLUMN($C2:$AX2)),1),"")),"30","00")&"
-
"&INDEX($A$1:$AX$1,IFERROR(LARGE(IF($C2:$AX2="x",COLUMN($C2:$AX2)),1)+1,""))&":"&IF(ISEVEN(IFERROR(LARGE(IF($C2:$AX2="x",COLUMN($C2:$AX2)),1),"")),"00","30")

isabelle

Le 2015-10-28 10:41, dvpetta@gmail.com a écrit :
> Hello,
>
> Have a bit of an odd but challenging request for the group here!
>
> The objective for this project is to take a details work schedule which shows
> tasks by half hour increment and automatically calculate a summary schedule
> for the entire week showing the employee's start and end time.
>
> I have a simple template for one day of the week which I've uploaded for you
> to understand the results I'm looking for:
>
> https://www.dropbox.com/s/gsfp5tt3uwoh620/schedule%20template.xlsx?dl=0
>
> In rows 1 - 3, this is the detailed schedule that would be input by the user.
> They simply enter letters based on the tasks required at the point during
> their shift.  Row 1 shows the time in half hour increments so when Employee 1
> has an X in column W, their shift will start at 10:00am.  For the same
> employee, the last X they have for that day is in column AN which means that
> their shift will end at 19:00 (or 7pm).
>
> For Employee 2, the same rules apply but this employee's tasks start in
> column T which would signify 8:30am and their last X comes in column AK which
> ends in 17:30.
>
> So this top section is fine and somewhat simple.
>
> The part I need help with is the bottom section which is the summary, rows
> 6-10.
>
> I want to automate this section based on the detailed schedule being entered
> in the section above.  Ideally, via formulas this summary section would
> automatically update based on the schedule being entered.  The end result
> would be summary in one cell for the day that shows their start and end time.
> I've entered examples of this in the spreadsheet and highlighted this in
> orange.
>
> I hope that all makes sense.
>
> I should also mention that I cannot use macros here. I've tried to do some
> sort of lookup using HLOOKUP and INDEX but unable to figure out how to
> automate the process.
>
> Thanks in advance for your help!
>

[toc] | [prev] | [next] | [standalone]

#108132

please look here:

http://www.cjoint.com/c/EJCruf1wCXa

isabelle

Le 2015-10-28 13:12, isabelle a écrit :
> hi,
>
> it is an array formula to insert with CTRL+Shift+Enter
>
> =INDEX($A$1:$AX$1,IFERROR(SMALL(IF($C2:$AX2="x",COLUMN($C2:$AX2)),1),""))&":"&IF(ISEVEN(IFERROR(SMALL(IF($C2:$AX2="x",COLUMN($C2:$AX2)),1),"")),"30","00")&"
>
> -
> "&INDEX($A$1:$AX$1,IFERROR(LARGE(IF($C2:$AX2="x",COLUMN($C2:$AX2)),1)+1,""))&":"&IF(ISEVEN(IFERROR(LARGE(IF($C2:$AX2="x",COLUMN($C2:$AX2)),1),"")),"00","30")
>
>
> isabelle
>
> Le 2015-10-28 10:41, dvpetta@gmail.com a écrit :
>> Hello,
>>
>> Have a bit of an odd but challenging request for the group here!
>>
>> The objective for this project is to take a details work schedule which shows
>> tasks by half hour increment and automatically calculate a summary schedule
>> for the entire week showing the employee's start and end time.
>>
>> I have a simple template for one day of the week which I've uploaded for you
>> to understand the results I'm looking for:
>>
>> https://www.dropbox.com/s/gsfp5tt3uwoh620/schedule%20template.xlsx?dl=0
>>
>> In rows 1 - 3, this is the detailed schedule that would be input by the user.
>> They simply enter letters based on the tasks required at the point during
>> their shift.  Row 1 shows the time in half hour increments so when Employee 1
>> has an X in column W, their shift will start at 10:00am.  For the same
>> employee, the last X they have for that day is in column AN which means that
>> their shift will end at 19:00 (or 7pm).
>>
>> For Employee 2, the same rules apply but this employee's tasks start in
>> column T which would signify 8:30am and their last X comes in column AK which
>> ends in 17:30.
>>
>> So this top section is fine and somewhat simple.
>>
>> The part I need help with is the bottom section which is the summary, rows
>> 6-10.
>>
>> I want to automate this section based on the detailed schedule being entered
>> in the section above.  Ideally, via formulas this summary section would
>> automatically update based on the schedule being entered.  The end result
>> would be summary in one cell for the day that shows their start and end time.
>> I've entered examples of this in the spreadsheet and highlighted this in
>> orange.
>>
>> I hope that all makes sense.
>>
>> I should also mention that I cannot use macros here. I've tried to do some
>> sort of lookup using HLOOKUP and INDEX but unable to figure out how to
>> automate the process.
>>
>> Thanks in advance for your help!
>>

[toc] | [prev] | [next] | [standalone]

#108133

Wow!

Thank you so much, this is perfect.

Mercí!

[toc] | [prev] | [next] | [standalone]

#108134

Hi,

Am Wed, 28 Oct 2015 07:41:44 -0700 (PDT) schrieb dvpetta@gmail.com:

> The part I need help with is the bottom section which is the summary, rows 6-10.

a little bit shorter and without array:
=TEXT((VERGLEICH("x";2:2;0)-3)/2/24;"hh:mm")&" - "&TEXT((VERGLEICH("";A2:AX2;-1)-2)/2/24;"hh:mm")


Regards
Claus B.
-- 
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[toc] | [prev] | [next] | [standalone]

#108135

MATCH("",A2:AX2,-1)
it's brilliant
chapeau!
isabelle

Le 2015-10-28 13:46, Claus Busch a écrit :
> Hi,
>
> Am Wed, 28 Oct 2015 07:41:44 -0700 (PDT) schrieb dvpetta@gmail.com:
>
>> The part I need help with is the bottom section which is the summary, rows 6-10.
>
> a little bit shorter and without array:
> =TEXT((VERGLEICH("x";2:2;0)-3)/2/24;"hh:mm")&" - "&TEXT((VERGLEICH("";A2:AX2;-1)-2)/2/24;"hh:mm")
>
>
> Regards
> Claus B.
>

[toc] | [prev] | [next] | [standalone]

#108136

> it's brilliant
> chapeau!

That's why Claus IS the *formula wizard*!<g>

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#108138

Hi Isabelle, hi Garry,

Am Wed, 28 Oct 2015 14:39:38 -0400 schrieb GS:

>> it's brilliant
>> chapeau!
> 
> That's why Claus IS the *formula wizard*!<g>

thank you for the compliments but I am only a crazy user. First I read
the question. Then I look into the table to find structures or sequences
I could use to keep the formula short and simple.


Regards
Claus B.
-- 
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[toc] | [prev] | [next] | [standalone]

#108139

> Hi Isabelle, hi Garry,
>
> Am Wed, 28 Oct 2015 14:39:38 -0400 schrieb GS:
>
>>> it's brilliant
>>> chapeau!
>> 
>> That's why Claus IS the *formula wizard*!<g>
>
> thank you for the compliments but I am only a crazy user. First I 
> read the question. Then I look into the table to find structures or 
> sequences I could use to keep the formula short and simple.
>
>
> Regards
> Claus B.

Well.., that's what makes you the formula wizard! I like short formulas 
because they're like the one-liner code snippets I like to use...

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#108137

Hi again,

Am Wed, 28 Oct 2015 18:46:19 +0100 schrieb Claus Busch:

> a little bit shorter and without array:
> =TEXT((VERGLEICH("x";2:2;0)-3)/2/24;"hh:mm")&" - "&TEXT((VERGLEICH("";A2:AX2;-1)-2)/2/24;"hh:mm")

sorry, I forgot to translate the formula:
=TEXT((MATCH("x",2:2,0)-3)/2/24,"hh:mm")&" - "&TEXT((MATCH("",A2:AX2,-1)-2)/2/24,"hh:mm")


Regards
Claus B.
-- 
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[toc] | [prev] | [next] | [standalone]

#108141

On Wednesday, 28 October 2015 19:51:33 UTC, Claus Busch  wrote:
> Hi again,
> 
> Am Wed, 28 Oct 2015 18:46:19 +0100 schrieb Claus Busch:
> 
> > a little bit shorter and without array:
> > =TEXT((VERGLEICH("x";2:2;0)-3)/2/24;"hh:mm")&" - "&TEXT((VERGLEICH("";A2:AX2;-1)-2)/2/24;"hh:mm")
> 
> sorry, I forgot to translate the formula:
> =TEXT((MATCH("x",2:2,0)-3)/2/24,"hh:mm")&" - "&TEXT((MATCH("",A2:AX2,-1)-2)/2/24,"hh:mm")
> 
> 
> Regards
> Claus B.
> -- 
> Vista Ultimate / Windows7
> Office 2007 Ultimate / 2010 Professional

Hello,

Unfortunately I left out a key part of the spreadsheet so I'm stumped again.

I forgot to mention that the detailed schedule will have different tasks than just "X".  Here is the list of options that they can enter into their schedule:

Task types
X
S
R
H
I
G
C
A
M
T
O
B

I've tried to change the match formula to allow for these different options but I can't seem to get it right.  Doesn't look like the MATCH formula likes AND or OR to be included.

I've updated the example on the dropsite with all of the types of task codes that would be used:

https://www.dropbox.com/s/gsfp5tt3uwoh620/schedule%20template.xlsx?dl=0

Thank you in advance!

Daniel

[toc] | [prev] | [next] | [standalone]

#108142

Hi Daniel,

Am Thu, 29 Oct 2015 11:19:20 -0700 (PDT) schrieb dvpetta@gmail.com:

> I forgot to mention that the detailed schedule will have different tasks than just "X".  Here is the list of options that they can enter into their schedule:

please download you workbook ("schedule template") from:
https://onedrive.live.com/redir?resid=9378aab6121822a3!326&authkey=!AApmk7UTBA7IuAM&ithint=folder%2cxlsm 


Regards
Claus B.
-- 
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[toc] | [prev] | [next] | [standalone]

#108143

Thank you Klaus!

[toc] | [prev] | [next] | [standalone]

#108149

"Claus Busch" <claus_busch@t-online.de> wrote:
part I need help with is the bottom section which is the summary, rows 6-10.
>
> a little bit shorter and without array:
> =TEXT((VERGLEICH("x";2:2;0)-3)/2/24;"hh:mm")&" - 
> "&TEXT((VERGLEICH("";A2:AX2;-1)-2)/2/24;"hh:mm")

Hi Claus.  I'm just curious, what the heck is that VERGLEICH function?
My version of Excel does not seem to have that function.

Thanks.

[toc] | [prev] | [next] | [standalone]

#108150

Hi Robert,

Am Thu, 12 Nov 2015 00:14:35 -0700 schrieb Robert Crandal:

> Hi Claus.  I'm just curious, what the heck is that VERGLEICH function?
> My version of Excel does not seem to have that function.

I am working with a german language version and forgot to translate the
formula.
VERGLEICH = MATCH


Regards
Claus B.
-- 
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[toc] | [prev] | [next] | [standalone]

#108140

Thanks so much everyone!

All of these formulas work perfectly for what I need.

You are all, indeed formulas wizards!

Cheers

[toc] | [prev] | [next] | [standalone]

#108641

Hello Everyone,

I'm back with a refinement on my original request.  The function has been working great for me but know I want to limit which hour types will be picked up into this calculation.

Here is Klaus' formula that works fine (file called schedule template):
https://www.dropbox.com/s/bofbx5ph556j33t/schedule%20template%20with%20formula.xlsx?dl=0

The formula in question is:
=TEXT((MATCH("*",C2:AZ2,0)-1)/48,"hh:mm")&" - "&TEXT((MATCH("",A2:AY2,-1)-2)/48,"hh:mm")

The key bit that I'd like to change is the MATCH section of the formula which essentially says when you find any character in this array begin the start of the shift.  This is done via "*" in MATCH.  

What I'd like to do is have MATCH not use "*" but only look for the following characters X, S, R, H, G, C, A, M, T, B.

I've tried to insert AND and OR but to no success.

Can anyone help?

[toc] | [prev] | [next] | [standalone]

#108642

Hi David,

Am Tue, 15 Mar 2016 10:08:03 -0700 (PDT) schrieb dvpetta@gmail.com:

> The key bit that I'd like to change is the MATCH section of the formula which essentially says when you find any character in this array begin the start of the shift.  This is done via "*" in MATCH.  
> 
> What I'd like to do is have MATCH not use "*" but only look for the following characters X, S, R, H, G, C, A, M, T, B.

try it with an UDF.
Please look here:
https://onedrive.live.com/redir?resid=9378aab6121822a3!326&authkey=!AApmk7UTBA7IuAM&ithint=folder%2cxlsm
for "schedule template" and download the file because macros are
disabled in OneDrive.


Regards
Claus B.
-- 
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[toc] | [prev] | [next] | [standalone]

#108643

Thank you Claus.

Is there any way to take the last part of the formula and have it use the same logic defined in the UDF?  

The formula you've given me is:
=stime(C64:AX64)&" - "&TEXT((MATCH("",A64:AY64,-1)-2)/48,"hh:mm")

The current formula works perfect for the "start time" of the schedule [stime(C64:AX64)]but the last part of the formula [TEXT((MATCH("",A64:AY64,-1)-2)/48,"hh:mm")] accounts for all hour types [""].  I'd like to make it such that the "end time" or last part of this formula


Here is an example of a shift which has hour types which would be include (like X) and then excluded (like I):
https://www.dropbox.com/s/ui4dtkwprv1gmd6/planner%20031616.xlsm?dl=0

I've tried to set up another UDF (eTime) but I obviously don't know what I'm doing as I'm getting error messages!

Thanks again for your help.

[toc] | [prev] | [next] | [standalone]

#108644

Hi Daniel,

Am Wed, 16 Mar 2016 04:44:59 -0700 (PDT) schrieb Daniel Petta:

> Is there any way to take the last part of the formula and have it use the same logic defined in the UDF?  

I put sTime and eTime in one UDF. Now you have to call the function in
the sheet with:
=myTime(C2:AX2)
Have a look:
https://onedrive.live.com/redir?resid=9378aab6121822a3!326&authkey=!AApmk7UTBA7IuAM&ithint=folder%2cxlsm
for "PLANNER 031616" and download the file.


Regards
Claus B.
-- 
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[toc] | [prev] | [next] | [standalone]

Page 1 of 2  [1] 2  Next page →

Back to top | Article view | microsoft.public.excel.programming

csiph-web