labeling rows

Sounds like a simple IF formula or a VLOOKUP. We need a little more information though. What type of criteria? How many criteria elements needs to be considered? How are the codes determined?

These don't need to be the exact criteria you're using, but some sort of example so we get an idea.

Seriously, there are about 40 columns of criteria related to each number.

Its kinda like copying and pasting the entire row. instead i want to label each row (000-999) so it takes all info in that row and compares it to another row

do you need a sheet to see what I'm talkin about?

I want to use VLOOKUP but on one column that will in-turn past the entire content/criteria of a number if it matches

heres the sheet I'm working on from another sheet. Take a look.

Ok - but what is your goal? What are you trying to do? What do you envision a script doing?

The sheet you viewed is specifically for North Car. but I have a master sheet that literally has every number (000-999)

Ok - so you want to say - if this cell is "782" - go to the master sheet, find 782, and copy all the information from that row into this row?

Exactly what I'm lookin' for.

Ok - it might make sense to use a macro for something like this, especially if you're hoping to keep the cells colored in the same way.

What about columns A & B? Should those be copied over too?

What about the Date column? I'm guessing those won't be copied from the Master file.

The Master file is identical to the State files in terms of the columns and separation. As I mentioned before, i can copy and paste from the master file directly into the state file, once I finished I just simply paste the dates back in a column and sort them using the date which will put them back in order (most recent to last). Yes I want to keep the cells colored. Column A signifies whether the number is a Triple (T), a double (D) or a single (X) number. Column B was used to sort the numbers from low to high.

I want to make it clear that the dates column can be overwritten because I can replace them.

Also, the sheet does not look like this in Open Office Calc (groups), I guess some things did not convert when I saved in xls format.

dont worry about the date column because I will insert those last for sorting

Ok here's the code I came up with. It's a little sloppy, but it works for me. Assuming that I have a "Master" sheet with the numbers 001-999, and that I'm starting with a sheet that has a list of numbers somewhere within that range (1,245,628,901,etc..) - This should work.

Please Login or Register  to view this content.

You'll have to adjust if the Master file is named something different or in a different sheet. If you're not using Excel, I'm afraid I can't be much help there.

Good luck!

I'm unsure how to apply and run this macro

To add a Macro in Excel, you'll need to open the built-in VBA Editor.

1. Open Excel. Open the file you want to add the macro too.
2. Hit CTRL + ALT + F11
3. This will bring up the VBA Editor window, hopefully with a pane down the left side.
4. You should see the filename for the file you're currently working.
5. Right-Click on that filename and select "Insert Module"
6. Paste the code about into that module.
7. You'll need to make adjustments to the code based off of your worksheets. Without providing specific information, it's hard to give a specific answer.

You can see that it will start in the cell A1 and work its way down. If you need to change the column where the picks are located, just change the Letter for Col="A".

Then it uses the Match formula to look up A1, A2, A3, etc.. from the "Master" sheet you mentioned. You can see where Master.xls is located in the code. Change that to be the name of your master workbook. If the information is not in Sheet1, change that. If the numbers 001-999 are not located in a1:a999, adjust that, but make sure to keep it as B1 or C1, etc.. and just change the last part to B1000 or C1001. Whatever you need.

8. Now that you're updated, go back out to Excel. Probably the easiest way to run this is to go back to your spreadsheet, North Carolina or whatever. Save a backup. Click at the very top of the Pick list. Go to TOOLS: MACROS: Select MatchPick3, and click Run.
9. Double check to make sure it did what you wanted. Compare it to the backup.

The macro seemed to have perfomed as expected but the colors are drastically different. Any reason why?

I changed the sheet from xls to xlsx and now I'm getting an arrow next to "Sub MatchPick3()"

Ok I had to modify the colors to fit within excel. Thanks a million werfnort!!!!