Twist on VLookup Function

The easiest way is to use Advanced Filter. Say your data looks like this (the first 3 columns):

Cells E1:G2 are my "Criteria Range". Go to Data-Filter-Advanced Filter. Select Copy to Another Location, select your list range, select your criteria range (again, E1:G2 in the example above), then select the upper-left cell where you want your data. Click OK and it will be done! Note that you cannot copy it to another sheet (I know, a bummer, but what can you do?).

Hope this helps,

Russell

EDIT: Sorry about the field names, I only copied the data for my example!
This message was edited by Russell Hauf on 2002-12-10 11:45

Neat function, but I could only get it to return the first instance of JMR -- I want it to return them all. Am I missing something here?

Also, I was looking for something that may be more of a formula -- if I physically changed one of the JMR's in the original data to something else, I want my results data to reflect that (automatically eliminate that JMR from the results). What this data represents is sales account managers territories -- we want to see what their territories would look like if we redistribute some of their accounts -- thus, if I take some of JMR's accounts away from him, what will his resultant territory look like? In addition, what will KLU's territory look like if I give those accounts to her?

Hope this makes sense! Thanks...

WHOOPS... first make sure sheet1 is sorted ...

ok, this is pretty ugly, but...
assuming data on sheet2, columns A-C; assuming an entry - could be via validation list - on sheet1, cell B2 for the target ID. left C2/D2 open for use as totals.
in cell C3, enter:
=IF(ISNA(INDEX(Sheet2!$A:$C,MATCH($B$2,Sheet2!$A:$A,0),1)),"",INDEX(Sheet2!$A:$C,MATCH($B$2,Sheet2!$A:$A,0),2))
in cell D3, enter:
=IF(ISNA(INDEX(Sheet2!$A:$C,MATCH($B$2,Sheet2!$A:$A,0),1)),"",INDEX(Sheet2!$A:$C,MATCH($B$2,Sheet2!$A:$A,0),3))
in cell C4, enter:
=IF(COUNTIF(Sheet2!$A:$A,"=" & INDIRECT("$B$2"))>=2,INDEX(Sheet2!$A:$C,MATCH($B$2,Sheet2!$A:$A,0)+1,2),"")
in cell D4, enter:
=IF(COUNTIF(Sheet2!$A:$A,"=" & INDIRECT("$B$2"))>=2,INDEX(Sheet2!$A:$C,MATCH($B$2,Sheet2!$A:$A,0)+1,3),"")
then, for as many rows as you'd ever expect to retrieve, copy the cells B4 & C4 formulas down, incrementing the >=2 to >=3 and the +1 refs to +2's and so on for each row. If it needs to be more clear i can email you a test wb.
just_jon
This message was edited by just_jon on 2002-12-10 14:16
This message was edited by just_jon on 2002-12-10 14:17
This message was edited by just_jon on 2002-12-10 15:04

Wow, you've impressed our little research department...

I can't quite get the formula to work correctly. I return data points that I shouldn't be returning. If you have saved the sample WB, I'd love to have it!

Thanks a ton !

Ganderson@directs.com

On 2002-12-10 12:07, FireLokust wrote:
Neat function, but I could only get it to return the first instance of JMR -- I want it to return them all. Am I missing something here?

Also, I was looking for something that may be more of a formula -- if I physically changed one of the JMR's in the original data to something else, I want my results data to reflect that (automatically eliminate that JMR from the results). What this data represents is sales account managers territories -- we want to see what their territories would look like if we redistribute some of their accounts -- thus, if I take some of JMR's accounts away from him, what will his resultant territory look like? In addition, what will KLU's territory look like if I give those accounts to her?

Hope this makes sense! Thanks...

Click to expand...

Russell, remembering that I did similar questions, asked me to look at this one...

Let A1:C8 in Sheet1 house the sample you provided including the labels...

Sheet2

In A1 enter:

=MATCH(9.99999999999999E+307,Sheet1!B:B)-ROW($A1:$C1)

In A2 enter: JMR [ the target item ]

In B1 enter:

=COUNTIF(OFFSET(Sheet1!$A$2,0,0,$A$1,1),$A$2)

In B2 enter:

=IF($B$1,VLOOKUP($A$2,OFFSET(Sheet1!$A$2,0,0,$A$1,3),2,0),"")

In C2 enter:

=IF(LEN(B2),VLOOKUP($A$2,OFFSET(Sheet1!$A$2,0,0,$A$1,3),3,0),"")

In A3 enter:

=IF(LEN(B3),A2,"")

In B3 enter:

=IF($B$1>COUNTA($B$2:B2),INDEX(OFFSET(Sheet1!$B$2,MATCH(B2,OFFSET(Sheet1!$B$2,0,0,$A$1,1),0),0,$A$1,1),MATCH($A$2,OFFSET(Sheet1!$A$2,MATCH(B2,OFFSET(Sheet1!$B$2,0,0,$A$1,1),0),0,$A$1,1),0)),"")

