Match Criteria

[Buliana]

Dear All,

Currently I am dealing with 16000 numbers to match. Since pivot table didn't work, is there any tips to match the numbers.Say for example for need to macth all the numbers at station 1 with other station. Appreciate if some body could possibly advise me on the above matter.

Thanks

[roly]

Hi,

You can use the COUNTIF function but this will only be accurate if the Station 1 has unique entries. If it does you can put an extra column of these totals for each station column (a bit messy) and sum those case match cells.

=IF(COUNTIF(B:B,$A2)>1,1,COUNTIF(B:B,$A2))

That will count station 2s case match and if there is more than one it will return 1, which I think is what you want. Fill it down to the bottom and across to the last station. Then sum them in the case match fields.

If the data does have duplicates then you may be able to create a custom function using VBA, which I can't do.

HTH,

Roly

[broro183]

hi,

It would be great if you could attach a zipped example xls file & we may be able to help in getting your PT to work.

To add to Roly's countif suggestion check out my attached untested version &/or the below links which may be helpful:

http://www.cpearson.com/excel/Duplicates.aspx

http://www.j-walk.com/ss/excel/tips/tip52.htm
- The formulae using "frequency" seems the most promising & you may be able to adapt it if my attached example isn't quite what you want.


hth
Rob

[Buliana]

Thanks for your reply,
Basically I am dealing with car registration number. Need to find out from station1, how many of them were observed at station 2 and station 3 by comparing registration number observed.

Cheers