Excel wizardry

InsideInsurance

I am trying to automate a spreadsheet for control purposes.

Everyday emails come in and with information attached which includes a name, an ID number and a second name. These are logged on the control spreadsheet along with some other info like the date received, who received it etc.

Separately to that a second spreadsheet/ report is produced which contains a list of the two names and ID numbers that should have been received that day.

What I need to do is find a way of getting the information from the report and inserting the names and ID numbers into the control spreadsheet when the first name and ID do not already exist - the second name shouldn't be checked for duplicates but should be inserted.

At the moment there are only 10 or so lines on the report each day so not too bad to manually check but this could go up to 200 before too long and will then be painful.

Any pointers on how to do it? My VBA skills are fairly limited

SavvyMail

There are many ways to do this....

The easiest is to use a VLOOKUP.

VLOOKUP(*What you are looking for in spreadsheet 1*,*where you are looking for it in spreadsheet 2*,*what you want it to return if it does find it*,FALSE)

e.g.

VLOOKUP(A1,'Sheet2'!$A$1:$A$1000,1,FALSE)

• This looks up value in A1
• Searches for it in spreadsheet "Sheet 2" in the range A1 to A1000
• If it finds it it will return the value.
• FALSE indicates it must match exactly.

Then, any time a value is returned, it means it is already in the other spreadsheet. If the formula returns #N/A it means it isn't in the other spreadsheet and you can copy and paste it in.

The dollar signs indicate the search range will not change if you copy and paste the forumla into other cells and therefore you can copy & paste this forumula every time you use it. What you will have to make sure is that all spreadsheets included in the forumla are open.

If you want it to be even more sleek I can advise how you can turn this into a compound IF forumla which returns anything you like, or even more complicated and throw the values in the other spreadsheet for you!

EchoLocation

I don't have access to Excel at the moment so unfortunately I can't write anything for you, but I would do it as follows...

Firstly, make a change to the control sheet and add a column that joins the 'Name' and 'ID Number' - thus creating a unique reference.

Then create a Macro to do the following...

Add a column to the report and populate it with the joined 'Name' and 'ID Number' values - creating a unique reference to match with the new column in the control sheet.
Then add another column to the report and use a countif function to mark any unique values that don't exist in the control sheet.
Then use the AutoFilter to select and copy these records across to the next available row in the control sheet.

If you want to keep track of which values have been added and when, you could also add a 'Created Date' column in the control sheet.

If you know nothing of VBA then this maybe a bit of a challenge, but if you have a limited knowledge you should be able to piece bits of code together from a bit of googling.

bridlestile

Hi, I'm with SavvyMail on this - as long as you have at least 1 item which is identical / common across both your sets of data, then you can combine the two lists very very easily with VLOOKUP.

Good luck,
Richard

paddyrg

Alternatively, can you make good use of the Excel built-in 'remove duplicates' and 'sort' functions?

Sometimes it is easier to insert and de-dupe than checking for dupes before updating