VLOOKUP: Range & Return

Hi

Here some links on lookups

http://www.cpearson.com/excel/lookups.htm#LeftLookup

http://www.contextures.com/xlFunctions02.html

http://www.contextures.com/xlFunctio...tml#IndexMatch


VBA Noob

Hi this might help

You will need to mod to your requirements

Please Login or Register  to view this content.

This uses Sheet1 as the reference page, housing the reference cell (F5) and the lookup column (Column A). It then transfers columns A:E in the row of any "found" value to the next available row in Sheet2.

Please note that both the reference value and the items in the lookup column can be formulas, text, or values. It will carry over any formulas to the new sheet, so please modify to correct any new referencing problems.

VBA Noob

Spot on

VBA Noob

OK. So since I want to apply this macro multiple times over, across an entire list, I reckon I need to change

Please Login or Register  to view this content.

to loop through a range of cells (change "F5" to a function of some sort)? Or do I need to some how pipe my list into an array which would replace "F5"?

Also, since I'm comparing data across two worksheets, how do I change the reference value to show it in a different location?

Originally Posted by Keeper4826

Wow, I think you and I should switch monikers!

I'm having a hard time visualizing how this lookup (the worksheet) is setup. I imagine Sheet1 has a rang of cells, A1:E20 for example, that has data, say HR info. Next to this range, in column F, you have the reference list, say a list of usernames (which would be dumplicated in the data range). The macro looks are cell F5 and then searches the data range for a match. If found, that row (columns A through E) will be copied to Sheet2. Did I get that correct?

In my case, my reference list is in a seperate worksheet from the data I wish to search on and return. How do I incorporate this? To extend the return data range, I changed

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

; as I want columns A through K copied. Did I get this correct?

Hi,

after speaking with VBA Noob, it is agreed that

Sheets("Sheet1").Cells(x, 11)).Copy _

(which is column K only) should be

Sheets("Sheet1").Range(Cells(x,1),Cells(x, 11)).Copy _

which is columns A to K

hth
---
added, looking back at the original code, did I miss a 'Paste' ?
---

Originally Posted by Keeper4826

OK. So since I want to apply this macro multiple times over, across an entire list, I reckon I need to change

Please Login or Register  to view this content.

to loop through a range of cells (change "F5" to a function of some sort)? Or do I need to some how pipe my list into an array which would replace "F5"?

Also, since I'm comparing data across two worksheets, how do I change the reference value to show it in a different location?

Hi,

How are you designing to run this lookup?

First thought is to activate for a single row when an entry into (say) F (f5) (or - per your first post column B ? ) is made, or you may want to run this as a macro to do all existing rows, or both.

Any clues?
---

Originally Posted by Keeper4826

Made suggested code change and got an error. See screen print.

Also, for the time being, I've copied my reference data to the same worksheet (ITIM - Sheet1 in example) in column M. Though I want to eventually perform against a range (mulitple outputs to Sheet2), I've stuck with M15 for the time being, which is a known match.

Hi,

delete the first red line

Im not sure about the last two red lines, firstly I would join them as 1 because the break where you have it would mislead me,
---
added
---
I have some problem with the 'cells' - but

Please Login or Register  to view this content.

does the copy as required.

hth
---

Originally Posted by Keeper4826

Never built a macro before, so how I'm designing to run is funny question. Maybe I should start with the basic premise again.

I have a worksheet with several columns of data, A through K. In a seperate worksheet, I have a reference list - a partial list of a single column from the first worksheet. I want to perform a search, using the reference list, against first worksheet. For each match, I want to return the entire row of data for that match.

The goal is to identify the sub list within the master data sheet. Does that make sense?

ok - how does the single column sheet come to be, did you extract it from another place as a 'bulk' column? - or do you type items in as you go, one item at a time?

---

OK, I figured out how to step through the macro with the debug utility, and am starting to understand the code a bit better.

To get things going, using my example, I copied the NETWORK_ID column from the PS worksheet to column M of the ITIM worksheet (later, I'd like to learn how to do this search between the two worksheets to avoid this step). The header is in row 9 like the others. I created the macro as follows:

Please Login or Register  to view this content.

In stepping through the debug process, I noted that the reference_value never changed. It checks only the 1st cell on the list/column (M9). As this is the header, I obviously need to change it to M10. Next, I need to change the If-Then statement so that after running through the potential match list, the reference_value moves to the next cell down. I assume this would be done by reusing the code found in the lastrow_lookup variable?


***Update***

Found my problem, though don't know how to fix.

Please Login or Register  to view this content.

This points to column A rather than column B. Column B has the network IDs, so this is where the possible matches exist. How do I change this line accordingly?

Originally Posted by Keeper4826

OK, I figured out how to step through the macro with the debug utility, and am starting to understand the code a bit better.

To get things going, using my example, I copied the NETWORK_ID column from the PS worksheet to column M of the ITIM worksheet (later, I'd like to learn how to do this search between the two worksheets to avoid this step). The header is in row 9 like the others. I created the macro as follows:

Please Login or Register  to view this content.

In stepping through the debug process, I noted that the reference_value never changed. It checks only the 1st cell on the list/column (M9). As this is the header, I obviously need to change it to M10. Next, I need to change the If-Then statement so that after running through the potential match list, the reference_value moves to the next cell down. I assume this would be done by reusing the code found in the lastrow_lookup variable?


***Update***

Found my problem, though don't know how to fix.

Please Login or Register  to view this content.

This points to column A rather than column B. Column B has the network IDs, so this is where the possible matches exist. How do I change this line accordingly?

missed Page 2 of the posts,
the easy answer first -

Sheets("ITIM").Cells(x, 2)

---

Originally Posted by Keeper4826

OK, I figured out how to step through the macro with the debug utility, and am starting to understand the code a bit better.

To get things going, using my example, I copied the NETWORK_ID column from the PS worksheet to column M of the ITIM worksheet (later, I'd like to learn how to do this search between the two worksheets to avoid this step). The header is in row 9 like the others. I created the macro as follows:

Please Login or Register  to view this content.

In stepping through the debug process, I noted that the reference_value never changed. It checks only the 1st cell on the list/column (M9). As this is the header, I obviously need to change it to M10. Next, I need to change the If-Then statement so that after running through the potential match list, the reference_value moves to the next cell down. I assume this would be done by reusing the code found in the lastrow_lookup variable?


***Update***

Found my problem, though don't know how to fix.

Please Login or Register  to view this content.

This points to column A rather than column B. Column B has the network IDs, so this is where the possible matches exist. How do I change this line accordingly?

to have a changing 'reference value,

Please Login or Register  to view this content.

but you need to do a 'With' rather than specify 'Sheets' each instruction

---

Originally Posted by Keeper4826

OK, I figured out how to step through the macro with the debug utility, and am starting to understand the code a bit better.

To get things going, using my example, I copied the NETWORK_ID column from the PS worksheet to column M of the ITIM worksheet (later, I'd like to learn how to do this search between the two worksheets to avoid this step). The header is in row 9 like the others. I created the macro as follows:

Please Login or Register  to view this content.

In stepping through the debug process, I noted that the reference_value never changed. It checks only the 1st cell on the list/column (M9). As this is the header, I obviously need to change it to M10. Next, I need to change the If-Then statement so that after running through the potential match list, the reference_value moves to the next cell down. I assume this would be done by reusing the code found in the lastrow_lookup variable?


***Update***

Found my problem, though don't know how to fix.

Please Login or Register  to view this content.

This points to column A rather than column B. Column B has the network IDs, so this is where the possible matches exist. How do I change this line accordingly?

Please Login or Register  to view this content.

seems to copy them
---

Originally Posted by Keeper4826

I have two data dumps. One from PeopleSoft, the other and LDAP feed from our ID management utility. Both are in Excel format (two separate files) and identify accounts which have supervisor status. The goal is to identify accounts which reside in one data store and not the other. This is a SOX audit issue.

I've copied the worksheets from the two reports into one spreadsheet, and then created a third worksheet for the resulting search function/macro. I don't know how to return a false search match, so I started with a true match and figured I look up how to flip it later. So, I used VLOOKUP to compare the ITIM worksheet to the PS worksheet, using network ID as the reference. This generated a single column list of matches, true supervisors. Opposite of what I wanted, but a start.

Then I started to look at how I could copy a whol row of data. I need to report a user's full information, such as firstname, lastname, their supervisor's name, etc. (all contained in the row for each user). That's what lead me to where I am. VLOOKUP isn't powerful enough for this. Thus the macro.

This is all a very clunky way of getting the data I need. But I need it fairly fast, and it is what it is. I'll look a streamlining this process later (more question, yeah!!!!). I've created a mockup of the spreadsheet I'm using. I'lve stipped out VLOOKUP function and the macro I've been playing with. Take a look at let me know if what I'm trying to do makes sense.

there are no formula in this workbook (except the totals), and column M (as per your code) has no value, did I miss something?

---

Originally Posted by Keeper4826

Yup. You missed the second to last line in my statement. I stripped the code out cause VLOOKUP was the wrong route to take, and the macro I'd written was AFU.

So you were going to start again?

We're getting places now. Here is where I'm at with the macro:

Please Login or Register  to view this content.

Next steps:

Adjust to perform macro against entire range in column M.

means what? - you are doing a columns A to K copy based on column B being equal(or not) to M10
What do you propose to do next?

Change search to perform on column in a different worksheet (PS).

Dim a variable as String, and set it to the sheetname required, then use the variable in place of the current "ITIM"

Flip the IF/THEN statement so that if a match is not found, the row of the netID being testd for is copied.

change
If Sheets("ITIM").Cells(x, 1) = reference_value Then
to
If Sheets("ITIM").Cells(x, 1) <> reference_value Then

The ultimate goal is it identify which accounts can't be found in both worksheets. I'm imagining two macros doing the same function. One that tests PS against ITIM, and another which does the opposite (unless the two can be comined into one macro?).

I'm not sure I see how this request fits into your current macro.

Define 'acount' for the purpose of this matching, and is each 'account' (as so defined) unique within the worksheet.

---

Originally Posted by Keeper4826

I've completed the macro. I went back tothe drawing board, using previous suggestions as a framework. Here's what I came up with:

Please Login or Register  to view this content.

This loops through two columns in two separate worksheets. It takes the first cell in the ITIM worksheet (column B) and validates it against each cell in column F of the PS worksheet. If no match is found, then the entire row for that cell in the ITIM worksheet is copied to a 3rd worksheet. It continues to loop through the column in ITIM, validating each cell against the column in PS, recording records which exist in one worksheet and not the other.

I've attached the test spreadsheet with the code example. The macro hasn't been run yet.

Hi,

it appears to do ITIM ok, but misses the point that Terrell Owens is on PS but not on ITIM, was this supposed to have been detected? (see attached)

hth
---