Really Complicated Macro/Formula required - please help

tyders

New Member
Joined
Feb 3, 2005
Messages
40
Ok here goes - hopefully I explain it OK and someone can help - if you can this will save me hours and hours of work a week.

Each week I pull off a list of invoices for my team to chase. This list goes into excel and they put notes in the last column detailing what they have done. i.e.

invoice No, Description, Value, Comments

The trouble is each week I pull a new list and some invoices have been paid etc thus we need to use the new updated list. The bad side is I lose all notes as the notes are not from the system.

so heres my question. Is there any way to write a macro or something that will do the following things,

(((((List A = Old list that has notes on
List B = New list with invoices paid cleared from it and new invoices on))))

1. It will need to compare both lists. The macro needs to look at List A, if the invoice number in cell A1 is on the list B then move to the next cell i.e. A2. If the invoice number in cell A1 is not on the new list B then delete the row and move to cell A2

2. Another Macro to work the opposite way. Any invoices on List B that are not on List A need to be highlighted.

Im really hoping someone can help with this one.

Thanks all.
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Crow_23

Board Regular
Joined
Feb 17, 2005
Messages
183
Vlookup?

Hey tyders,

From what I gather all you need is a vlookup that references the invoice #s, assuming that an invoice # is not duplicated.
 

Crow_23

Board Regular
Joined
Feb 17, 2005
Messages
183
Vlookup?

Hey tyders,

From what I gather all you need is a vlookup that references the invoice #s, assuming that an invoice # is not duplicated. I do somewhat the same thing that you have mentioned by just using the vlookup function.
 

tyders

New Member
Joined
Feb 3, 2005
Messages
40
Thanks for the response. I havnt got around to using VLookups yet in the excel course im doing so any guidance on how to do it would be appreciated and I will give it a go.

Many Thanks
 

KenWright

Active Member
Joined
Jan 14, 2005
Messages
267
How does VLOOKUP work??

The VLOOKUP function essentially takes a value that you specify, whether it be a hardcoded number/letter/text or a value within a cell reference, and then goes and looks it up in a table. It will look for that value in the leftmost column of the table, and either find it or the closest match, and will then return the corresponding value on the same row, in whatever column of that table that you tell it to:-

Example - With the following table

D E
1 1 0.20
2 10 0.25
3 20 0.30
4 30 0.35
5 40 0.40

and with your value that you are looking up in say cell A1 (and let's assume it is 25 for example).

You can put a formula in pretty much any other cell, that says, take the value in cell A1 (25), go and look for it in the lefthand column of the table (D1:D5), and then when you have found it (or the lowest closest number to it), go to the second column (or whichever one you specify if there
are more than 2) and give me the number that it is on the same row in that column.

So, with the formula being =VLOOKUP(A1,D1:E5,2) it will first take the value in A1 which is 25, then go look for it in the leftmost column (D) of your specified table (D1:E5), and it will try to find that number. Now it isn't there, so what it will do is look for the next lower closest number, which in this case will be 20, and the 2 in the formula says to go and get the value in the 2nd column (E) in your table, that is on the same row as the 20. That value in this case is 0.3

If you put the value 30 or 31 or 32 etc into A1 now, you will see the result of the formula change, because now it will either find those numbers or the lower closest number (and in each case there it is 30), and will subsequently return 0.35 as the corresponding value.

Just to show you how the 2 really works in that formula, if you added one more column to your table so that it looked like this:-

D E F
1 1 0.20 0.15
2 10 0.25 0.25
3 20 0.30 0.35
4 30 0.35 0.45
5 40 0.40 0.55

and you actually wanted the value from Col F, then you would simpl;y change the 2 in the formula to a 3 to signify the third column, eg:-

=VLOOKUP(A1,D1:E5,3)

With the examples already given, 25 in A1 would return 0.35, and 30/31/32 would return 0.45

The one caveat to all of this (When getting the nearest number is OK) is that the data in your leftmost column must be sorted in ascending order.

There are times when you would only want it to give you a value if you had an exact match on the number, and in this instance you would simply add a 4th argument of 0 or FALSE to the formula,
eg:-

=VLOOKUP(A1,D1:E5,3,0)

or

=VLOOKUP(A1,D1:E5,3,FALSE)

In these cases you do not need to have the data in your leftmost column sorted.
 

tyders

New Member
Joined
Feb 3, 2005
Messages
40
Ok im nearly there.

I have managed to do the Vlookup function on a single worksheet as per the example above.

How would I do it with 2 worksheets so it pulls info from 1 worksheet to another?

i.e. sheet 1 = Old
sheet 2 = New

when I type the formula in the New sheet it looks like this,

=VLOOKUP(New!B1, Old!A1:I11, ?)

The ? part is what I am having trouble with. I want to return the value that is on the Old sheet. The ? should be the number 8 to reflect the 8th column however by just putting the number it refers to the number 8 on the New sheet not the Old.
I ahve also tried putting in Old!8 and it comes up with #N/A

Any ideas?

Thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,575
Members
414,390
Latest member
plimbu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top