Prev: VBA DataEntryMode bug?
Next: Scripting.FileSystemObject stopped working
From: Fonebone on 22 Jan 2010 05:50
Using: Excel 2007

I want to document Windows Group Policy settings side by side on a single
sheet. To accomplish this task I want to return the row number of the current
sheet where the data of the row that has an exact match. The data I want to
lookup is in three colums.

Example:
Column B | Column C | Column D
-------------------------------------------------------------------------------------------
Policy | Control Panel | Load a specific visual style
Policy | Desktop | Do not add shares
Policy | Desktop | Hide Network Locations icon
Policy | Desktop | Prohibit User from manually redirecting
Policy | Desktop | Remove the Desktop Cleanup Wizard

What I want to query: I want the row number to be returned where I've got an
exact match on a single row within the active worksheet.

Example: Give me the row number where "Column B = Policy" and "Column C =
Desktop" and "Column D = Do not add shares".

I do make a remark that the columns are NOT sorted.

My Question: How can I query this information from a active worksheet and
return the row number where the match is found.

I hope you can help me
Thx in advance. Greetings Fred
From: Bernard Liengme on 22 Jan 2010 09:02
I put you data on a worksheet beginning in B6
In B1 I entered the text for the B column match - "Policy"
In C2 I entered "Desktop" and in D2, "Prohibit User from manually
redirecting"
In E6 I entered the formula =IF(AND(B6=$B$1,C6=$C$1,D6=$D$1),"X","") and
copied it down the column by double clicking E6's fill handle.
In A1 I enter the formula =MATCH("X",E:E,0)
This returned the value 8 as that is the row where the three items match.
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Fonebone" <Fonebone(a)discussions.microsoft.com> wrote in message
news:D43AB40C-BE56-468B-B7A9-8CF47B15BC8E(a)microsoft.com...
> Using: Excel 2007
>
> I want to document Windows Group Policy settings side by side on a single
> sheet. To accomplish this task I want to return the row number of the
> current
> sheet where the data of the row that has an exact match. The data I want
> to
> lookup is in three colums.
>
> Example:
> Column B | Column C | Column D
> -------------------------------------------------------------------------------------------
> Policy | Control Panel | Load a specific visual style
> Policy | Desktop | Do not add shares
> Policy | Desktop | Hide Network Locations icon
> Policy | Desktop | Prohibit User from manually redirecting
> Policy | Desktop | Remove the Desktop Cleanup Wizard
>
> What I want to query: I want the row number to be returned where I've got
> an
> exact match on a single row within the active worksheet.
>
> Example: Give me the row number where "Column B = Policy" and "Column C =
> Desktop" and "Column D = Do not add shares".
>
> I do make a remark that the columns are NOT sorted.
>
> My Question: How can I query this information from a active worksheet and
> return the row number where the match is found.
>
> I hope you can help me
> Thx in advance. Greetings Fred

 | 
Pages: 1
Prev: VBA DataEntryMode bug?
Next: Scripting.FileSystemObject stopped working