multiple 'if-thens' (Chain Rule) with two sheets

[abuchan]

I have attached a sample file with two sheets.

Sheet 1 has two columns, 'A' represents a parcel number, and 'B' represents an indicator for the plat name in which the parcel number is located.

Sheet 2 also has two columns. 'A' represents the same indicator as Sheet 1 Column 'B' and Column 'B' is the formal name for the plat.

What I hope to achieve is Sheet 1 Column 'A' to be the parcel number (as it currently is)and Column 'B' to be the formal name for the plat (instead of the number).

As you will notice, the plat numbers on Sheet 1 Column 'B' can repeat many times. A plat number of '0' would mean it is not associated with a plat and therefore should return a blank.

The 'real' file has over 275,000 parcel numbers. All total, there are approximately 8800 differnent plat names.

Thank you for considering and/or offering any advice.

Due to my 'lacking' skill level, I need it to be a macro as I have yet to learn other methods.

[protonLeah]

The formula below entered into Sheet1 cell C2 and copied down will work. You can then hide or delete column B:

Please Login or Register  to view this content.

To quickly copy down after entering the formula, double click the small solid square at the bottom right corner.

[abuchan]

Thank you, but please excuse my ignorance. I tried doing what you described but ended up with a blank column. However, if I highlight a cell in the column I see the formula you created in the formula window. I am obviously doing something wrong.

[protonLeah]

Here's your sample workbook with the formulas:

[abuchan]

That cleared everything up for me, thank you. *&Y^^%$, I wish I understood this stuff! Nice work, Thank you.

[abuchan]

I just ran the formula on the 'real' file and ran into some problems. It seems the most common return value is #N/A. I looked at some of them specifically and they have a a plat number and there is a plat name associated with the number on Sheet 2.

Is it possible that I am asking Excel to do too much? After all, there are over 280,000 unique parcel numbers and over 7,000 unique plat names.

Any help or advice you can provide is appreciated.

Thanks again for your efforts.

[protonLeah]

Can you post a sample that includes both correct and incorrect (#N/A) results?

[buran]

The 'real' file has over 275,000 parcel numbers. All total, there are approximately 8800 differnent plat names.

I think in the *real* file there are more than 25 entries and in this case using the absolute address Sheet2!$A$2:$B$25 in vlookup formula will cause #NA for all entries after row 25 on sheet2.

Just my 2 cents...

[buran]

If I'm right here is macro that will fill column C of Sheet 1 with the formula suggested by protonLeah.

Please Login or Register  to view this content.

It will find the last row in column A on sheet2 and use it in the formula. It will copy the formula down to the last row in column A on Sheet1.
Please, note that the sheet's names are hard-coded.
Buran

[abuchan]

Thank you for your efforts. Yes, there are more rows than 25. Sheet 1 has about 280,000 rows and Sheet 2 in the neighborhood of 7700.

I attempted to run the macro above, and it gave me a 'run-time error 1004.
Method 'Range' of object'_Global' failed.

When I 'debug'ed it highlighted the following code

Please Login or Register  to view this content.

Not sure what any of this means, but thank you for your effort.

Please note that I will be away from my computer until Sunday evening. Thank you again.

also, I don't know if it makes a difference but I have no idea what "Please, note that the sheet's names are hard-coded." means.

[protonLeah]

The line:

.AutoFill Destination:=Range("C2:C5" & LastRow2)

Should be:

.AutoFill Destination:=Range("C2:C" & LastRow2)

---------------------------------------
You said: "I don't know..what... 'the sheet's names are hard-coded'..means... "

Note that in buran's macro, there are several lines such as: "Worksheets("Sheet1")...."
So, if you had renamed your actual workbook tabs to something other that sheet1, shee2, etc, then you would have to search through the macro to replace the Sheet1/Sheet2 instances with the actual tab names you used.
============================
A third alternative is to create a dynamic named range that will adjust with the size of the list on sheet 2:
1. Formulas Tab on the Ribbon bar
>>Define Name
In the New Name dialog box, enter FullName in the name box
In the Refers to box, enter

Please Login or Register  to view this content.

Close the dialog box and return to sheet 1. Modify the formula to:

Please Login or Register  to view this content.

Copy down as before.

[abuchan]

You guys got it! Caught me as I was walking out the door to go see Elton John. A great day it is! Thank you.

[buran]

@ protonLeah: Thanks for fixing my stupid mistake :-)

Buran