V-LOOKUP in English Please

addendum: post should read approx. 7,000 lines each.

Re: addendum: post should read approx. 7,000 lines each.

Vickie,

I'll guess and say that what he was doing was using VLookup to display #N/A errors. Which it would do if it were to look for an exact match in last month's data and it wasn't there. Best way to find out is try it.

Usually when folks post these kinds of questions I usually ask them if they've thought about using Access to do this task instead. So I'll ask you, have thought about that? Is it a possiblity (i.e., do you have Access available?) I know you'll think I'm trying to unnecessarily complicate your task, but in the long run it might be better for you to acquire that skill. (Assuming of course that Access is available to you.)

Anyway, try to use VLookup. You probably have to sort the last month's data. I assume the rows are unique, that is each row has a different Product in it.

enjoy

First go to Help on the far right side of the menu bar. Click on Microsoft excel help, then search for vlookup. I believe it would be best to use the vlookup "false" formula.
You will first have to name Sheet A with some table name (if it's not already named)let's say
tableA.
Then in an empty column in sheet B, starting on the first row or record of sheet B, enter the following:
=vlookup(cell#,tablename,column in table to return value of,false).
it will probably look something like this:
=vlookup(b6,tableA,3,false)
Then copy this formula down the column beside all records in sheet B.

What you should get is the value in column 3 of
tableA, that matches the value of cell b6. If there is no match I believe the formula returns
N/A# or something obviously saying "it ain't there".
If there is no match for the value in b6 then that item was not sold last month and is therefore a new item. OK?

To name a table, select all the data in sheet A,
then click on Insert, name, define, type in the name you want to use etc.
I have used vlookup for years. it's the best thing since sliced bread.
give me an e mail if you need more help.

VLOOKUP() in Excel will not work if you itmes were not sorted. Is you VLOOKUP() function on sheet2? If so try this, for example, your data start on UPC start on A2, price start on B2, and you type in a UPC in sheet2 B2, your VLOOKUP function in B3, VLOOKUP(B2,Sheet1!A2:B100,2) this will look for the price.

Excel is not a data base and cannot be easily update data. Use Access is better.

John -> sorting

Hi John,

VLOOKUP doesn't require sorting if you're after an exact match, I think.... so if the last arguement is "FALSE", the data can be in any old order - at least, on my tax spreadhseets it's all jumbled up

oh, wait, that's in '97 anyhow : I'm not sure about later versions

Here's one way to do it, in English with no VLOOKUP

Hello Vickie,

With all respect to your ex-coworker, I would not use VLOOKUP for this, but simply automate it with a VBA-based comparison, filter, and copy > paste macro. This way you only have to click a button for instant comparison results.

Let's say your workbook contains 3 worksheets, named CurrentMonth, PreviousMonth, and NewItems, and that each sheet has an identical header row in row 1, as such:

A: Product
B: Size
C: Color
D: UPC Code
E: Dollar Sales

I assume what you are specifically comparing is the Product item in column A, between the CurrentMonth sheet and the PreviousMonth sheet. If a Product item (in other words, a "value") is found in column A of the CurrentMonth sheet that is not found in the PreviousMonth sheet, then it (and the row it is in) would become part of a list that is compiled and displayed in a sheet we can call NewItems.

I have a workbook that I created and tested this on, so if you want to receive it, please post your email address, and I'll be happy to send it to you.

You can draw a command button on your CurrentMonth worksheet and attach this macro to that button.

Here's the macro, which you may need to modify for your exact situation. I included a bunch of notes in it so you can see why it does what it does.

Sub FindNewItems()
Application.ScreenUpdating = False
Sheets("CurrentMonth").Select
'Identify last row for comparison formula to be entered
Dim LastRow As Long
LastRow = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
'Enter comparison formula in F2
Range("F2:F" & LastRow).FormulaR1C1 = _
"=IF(COUNTIF(PreviousMonth!R2C1:PreviousMonth!R7000,RC[-5])<>0,""Same"",""New"")"
'Copy > PSV formula to prepare for filter
Range("F2", Range("F2").End(xlDown)).Copy
Range("F2", Range("F2").End(xlDown)).PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
'Execute filter
With ActiveSheet.UsedRange
.AutoFilter Field:=6, Criteria1:="New"
'Copy filtered data, dump it in next available row in New Items worksheet
'Assumes the same headers are in all sheets in row 1
.Range("A1:E7000").Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy _
Destination:=Sheets("NewItems").Range("A65536").End(xlUp).Offset(1, 0)
'Remove AutoFilter
.AutoFilter
'Clear formula generated criteria and go A1
Columns("F:F").Clear
Range("A1").Select
End With
Application.ScreenUpdating = True
End Sub

Good luck, hope this helps.

Tom Urtis

THANKS Can you send the test workbook please?

Tom, I'd appreciate it if you would send me the test workbook. I thought a macro or even Access would be a little easier than the VLOOKUP for this task as well but I thought it was because I didn't fully understand this function. You can send the test workbook to:

vickie321nc@yahoo.com

THANKS SO MUCH. This forum is great and I expect you all will see my name a whole lot more as I get more familiar with my new job!

Re: THANKS Can you send the test workbook please?

OK Vickie, no problem, the demonstration workbook is on its way.

Tom Urtis

: A previous employee left these instructions for finding new products/items with each monthly update. He's no longer around and manager doesn't have a clue and I'm an advanced-beginner having to go to intermediate/advanced level rather quickly. Anyway, I have two spreadsheets with approx. 7,000 each. Worksheet A is previous month's data. Worksheet B contains current month's data. Data looks sort of like

THANKS

My first instinct was to perform in Access or via a macro. Thanks for the input. : A previous employee left these instructions for finding new products/items with each monthly update. He's no longer around and manager doesn't have a clue and I'm an advanced-beginner having to go to intermediate/advanced level rather quickly. Anyway, I have two spreadsheets with approx. 7,000 each. Worksheet A is previous month's data. Worksheet B contains current month's data. Data looks sort of like:

THANKS TO ALL OF YOU!

This forum is GREAT! It's nice to have such a user group to exchange information. A previous employee left these instructions for finding new products/items with each monthly update. He's no longer around and manager doesn't have a clue and I'm an advanced-beginner having to go to intermediate/advanced level rather quickly. Anyway, I have two spreadsheets with approx. 7,000 each. Worksheet A is previous month's data. Worksheet B contains current month's data. Data looks sort of like: