What am I missing?

[ComDawg]

I am trying to build a script that can match the number in column a of worksheet1 to the same number in column a of worksheet2 where ever it may be in that column. If it finds a match then copy the text from column b of worksheet1 to column c of worksheet2. Here is what I have so far:

Sub compare_replace()
Dim ws1 As Worksheet
Dim ws2 As Worksheet

Set ws1 = Worksheets(1)
Set ws2 = Worksheets(2)

If ws1(a2(Match(ws2("a:a")))) Then
replace: ws2 ("c:2"), ws1("b:2")
Loop
End If

End Sub

Any assistance or links to other threads to read would be greatly appreciated.

Thanks in advance.

 

[SkipVought]

Hi,

Is there a reason that you are not using spreadsheet lookup functions. like INDEX, MATCH, or VLOOKUP?

Skip,
[sub]
Just traded in my old subtlety...
for a brand NUANCE![/sub]

 

[firefytr]

Hi there,

Why not just use the MATCH() function from sheet 2 column C?

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[ComDawg]

Skip,

Only reason I have for not using them is my own lack of knowledge of them. I thought this little project would not be difficult for me, But 5 hours into it and I'm beat. I would love to here any suggestions on this.

John

 

[firefytr]

Suggestions: Use the MATCH function.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[SkipVought]

Ditto!

Skip,
[sub]
Just traded in my old subtlety...
for a brand NUANCE![/sub]

 

[ComDawg]

Is there any where I can get some Samples of the Match Function? Is it in the Help?

 

[SkipVought]

Check Excel Help.

Search on Tek-Tips.

Skip,
[sub]
Just traded in my old subtlety...
for a brand NUANCE![/sub]

 

[firefytr]

Google.com

Yahoo.com

MSN.com

Ask.com

Good search keywords might be "excel match function" or "excel formula find exact match" or "excel match find exact", all w/o the quotes of course.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[ComDawg]

Thanks for the search tips! So you think a formula would be better to consolidate to worksheets?

 

[firefytr]

I'd recommend always going to formulas first. Shouldn't take very long, once you're done you can copy the values and do a paste special/values over the top of it, thus making the values static. The Help files should really help you on this one though, they're not too bad for that function. And it's one of the most searched on formulas online.

Be sure to post back if you need additional help.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[SkipVought]

"So you think a formula would be better to consolidate to worksheets?"

What! Where did you say anything about "consolidate worksheets?" Your question was regarding a LOOKUP procedure.

What so you need to "consolidate?" Is there data on multiple sheets?

Depending on the nature of your workbook design, it may be better to put your efforts into consolidating your scattered source data into one unified table, from which it will be infinitely easier to maintain, analyse and report.

Skip,
[sub]
Just traded in my old subtlety...
for a brand NUANCE![/sub]

 

[ComDawg]

Skip,

This is from my first post.
"If it finds a match then copy the text from column b of worksheet1 to column c of worksheet2"

I have 2 work sheets heres an example.

Worksheet1
Number Name
1234 joe
4321 john
7890 mary


Worksheet2
Number Bldg Name Port Des Date
1234 xxxx xxxx xxxx xxxx
1235 xxxx xxxx xxxx xxxx
1236 xxxx xxxx xxxx xxxx


I am trying to have a macro that worksheet2 will scan worksheet1 for a number match then pull the name from worksheet1 and move it to Worksheet2.

Thanks,
John

 

[SkipVought]

Nothing get MOVED. You can RETURN a value from a lookup range on another sheet.

Skip,
[sub]
Just traded in my old subtlety...
for a brand NUANCE![/sub]

 

[firefytr]

If you're just looking for the 'Name' column on worksheet 2, then an MATCH would return the found index number (generally row number, depending on where you start your range). If you wanted the value returned, you'd need to couple it with the INDEX function.

If, however, you want to return multiple column values based on their column headers, you'll definitely need the INDEX/MATCH combo, and if your data structure is not identical to worksheet 1, then you'll need an INDEX/MATCH/MATCH type return (for row AND column return).

Also, if this is "only an example" you may not find something here that fits your needs exactly. This is often the case with 'examples' of data, instead of actual data values. As I said, post back if you need more help.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[ComDawg]

I can not get the index/match function to work.

I can get vlookup to work though.

=INDEX(Sheet1!a2:b4664,Match(a55,a2:a2758,1),2)<-- I can not get this to work. The a55 after match is the cell number of the number im trying to match to the sheet1 index.

=VLOOKUP(12345,Sheet1!$A$1:$B$4664,2,FALSE) <-- this works but I have to change the value "12345" to match every entry on sheet 1 (4664 entries)

I'm still searching for some examples of formula's and or vba code to do this automatically. As always any help would be appreciated. Did I mention i am VERY new to VBA??

 

[firefytr]

Still not sure why you'd want to do this via VBA...

If you changed this formula...

=VLOOKUP(12345,Sheet1!$A$1:$B$4664,2,FALSE)

... to this ...

=VLOOKUP(A1,Sheet1!$A$1:$B$4664,2,FALSE)

.. would it work for you to copy down?

With a value not found, you'd receive the $N/A error.

As far as your INDEX/MATCH combo, I'm not sure where you're placing it, nor the data in the cells referenced.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[SkipVought]

The a55 after match is the cell number of the number im trying to match to the sheet1 index."

hold the lookup range ABSOLUTE (also in the INDEX function.
Reference the SHEET in the lookup range.
Use the ZERO Match Type...
[tt]
Match(a55,Sheet1!$a$2:$a$2758,[red]0[/red])
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a brand NUANCE![/sub]

 

[ComDawg]

THANK YOU!!!!!!!!!!!!!! I changed the vlookup as suggested and it worked perfectly!!!!!!!!. I am still going to try the index/match formula to see if I can get it to work. I will keep you posted. I really appreciate your help on this!

John

 

[firefytr]

Well, a couple of things, food for thought, on your INDEX/MATCH:


=INDEX(Sheet1!a2:b4664,Match(a55,a2:a2758,1),2)


Your initial range is not absolute, so it'll move with every move of your formula. Lock it in.

Same with your range in the MATCH() function. As far as what Skip suggested, that may be the case, but you should analyze whether that is indeed good for you and your data subset first. Note that an exact binary match can be quite expensive when laid out over a large series of cells, especially if you introduce error handling into your formulas.

So with that in mind, perhaps...

=INDEX(Sheet1!$A$2:$B$4664,Match($A55,$A$2:$A$2758,1),2)

If you have duplicates in your data, you may want to think about the 0 (or False) syntax Skip mentioned. Again, it depends on your data. If I were you, I'd try both ways and check it out for yourself.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci