Excel macro/formula

Lummux

Lummux

Lummux

Associate
Joined
18 Oct 2002
Posts
377
Morning all,

I’ve been playing with this all night and not got to far, I’m trying to find a way in excel to compare two columns and then if there is a match then add it to a running total.

So for example column a has about 3500 cells, they need to be compared to all the cells in column b (about 80), then if there is a match then add an the contents of column c to a total and display it.

If this was PHP, C, JAVA, etc… then I could do it but I’m having issues with excel, its completely foreign to me and I know when to admit I’m beaten.

And before anyone says it, its not home work (I’m a bit old for that), its the logs from a phone system that I need to analyse.

Cheers,
 
Hi,

Got one way around it, not sure if it is the best and I hope I have understood it, but here goes:

I use Index and Match (others prefer VLookUp but this is more durable). Placing the formula below into C1, I am asking the question 'Does A1 match any of the numbers in a given range (B1-B100) in column B?'. If so then place the value from A1 into C1.

=INDEX(B$1:B$100,MATCH(A1,B$1:B$100,0))

If no match is found, then the above formula will return #N/A. To tidy this up, I use the ISERROR function to trap for the error and report 'No Match ' instead. Therefore replacing the above formula in C1 with:

=IF(ISERROR(INDEX(B$1:B$100,MATCH(A1,B$1:B$100,0))),"No Match",INDEX(B$1:B$100,MATCH(A1,B$1:B$100,0)))

You can now put an AutoSum at the bottom of column C for your total.

I have uploaded an example for you to look at if the above isn't clear.

Hope this helps....and works :)
 
You must log in or register to reply here.
Back
Top Bottom