Or, index, match statements

I have a workbook with 8 workheets.  The first 7 worksheet are name Mon, 
Tues, Wed, Thur, Fri, Sat, Sun.  These 7 contain the name my employees and 
whether they show up to work in text statement.  They all look like these.

Mon Worksheet

A1                B1
Name
Tom
Jason            not show up to work
Susan            not Show up to work
Ryan
Bill                 not Show up to work

Notice that if any of my 5 employees show up to work, cell in column B is 
leaving blank

Similarly for Tues

A1                B1
Name
Tom
Jason            not show up to work
Susan            not Show up to work
Ryan
Bill

From Wed to Sun are similar.  Now, on the last worksheet in the workbook 
which is the number 8 worksheet, I named it "contract cancel".  I will have 
the names of all my employees just like I did in the 7 weekdays worksheets. 
However, in the B column, I want to have an if statement that check each of 
my employees in 7 days, and if any of them have a statement not show up to 
work, I want it to be display in column B of sheet "contract cancel".  In 
this way, I can say goodbye to to them because they were absence or did not 
show up to work at least one day last week.

contract cancel worksheet

A1                B1
Name
Tom
Jason             not show up to work
Susan            not Show up to work
Ryan
Bill                  not show up to work

I know I must use the "if" statement, then "or" statement, then "index", 
then "match", but I can't figure out how put them together or try to get it 
to work. Can anyboday help? 


0
Unknown
3/31/2008 1:41:00 AM
excel.newusers 15348 articles. 2 followers. Follow

12 Replies
681 Views

Similar Articles

[PageSpeed] 24

One way...

Download and install the free add-in Morefunc.xll from:

http://xcell05.free.fr/english/

It contains a function called THREED that can be used in an array formula** 
like this:

=SUM((THREED(Mon:Sun!A$2:A$10)=A2)*(THREED(Mon:Sun!B$2:B$10)="no show"))

A result that is >0 means that employee had a "no show" on some day of the 
week.

Or, you can write the formula like this to get a result of "no show" :