In C3 enter:

=IF($B$1>COUNTA($C$2:C2),INDEX(OFFSET(Sheet1!$C$2,MATCH(C2,OFFSET(Sheet1!$C$2,0,0,$A$1,1),0),0,$A$1,1),MATCH($A$2,OFFSET(Sheet1!$A$2,MATCH(C2,OFFSET(Sheet1!$C$2,0,0,$A$1,1),0),0,$A$1,1),0)),"")

Select A3:C3 and copy down as far as required.

Russell: Care to provide code VBA that copies A3:C3 down as many rows as necessary using the value in B1?

See the exhibit...

Aladin

think i'll put the snatch on aladin's code, myself...
just_jon

You could use SUMIF to do this.

Make a list of the initials (If you have lots of them, let a pivot table do the work).

The list can be on another worksheet, or on the same one. Let's say that you have your intials in cells A2:A15, Sales in B2:B15, and Margin in C2:C15. Out in, oh, let's say column E2:E5 you have the initials of the individuals. In F2, enter

=SUMIF($A$2:$A$15,$E2,B$2:B$15)
and in G2 enter
=SUMIF($A$2:$A$15,$E2,C$2:C$15)

and copy those formulas down for each initials entry.

The totals will change each time you change one of the initials.

Well this would get you totals for each individual. If you want each entry grouped by initial, just do a sort.

I ask Aladin for a small favor and he turns on me!

Just kidding. Nice work as usual, Aladin!

Here is some code that will automatically fill/delete the formulas depending on the number of matches found (note that the formulas in Row 3 can never be deleted). This code goes in the Worksheet module*. Also, I would suggest to lock all the formula cells and unlock only cell A2 (I'd also probably hide row 1, but that is a personal preference - if you wanted the user to see how many matches there were - or if you want to make sure the code is working correctly, you would leave it visible), then protect the sheet. But I digress.

My worksheet example and code follow:

and the code:<pre><font color='#000000'><font color='#000080'>Option</font><font color='#000080'>Explicit</font></pre><hr align=left width=500><font color='#000080'><pre>Private</font><font color='#000080'>Sub</font> Worksheet_Change(</font><font color="#000080">ByVal</font><font color='#000000'> Target<font color='#000080'>As</font> Range)<font color='#000080'>On</font><font color='#000080'>Error</font><font color='#000080'>GoTo</font> HandleErr<font color='#000080'>If</font><font color='#000080'>Not</font> (Intersect(Target, Range("A2")) Is Nothing)<font color='#000080'>Then</font><font color='#000080'>Dim</font> intNumRows<font color='#000080'>As</font><font color='#000080'>Integer</font><font color='#000080'>On</font><font color='#000080'>Error</font> Resume<font color='#000080'>Next</font>
intNumRows = Range("B1").Value
If Err<> 0 Then GoTo ExitHere<font color='#008000'>' B1 didn't contain a value & wasn't blank</font><font color='#000080'>On</font><font color='#000080'>Error</font><font color='#000080'>GoTo</font> HandleErr

intNumRows = Abs(intNumRows >= 3) * (intNumRows - 2) + 3
Range("A4:C65536").ClearContents<font color='#000080'>If</font> intNumRows< 4<font color='#000080'>Then</font><font color='#000080'>GoTo</font> ExitHere

Range("A3:C3").AutoFill _
Destination:=Range(Range("A3"), Cells(intNumRows, 3)), _
Type:=xlFillDefault<font color='#000080'>End</font><font color='#000080'>If</font>

ExitHere:<font color='#000080'>Exit</font><font color='#000080'>Sub</font>

HandleErr:<font color='#000080'>Select</font><font color='#000080'>Case</font> Err.Number<font color='#000080'>Case</font><font color='#000080'>Else</font>
MsgBox Err.Description, vbCritical, "Error in Worksheet_Change Event"<font color='#000080'>End</font><font color='#000080'>Select</font><font color='#000080'>End</font><font color='#000080'>Sub</font></font></body></pre>
.

Hope this helps. If anyone has questions about the code, let me know.

Russell

*For info about accessing Workbook/Worksheet modules, click HERE
This message was edited by Russell Hauf on 2002-12-10 18:00

On 2002-12-10 17:55, Russell Hauf wrote:
[...]
Here is some code that will automatically fill/delete the formulas depending on the number of matches found (note that the formulas in Row 3 can never be deleted). This code goes in the Worksheet module*. Also, I would suggest to lock all the formula cells and unlock only cell A2 (I'd also probably hide row 1, but that is a personal preference - if you wanted the user to see how many matches there were - or if you want to make sure the code is working correctly, you would leave it visible), then protect the sheet. But I digress.
[...]

Click to expand...

Russell,

This is just great. And, a much justified use of VBA. I like the suggestions too.

Thanks.

Aladin