Natch problem (2003 SP3)

[steveh]

Sorry folks, I am having a mare of a day!!

I have a range as shown in the second row of Fig 1, below, (the numbers above have only been typed in by me to corrospond to Fig 2) I then use a Match formula to convert the letter from the range to a number =IF(D7="",0,MATCH(D7,LeaveType,0)) but for some reason the number being returned for most of the items is incorrect, as you can see from Fig 2 it correctly identifies B,b and L,l only. Can anybody suggest why it is not returning the right number for the other lower case letters in the range?

[HansV]

The question is more why B/b and L/l are distinguished - according to the Excel help, "MATCH does not distinguish between uppercase and lowercase letters when matching text values".
Are you sure that there are no extra spaces involved or something like that?

Try

=IF(D7="","",MATCH(D7,LeaveType,0)+EXACT(LOWER(D7),D7))

[agibsonsw]

I thought I might offer an alternative (just for interest..) which doesn't rely on an uppercase character being immediately followed by the lowercase version in the range LeaveType:
=IF(D7="","",SUMPRODUCT(EXACT(D7,LeaveType)*COLUMN(LeaveType)))

This does assume that the range LeaveType begins from column A. If not then amend
COLUMN(LeaveType) to
(COLUMN(LeaveType)-4+1) if the data begins in column D.

[steveh]

The question is more why B/b and L/l are distinguished - according to the Excel help, "MATCH does not distinguish between uppercase and lowercase letters when matching text values".
Are you sure that there are no extra spaces involved or something like that?

Try

=IF(D7="","",MATCH(D7,LeaveType,0)+EXACT(LOWER(D7),D7))

Hi Hans

There are no additional spaces and I have manually checked it and run the ASAP utility to clear leading, trailing and excessive spaces.

I have put in your code though into E7 and when I run the user form it works as expected, please see Fig 1 below. However, if I drag the formula down to the last cell (D781) it removes the data in the Calendar grid that was previousely entered, please see fig 2. If i then try and reinput the same data and/or any other subsequent data nothing happens, please see fig 3.

I know that posting a workbook would be the most useful thing at this point but it is so large and unwieldly I cannot cut it down (yet) into a postable chunk. The formula that sits behind each cell in the Calendar view if it helps is

=IF(WEEKDAY(I$6,2)>5,"",IF(ISNUMBER(MATCH(I$6,PublicHoliday,0)),"PH",INDEX(LeaveType,SUMPRODUCT(($H7=SNames)*(SFrom<=I$6)*(STo>=I$6)*SType))))

PublicHolidays is a Range of, yes, Public Holidays
LeaveType is a range of the leave types
SNames is a range that starts in Column A Row 7 and matches the staff names
SFrom and STo are ranges that start in B7 and C7
SType is a Range that starts in E7 and converts the absence value in D7 to a number to match the LeaveType range

[steveh]

I thought I might offer an alternative (just for interest..) which doesn't rely on an uppercase character being immediately followed by the lowercase version in the range LeaveType:
=IF(D7="","",SUMPRODUCT(EXACT(D7,LeaveType)*COLUMN(LeaveType)))

This does assume that the range LeaveType begins from column A. If not then amend
COLUMN(LeaveType) to
(COLUMN(LeaveType)-4+1) if the data begins in column D.

Hi Andrew

Thank you for your advice but I am not too sure what it is supposed to be matching, if I put the forumala into E7 as is and select Holiday as the leave type it gives me 52 (there can only be 18 matches) if I change it as suggested to -4+1 it gives me -12?

Thanks anyway

[HansV]

Sorry, Steve, without seeing a copy of the workbook I can't offer further help.

[steveh]

Sorry, Steve, without seeing a copy of the workbook I can't offer further help.

Thanks Hans

I thought that would be the case and I have been busy stripping as much as I can away. If I remove all of the Macros (they only affect the user form and the passing of data to the worksheet) and buttons I might be able to get it small enough to Zip. Would that be OK as the data in A:D7 would need to be entered manually to make it work?

[HansV]

It's worth a try. And don't forget that you can zip the workbook!

[steveh]

It's worth a try. And don't forget that you can zip the workbook!

Hi Hans

Removing all of the formatting, months from February to December, taking out some data gathering sheets along with the Macros and Buttons have managed to get me down from 3.5mb to 67kb, phew. It is manual now though for the name, from, to and holiday type column

[HansV]

My guess higher up in this thread was correct. You do have a problem with extra spaces! There is a space after the "B" in BC5 and after the "L" in BE5. This means that MATCH doesn't find these, and skips to the lower case letter next to them. If you remove the trailing space from BC5 and BE5, the lookup will be correct.

The return value "" in the original formula =IF(D7="",0,MATCH(D7,LeaveType,0)) and in the modified version =IF(D7="","",MATCH(D7,LeaveType,0)+EXACT(LOWER(D7),D7)) causes problem with SUMPRODUCT if some rows in SType are empty strings. The following modification works better:

=IF(D7="",0,MATCH(D7,LeaveType,0)+EXACT(LOWER(D7),D7))

[steveh]

My guess higher up in this thread was correct. You do have a problem with extra spaces! There is a space after the "B" in BC5 and after the "L" in BE5. This means that MATCH doesn't find these, and skips to the lower case letter next to them. If you remove the trailing space from BC5 and BE5, the lookup will be correct.

The return value "" in the original formula =IF(D7="",0,MATCH(D7,LeaveType,0)) and in the modified version =IF(D7="","",MATCH(D7,LeaveType,0)+EXACT(LOWER(D7),D7)) causes problem with SUMPRODUCT if some rows in SType are empty strings. The following modification works better:

=IF(D7="",0,MATCH(D7,LeaveType,0)+EXACT(LOWER(D7),D7))

Hi Hans

The revices formula works like a treat, thank you so much. The spaces are strange though because as I said I ran the ASAP utility and I also went to each cell and put the cursor before the letter and backspaced and after the letter and hit delete a few times. That said I have saved so many different versions trying to get it to work in bits and pieces I guess I may have missed the posted version.

Thanks from all for your tireless assistance in 2010 and my New Years resolution will be to try and not bother you so much in 2011!!