=IF(SUM((THREED(Mon:Sun!A$2:A$10)=A2)*(THREED(Mon:Sun!B$2:B$10)="no 
show")),"no show","")

** array formulas need to be entered using the key combination of 
CTRL,SHIFT,ENTER (not just ENTER)

Here's a sample file that demonstrates this. The Morefunc add-in functions 
have been embeded in this file. For it to work properly you'll have to 
enable macros. This is for demostration purposes only.

xTHREED.xls

http://www.freefilehosting.net/download/3ed74


-- 
Biff
Microsoft Excel MVP


"Unknown Soldier" <nomailplease.com> wrote in message 
news:47f04114$0$6499$4c368faf@roadrunner.com...
>I have a workbook with 8 workheets.  The first 7 worksheet are name Mon, 
>Tues, Wed, Thur, Fri, Sat, Sun.  These 7 contain the name my employees and 
>whether they show up to work in text statement.  They all look like these.
>
> Mon Worksheet
>
> A1                B1
> Name
> Tom
> Jason            not show up to work
> Susan            not Show up to work
> Ryan
> Bill                 not Show up to work
>
> Notice that if any of my 5 employees show up to work, cell in column B is 
> leaving blank
>
> Similarly for Tues
>
> A1                B1
> Name
> Tom
> Jason            not show up to work
> Susan            not Show up to work
> Ryan
> Bill
>
> From Wed to Sun are similar.  Now, on the last worksheet in the workbook 
> which is the number 8 worksheet, I named it "contract cancel".  I will 
> have the names of all my employees just like I did in the 7 weekdays 
> worksheets. However, in the B column, I want to have an if statement that 
> check each of my employees in 7 days, and if any of them have a statement 
> not show up to work, I want it to be display in column B of sheet 
> "contract cancel".  In this way, I can say goodbye to to them because they 
> were absence or did not show up to work at least one day last week.
>
> contract cancel worksheet
>
> A1                B1
> Name
> Tom
> Jason             not show up to work
> Susan            not Show up to work
> Ryan
> Bill                  not show up to work
>
> I know I must use the "if" statement, then "or" statement, then "index", 
> then "match", but I can't figure out how put them together or try to get 
> it to work. Can anyboday help?
> 


0
biffinpitt (3172)
3/31/2008 3:13:15 AM
MySheets - is a defined name range (eg. c1=mon, c2=tue, c3=wed.....c7=sun)
name in A2:A6
B1:B6 either has a 1 (not show up to work) or emty string (blank cell)

Sheet 8
In B2: 
=IF(SUMPRODUCT(SUMIF(INDIRECT("'"&MySheets&"'!A2:A6"),A2,INDIRECT("'"&MySheets&"'!B2:B6")))>0,"contract cancel","")

ctrl+shift+enter, not just enter
copy down


"Unknown Soldier" wrote:

> I have a workbook with 8 workheets.  The first 7 worksheet are name Mon, 
> Tues, Wed, Thur, Fri, Sat, Sun.  These 7 contain the name my employees and 
> whether they show up to work in text statement.  They all look like these.
> 
> Mon Worksheet
> 
> A1                B1
> Name
> Tom
> Jason            not show up to work
> Susan            not Show up to work
> Ryan
> Bill                 not Show up to work
> 
> Notice that if any of my 5 employees show up to work, cell in column B is 
> leaving blank
> 
> Similarly for Tues
> 
> A1                B1
> Name
> Tom
> Jason            not show up to work
> Susan            not Show up to work
> Ryan
> Bill
> 
> From Wed to Sun are similar.  Now, on the last worksheet in the workbook 
> which is the number 8 worksheet, I named it "contract cancel".  I will have 
> the names of all my employees just like I did in the 7 weekdays worksheets. 
> However, in the B column, I want to have an if statement that check each of 
> my employees in 7 days, and if any of them have a statement not show up to 
> work, I want it to be display in column B of sheet "contract cancel".  In 
> this way, I can say goodbye to to them because they were absence or did not 
> show up to work at least one day last week.
> 
> contract cancel worksheet
> 
> A1                B1
> Name
> Tom
> Jason             not show up to work
> Susan            not Show up to work
> Ryan
> Bill                  not show up to work
> 
> I know I must use the "if" statement, then "or" statement, then "index", 
> then "match", but I can't figure out how put them together or try to get it 
> to work. Can anyboday help? 
> 
> 
> 
0
3/31/2008 3:14:00 AM
1. Think there's no need for you to always cross-post to so many excel 
newsgroups. Going by your subject line & subject, posting only in/to 
microsoft.public.excel.worksheet.functions would suffice.
2. You should always provide feedback to all responders who reply to your 
postings. Don't think you have practised this as yet for your previous 
postings. Go do it now.
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---  


0
demechanik (4694)
3/31/2008 7:20:59 AM
Thank you all for your helps.  However, I have another question.  What if 
the names in "contract cancel" are not in the order as in the weekdays 
worksheet.  Then, I think the formulas would have been different.  How do I 
change the formulas if they names in "contract cancel" are not the order 
with the other worksheets?

Many thanks


"Teethless mama" <Teethlessmama@discussions.microsoft.com> wrote in message 
news:26215D9E-A47D-49A7-AAD5-2C24B9FEDC52@microsoft.com...
> MySheets - is a defined name range (eg. c1=mon, c2=tue, c3=wed.....c7=sun)
> name in A2:A6
> B1:B6 either has a 1 (not show up to work) or emty string (blank cell)
>
> Sheet 8
> In B2:
> =IF(SUMPRODUCT(SUMIF(INDIRECT("'"&MySheets&"'!A2:A6"),A2,INDIRECT("'"&MySheets&"'!B2:B6")))>0,"contract 
> cancel","")
>
> ctrl+shift+enter, not just enter
> copy down
>
>
> "Unknown Soldier" wrote:
>
>> I have a workbook with 8 workheets.  The first 7 worksheet are name Mon,
>> Tues, Wed, Thur, Fri, Sat, Sun.  These 7 contain the name my employees 
>> and
>> whether they show up to work in text statement.  They all look like 
>> these.
>>
>> Mon Worksheet
>>
>> A1                B1
>> Name
>> Tom
>> Jason            not show up to work
>> Susan            not Show up to work
>> Ryan
>> Bill                 not Show up to work
>>
>> Notice that if any of my 5 employees show up to work, cell in column B is
>> leaving blank
>>
>> Similarly for Tues
>>
>> A1                B1
>> Name
>> Tom
>> Jason            not show up to work
>> Susan            not Show up to work
>> Ryan
>> Bill
>>
>> From Wed to Sun are similar.  Now, on the last worksheet in the workbook
>> which is the number 8 worksheet, I named it "contract cancel".  I will 
>> have
>> the names of all my employees just like I did in the 7 weekdays 
>> worksheets.
>> However, in the B column, I want to have an if statement that check each 
>> of
>> my employees in 7 days, and if any of them have a statement not show up 
>> to
>> work, I want it to be display in column B of sheet "contract cancel".  In
>> this way, I can say goodbye to to them because they were absence or did 
>> not
>> show up to work at least one day last week.
>>
>> contract cancel worksheet
>>
>> A1                B1
>> Name
>> Tom
>> Jason             not show up to work
>> Susan            not Show up to work
>> Ryan
>> Bill                  not show up to work
>>
>> I know I must use the "if" statement, then "or" statement, then "index",
>> then "match", but I can't figure out how put them together or try to get 
>> it
>> to work. Can anyboday help?
>>
>>
>> 


0
Unknown
4/1/2008 12:22:36 AM
Thank you Max.  I am sorry, I have been busy lately.

Did you get the news question I post?  Thank for the last help.  It works 
terrifically.

Thank you all for your helps.  However, I have another question.  What if
the names in "contract cancel" are not in the order as in the weekdays
worksheet.  Then, I think the formulas would have been different.  How do I
change the formulas if they names in "contract cancel" are not the order
with the other worksheets

Here is my new question:


"Max" <demechanik@yahoo.com> wrote in message 
news:ufANW%23vkIHA.5956@TK2MSFTNGP03.phx.gbl...
> 1. Think there's no need for you to always cross-post to so many excel 
> newsgroups. Going by your subject line & subject, posting only in/to 
> microsoft.public.excel.worksheet.functions would suffice.
> 2. You should always provide feedback to all responders who reply to your 
> postings. Don't think you have practised this as yet for your previous 
> postings. Go do it now.
> -- 
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> --- 
> 


0
Unknown
4/1/2008 12:28:28 AM
"Unknown Soldier" wrote
> .. Thank for the last help.  It works terrifically.
The closure should have been posted in that thread (not here):
http://tinyurl.com/2c5dla
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---  


0
demechanik (4694)
4/1/2008 1:23:24 AM
Yeah, that project you helped me was great.  Thanks very much.

Can you help me with this one?  Say I have three sheets, 1,2,3.

In sheet1 in have 5 guys and their status.

A1                    B1
Tom                no show
Jason
John                no show
Susan
Crytal

In sheet2 still the same guys, but let say I miss one, and the list in not 
in order like the first sheet.

A1                    B1
Susan            no show
John              no show
Jason
Crytal

In sheet3, I still have all 5 guys, but they not in any order like the first 
two sheet.  I want put a formulas in B1 down to show that if any of the name 
has at least 1 no show will have the display no show display in column B1 
next to their name.  I think I must use the or statement, combine with index 
match, but I can't find out how to get it to work

A1                            B1
Crytal
Tom                        no show
Jason
Susan                       no show
John                        no show


"Max" <demechanik@yahoo.com> wrote in message 
news:OcOdNb5kIHA.6136@TK2MSFTNGP03.phx.gbl...
> "Unknown Soldier" wrote
>> .. Thank for the last help.  It works terrifically.
> The closure should have been posted in that thread (not here):
> http://tinyurl.com/2c5dla
> -- 
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> --- 
> 


0
Unknown
4/1/2008 2:24:06 AM
This might suffice ..
In Sheet3,
Put in B1, copy down
=IF(OR(ISTEXT(INDEX(Sheet1!B:B,MATCH(A1,Sheet1!A:A,0))),ISTEXT(INDEX(Sheet2!B:B,MATCH(A1,Sheet2!A:A,0)))),"no 
show","")
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
--- 
"Unknown Soldier" <nomailplease.com> wrote in message 
news:47f19cc1$0$30687$4c368faf@roadrunner.com...
> Yeah, that project you helped me was great.  Thanks very much.
>
> Can you help me with this one?  Say I have three sheets, 1,2,3.
>
> In sheet1 in have 5 guys and their status.
>
> A1                    B1
> Tom                no show
> Jason
> John                no show
> Susan
> Crytal
>
> In sheet2 still the same guys, but let say I miss one, and the list in not 
> in order like the first sheet.
>
> A1                    B1
> Susan            no show
> John              no show
> Jason
> Crytal
>
> In sheet3, I still have all 5 guys, but they not in any order like the 
> first two sheet.  I want put a formulas in B1 down to show that if any of 
> the name has at least 1 no show will have the display no show display in 
> column B1 next to their name.  I think I must use the or statement, 
> combine with index match, but I can't find out how to get it to work
>
> A1                            B1
> Crytal
> Tom                        no show
> Jason
> Susan                       no show
> John                        no show 


0
demechanik (4694)
4/1/2008 11:37:29 AM
Thanks again max.  I will test it and see how it works.


"Max" <demechanik@yahoo.com> wrote in message 
news:O3AAXy%23kIHA.1184@TK2MSFTNGP04.phx.gbl...
> This might suffice ..
> In Sheet3,
> Put in B1, copy down
> =IF(OR(ISTEXT(INDEX(Sheet1!B:B,MATCH(A1,Sheet1!A:A,0))),ISTEXT(INDEX(Sheet2!B:B,MATCH(A1,Sheet2!A:A,0)))),"no 
> show","")
> -- 
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> --- 
> "Unknown Soldier" <nomailplease.com> wrote in message 
> news:47f19cc1$0$30687$4c368faf@roadrunner.com...
>> Yeah, that project you helped me was great.  Thanks very much.
>>
>> Can you help me with this one?  Say I have three sheets, 1,2,3.
>>
>> In sheet1 in have 5 guys and their status.
>>
>> A1                    B1
>> Tom                no show
>> Jason
>> John                no show
>> Susan
>> Crytal
>>
>> In sheet2 still the same guys, but let say I miss one, and the list in 
>> not in order like the first sheet.
>>
>> A1                    B1
>> Susan            no show
>> John              no show
>> Jason
>> Crytal
>>
>> In sheet3, I still have all 5 guys, but they not in any order like the 
>> first two sheet.  I want put a formulas in B1 down to show that if any of 
>> the name has at least 1 no show will have the display no show display in 
>> column B1 next to their name.  I think I must use the or statement, 
>> combine with index match, but I can't find out how to get it to work
>>
>> A1                            B1
>> Crytal
>> Tom                        no show
>> Jason
>> Susan                       no show
>> John                        no show
>
> 


0
Unknown
4/2/2008 1:14:08 AM
Sure. Pl do so on your actuals, then post back to let us know here.
(it works ok in my tests)
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
--- 
"Unknown Soldier" <nomailplease.com> wrote in message 
news:47f2dddb$0$17358$4c368faf@roadrunner.com...
> Thanks again max.  I will test it and see how it works.


0
demechanik (4694)
4/2/2008 2:34:29 AM
It works, but Max what if I want the OR statement hit a true criteria and I 
want to it to show whattever text in the true statement.  Because I some of 
the "no show" Statement there some text like "no show at 6 am"  "no show at 
1 pm" ect.  How do I do this?  I know we must replace "no show" for true 
statement with something, but I don't what to replace it with.


"Max" <demechanik@yahoo.com> wrote in message 
news:OVWAmnGlIHA.6092@TK2MSFTNGP06.phx.gbl...
> Sure. Pl do so on your actuals, then post back to let us know here.
> (it works ok in my tests)
> -- 
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> --- 
> "Unknown Soldier" <nomailplease.com> wrote in message 
> news:47f2dddb$0$17358$4c368faf@roadrunner.com...
>> Thanks again max.  I will test it and see how it works.
>
> 


0
Unknown
4/2/2008 3:00:07 AM
In Sheet3,
Suggest that you use "plain" index/match in 2 cols to pull out the results 
from Sheet1 & Sheet2. Check through the full range of results that you get. 
Then you can build the necessary error trapping & what-not later (these can 
be formulated in adjacent cols to the right)
In C1: =INDEX(Sheet1!B:B,MATCH(A1,Sheet1!A:A,0))
In D1: =INDEX(Sheet2!B:B,MATCH(A1,Sheet2!A:A,0))
Copy C1:D1 down.  For matched cases, col C returns results from Sheet1's col 
B, col D returns Sheet2's col B. Blank cells will return as zeros. For 
unmatched cases, it will return as: #N/A.
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
--- 
"Unknown Soldier" <nomailplease.com> wrote in message 
news:47f2f6a9$0$11372$4c368faf@roadrunner.com...
> It works, but Max what if I want the OR statement hit a true criteria and 
> I want to it to show whattever text in the true statement.  Because I some 
> of the "no show" Statement there some text like "no show at 6 am"  "no 
> show at 1 pm" ect.  How do I do this?  I know we must replace "no show" 
> for true statement with something, but I don't what to replace it with.


0
demechanik (4694)
4/2/2008 4:08:17 AM
Reply:

Similar Artilces:

Money will not show imported bank statement
I have Money 2005 and I have regularly imported bank statements as .ofx files from Barclays. It has now stopped working. The file downloads OK and when I use file>import and point to the .ofx file it says import complete, but the transactions do not show on the accounts and the items to review shows zero. Any sugestions? Paul In microsoft.public.money, Paul Fearnley wrote: >I have Money 2005 and I have regularly imported bank statements as .ofx >files from Barclays. It has now stopped working. The file downloads OK and >when I use file>import and point to the .ofx file...

Trouble matching overdue payments
This seems to be a constant problem that I would like to find a fix for. Money (2005 s.b.) somehow doesn't match payments from my credit card to reoccurring bills. It then shows that I have an overdue bill even though I paid it. Is there some way to match the overdue bill to a payment I already made? It seems like my only options are "skip occurrence" (which I always do) or "enter into register". Both options suck. Anybody have any ideas? Thanks. ...

If Statement based on Alpha or Numeric
I am trying to do an if statement based on whether a character is numeric or alpha. e.g. I have the following spreadsheets: A 1 01-BHZ 2 01-049 Basically what I am wanting to do is an if statement based on a mid formula. i.e. if mid(A1,4,1) is numeric then "Numeric", if not "Alpha" Is there a formula I can use? Is it a public function with a bit of VBA? Please help Regards, Ryan =IF(ISNUMBER(1*MID(A1,4,1)),"Numeric","Alpha") HTH Jason Atlanta, GA >-----Original Message----- >I am trying to do an if statement based on whether a c...

No banking info in Direct Deposit statements
We would like to know if there is a way to NOT display the employee's banking/account information that is currently being printed in the upper left hand side of the Direct Deposit Statement. Thanks for your help! This can be done easily using report writer. Send email to info@logicalsolutionsllc.com and I can point you in the right direction. >-----Original Message----- >We would like to know if there is a way to NOT display the employee's >banking/account information that is currently being printed in the upper left >hand side of the Direct Deposit Statement. >...

To index or not to index
I have a table of stuff stored in a repository and an attached table of inventory dates, linked one-to-many by an Autonumber ID field. I regularly need to find the oldest or newest inventory dates (or all, in order by date) for each item record in the inventory table, which is normally an automatic case for indexing. But this stuff is not inventoried very often, so far, only two out of over 80,000 records have three records in the inventory table, all others have zero, one or two inventory records. This is NOT going to change. It will likely be decades before there are as many as...

INDEX/MATCH help
I just learned about the INDEX/MATCH function while searching some of the Excel tip pages - and I think I can make good use of it. What I want to do is this: worksheet 1 - is the format of our Income Statement worksheet 2 (titled TB) is our Trial Balance. I want worksheet 1 to read TB (worksheet 2) and pick up the YTD amount for each account #. I tested this out and my formula is working fine. However, sometimes an account # on worksheet 1 doesn't appear on Worksheet 2 because there wasn't any activity. In this case I get a $N/A. I need to edit my formula so that if there ...

Create Clustered index or Covering Index
Which would be better on a 3 column temporary table? A Clustered index or a Covering Index? In this case the uniqueidentifier is always the same and is done this way because there are about 20 procedures that already use this as a static table and I don't want to impace all the procedures with this change. CREATE TABLE #temp (a uniqueidentifier, PID int,CID int) CREATE CLUSTERED INDEX idx on #temp (CID) or CREATE TABLE #temp (a uniqueidentifier, PID int,CID int) CREATE INDEX idx on #temp (CID) INCLUDE (PID,a) Thanks, Tom Tom, Better for what? It depends....

Indexes in tables.
Hi, Can I add index for tables in SQL Manager. For example for SOP30200 index for any field. Will it be a problem with program or not? Any other ways? Thanks, Vitali I believe any future upgrade will blow away your index. -- Charles Allen, MVP "Vitali V" wrote: > Hi, > > Can I add index for tables in SQL Manager. For example for SOP30200 index > for any field. Will it be a problem with program or not? > Any other ways? > > Thanks, > > Vitali > > > Suggest you leave the tables alone. You'll just get into a pickle.. "Char...

Match & Index??
I have the following info in different workbooks. In workbook 1, I have in Columns A,B,C,D: Mark & No. Start End Deal SLGG1234 3/15/2004 6/15/2004 211 SLGG1234 1/1/2004 3/14/2004 111 SLGG1234 6/16/2004 8/15/2004 311 SLGG1255 2/13/2004 8/15/2004 411 In workbook 2, I have Columns A,B, C: Mark & No. Date Deal SLGG1234 3/14/2004 SLGG1234 6/14/2004 In Column C of workbook 2, I want a formula to look at Cols A and B. Compare the info in them to Cols A,B and C in workboo...

text and picture colour matching
Hi there I am producing a document which has a picture and text. I am trying to make them the same colour. I set the text and the picture to the same rgb colour numbers 255 (i.e. royal blue). They look the same on the screen and yet when they print out the picture and the text are very different shades of blue. It's an issue I've had before in pub 2003 but didn't in pub 2000 on the same printer! Any suggestiions greatfully received. Thanks Ken Are you running the most current printer driver? -- JoAnn Paules MVP Microsoft [Publisher] "Ken" <anonymous@discussio...

subtotal, match?
Morning all. I need to do a name check to match with a subtotal function. Is that possible? I was thinking that subtotal(Func_Num,match(....)) might handle it, but I don't see anything that'd allow for that in the list of function numbers for subtotal. My goal is to perform a subtotal type operation, to compare names in a field range from one worksheet to another. I'm already performing a subtotal operation on one field, and wanted to do an if test on the names that show up with each operation. With the false response, I'll then be changing the name eleme...

Why do my data labels do not match source data?
I have created a chart but my data labels do not match the source data. The labels state, Series 1, Series 2, etc. Hi, If the labels read series 1, series 2 etc it would suggest that you have not specified a range in which to pick up the series name from. Right click the chart and pick Source Data... On the Series tab check the Name control contents. Cheers Andy KeithRD48 wrote: > I have created a chart but my data labels do not match the source data. The > labels state, Series 1, Series 2, etc. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

Under HELP, where is the INDEX
In Word 2003, is the INDEX completely gone? I always used it to search topics and definitions of terms. Hi MS decided to change the help system :-( So the Index feature is gone. Maybe you have your old Office CDs still available and can copy the old help files -- Regards Frank Kabel Frankfurt, Germany "Perotin" <Perotin@discussions.microsoft.com> schrieb im Newsbeitrag news:34B1A278-5835-48DD-9A74-1E0E78441485@microsoft.com... > In Word 2003, is the INDEX completely gone? I always used it to search topics > and definitions of terms. ...

index
Hi guys, I have a web site I developed in Publisher Windows 98. We now have a new version. When I work on it and then publish to the web, it does not create a index page, infact it does not see page 1 ! I inserted a blank page as new number 1 and then it made page 2 as the index page ! Appears to work but what am I missing. Also where I have photos and a frame around them, it makes two files/copies of each photo to be sent to web site. Like 505 and 5051. In 5051 the photo is of pour quality but 505 is fine. Have not sent over yet as it appears I have to remove the existing index...

Index of using XPATH?
Hi. I have an xml file in this format: <strings> <string>Item1</string> <string>Item2</string> <string>Item3</string> <string>Item4</string> </string> I'm looking for the best way to search for a specific string, and return the index of its node within the strings element. That is, if someone enters Item3, I need the value 3 (or 2, if the index is 0-based). I have a feeling there's a reasonably simple XPath expression that will get this for me, but I'm trying to avoid iterating through all the elements to fin...

Performance of XPathNavigator.Matches()
I'm experiencing bad performance with certain kinds of match queries. Using a custom XPathNavigator that wraps the usual navigator I can see that many more node visits are performed than should be required. My document looks something like this: <a> <b> <c/> </b> <b/> <b/> <!-- many more "b" elements here --> </a> I have a navigator positioned at the first "b" element. If I run the query nav.Matches( "a/b[c]" ), only three nodes are visited in order to complete the query. However...

Start_Incremental Full Text indexing job on CRM 4.0 server
We have just completed upgrading to CRM 4.0 in production. We upgraded from CRM 3.0 to 4.0. I noticed that there is a job on the SQL server titled "Start_Incremental on <OrganizationName>.ftcat_documentindex.[7.5]" Th job runs every 15 minutes and fails. I have been able to find information on 1.2 and 3.0 installations, but not 4.0. Is this job even needed in 4.0? We are on SQL 2005 so the index should run every 15 minutes anyway?? I have been able to find the catalog and noticed that the name is not ftcat_documentindex as specified in the job's step1. I was able to g...

Contact Index button
We have some problems displaying the Chinese Contact name in the contact list. Any one can help? ...

Help with INDEX/MATCH
I'm trying to make a top 10 but I'm having problems with non unique values. Raw data : Pink 1 Blue 5 Yellow 3 White 6 Purple 4 Brown 2 Red 15 Orange 48 Black 18 Green 20 Beige 22 Violet 56 Gold 12 To get my top 10 I use this, which works fine. =LARGE(totals,1) - through to 10 I then use this to match the text title to the value. =INDEX($B$5:$C$17,MATCH(B23,$C$5:$C$17,0),1) Result : 1st 56 Violet 2nd 48 Orange 3rd 22 Beige 4th 20 Green This all works fine as long as the values are unique, however if I change Gold to 56 I get the following result 1st 56 Violet 2nd 56 Violet 3rd...

Sendkeys statements
Converting Access 97 to 2000, I'm coming across many SENDKEYS ststements which the MS Conversion white paper warns about. How do I convert: Sendkeys "{TAB}" Sendkeys "{ESC 2}" Are these safe to leave in? I have no idea what the second statement above does but its executed when canceling an update of a table. Tab moves to the next field. SetFocus to the desired control, e.g.: Me.[Text99].SetFocus Esc twice undoes the changes to the record. Replace with: If Me.Dirty Then Me.Undo -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access use...

copy data for match word from one file to another file
I need help to merge two files. Any help or macro will be highly appreciated. I have two files. file A and file B. Need to update FILE A from File B. Each file has hundred of rows and about 50 column. If any word or phrase under column "Part" file A matches with any word under column "Part" file b, then copy all the data of matching COLUMN "PART" and corresponding column "NAME" from file B to matching COLUMN "PART" AND corresponding column "NAME" FILE A. See example File A NAME ...

XML indexing ?
Hello, I have an MFC application. In one of my screens the user need to choose a driver from a list of thousands drivers. We cannot use DB, so we use XML only. The drivers files are in the system files every manufacturer has its own directory and every model in the manufacturer directory has its own directory too (many many directories and sub-directories), each model directory has few files (driver files). I need a very fast way to show the user all the manufacturers and when he chooses the manufacturer I need to display all the chosen manufacturer's models (dirs). After the user chooses ...

Composite Index vs Covering Index
I assume that you want to keep the index as small as possible. CREATE TABLE Temp (PK int , FirstName varchar(20), LastName varchar(30),ZipCode varchar(12) ...) If I have a query that is returning First Name, Last Name and ZipCode and need an index set up like LastName, FirstName. But I want to put the Zip Code there as well so I only have to access the index. I could create a covering index: CREATE INDEX idx on Temp (LastName,FirstName) INCLUDE (ZipCode) or a composite index. CREATE INDEX idx on Temp (LastName,FirstName,ZipCode) I assume the covering index would be ...

Using index scan and not index seek
I have the following: CREATE TABLE Task ( ObjectID int PRIMARY KEY CLUSTERED, Version int Description varchar(100), CompanyCustomer int, ) CREATE TABLE File1 ( ObjectID int, Version int Description varchar(100), CompanyCustomer int ) SELECT ObjectID, Version, Description, CompanyCustomer FROM Task INNER JOIN File1 on (Task.ObjectID = File1.ObjectID AND Task.Version = File1.Version) WHERE ISNULL(CompanyCustomer,0) =1) On table Task, I have an index on ObjectID and an index on Version. The same...

Match / Index off two "keys"
I need a way to find a value (could be text) in column C based on th values in column A and column B. Entries in A and entires in B are no unique by themselves; however, by "keying" off columns A and B together only one entry in column C results. Example: A-----B----- D-----X-----Test1 D-----Y-----Test2 F-----Y-----Test3 I've tried various index and match combinations, but haven't figured i out yet. Any ideas? Thanks, J -- carlyma ----------------------------------------------------------------------- carlyman's Profile: http://www.excelforum.com/member.php?action=...