MegaSack DRAW - This year's winner is user - rgwb
We will be in touch
I need some help from someone who is handy with Excel.
I have two worksheets. The first worksheet has a list of telephone numbers dialled. The second worksheet has a larger list of telephone numbers dialled along with a cost for that call in the same row.
I want to create a new column in the first worksheet which searches the list of telephone numbers in the second worksheet, finds a match between the number in the first worksheet and second worksheet and finds the cost of the call associated with the dialled number in the second worksheet.
Am I being too ambitious? I would do it manually, but I have 5,000 rows in the first sheet and 12,000 rows in the second sheet so a working formula would be excellent.
Any help would be much appreciated
Look up pivottable should be able to do what your asking.
Just to finish, the pivot table will let you see all calls against each number, them use av lookup to cross reference between the two worksheets.
VLOOKUP is your friend
How big are the spreadsheets? Wanna email them to me (in my profile) I'll have a look at it for you...?
If you just want the total cost for each number, you can use SUMIF, I think:
=SUMIF([Column of numbers on sheet 2],[Cell address of number you want to check total cost for],[Column of costs of calls on sheet 2])
That what you're after? You can copy that down a column on sheet 1 to get the total costs for each number, if you see what I mean.
Just a further comment, a v look up on its own will return the first match.and if your going to do it right you are going to have to rely on exact matches, ie set the flag to false,
What ever method you choose make sure the phone numbers use a common format or it will go wrong
Thanks everyone. Having a look at vlookup's in the first instance.
vlookup will work, but only if there is only a single result for each number in the second table. You will also need to incorporate and "ISNA" argument to remove the N/A results.
A pivot table looking solely at table two would be better as you can use the sum function (or average depending on what end result is needed). The results of the pivot table can then be used with the vlookup function (set to false) if you need to reduce it specifically to the numbers in table one.
Send me the sheet if you like, quite happy to do it for you & mail it back. T'is a piece of the proverbial.
GTD