Partial text string
Hi Everyone,
I have been using the simple formula;
< =COUNTIFS(H:H,">=1",D:D,"WA1*") >
to COUNT when a range equals a partial postcode, in this instance WA1 and it works great. However I am having an issue of double counting when I have a postcode such as WA11 when it then counts the WA1 and WA11 results as one hit.
Is there a way around this problem without having a list of all postcodes and doing a VLOOKUP?
Thanks in advance for your help,
E.
What's after WA1xxxxx?
What's xxxxx portion?
That's the nature of the * wildcard. It means "any number of characters and any character". You may need to diversify your formula.
How about
=COUNTIFS(H:H,">=1",D:D,"WA"&ROW(A1))
Copy down. In the first row you'll get the results for WA1, in the second for WA2, in the 10th for WA10 and in the 11th for WA11 etc. No double counts.
Hi zbor,
A standard British postcode is made up of either 5, 6 or 7 letters and numbers in different combinations to represent a postal area, for example, WA1 1AA OR WA11 8BB and so on.
All I need to do is count my data by the first part of the post code, for example WA1 ###, OR WA11 ###.
Does that make sense? its hard to describe.
Thanks
E
Add space:
Formula:
Please Login or Register to view this content.
@zbor, the British post codes work on a pattern with two groups. The first group can have between 2 and four characters. The second group has 3 characters. It's complex. See Wikipedia.
Postcode unit
The postcode unit is two characters added to the end of the postcode sector. Each postcode unit generally represents a street, part of a street, a single address, a group of properties, a single property, a sub-section of the property, an individual organisation or (for instance Driver and Vehicle Licensing Agency) a subsection of the organisation. The level of discrimination is often based on the amount of mail received by the premises or business. Examples of postcode units include "SW1W 0NY", "PO16 7GZ", "GU16 7HF", or "L1 8JQ".
Validation
The format is as follows, where A signifies a letter and 9 a digit:
Format Coverage Example
AA9A 9AA WC postcode area; EC1–EC4, NW1W, SE1P, SW1 EC1A 1BB
A9A 9AA E1W, N1C, N1P W1A 0AX
A9 9AA B, E, G, L, M, N, S, W M1 1AE
A99 9AA B33 8TH
AA9 9AA All other postcodes CR2 6XH
AA99 9AA DN55 1PT
Hi teylyn,
Thanks for the advice. I have put the code in but its not returning any values. Not sure if I'm using it right, any suggestions?
Thanks
E
Pls attach sample file with expected result
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Suggestions? Yes. Post a sample file.
Thx teylyn.
As I see it user put manualy code start instead of reference so if space is after WA1 above should work.
zbor, I did try the space hoping for an easy fix but the double counting still happened.
teylyn / nflsales, I'll post a sample book as soon as I can take out any confidential data from it.
Thanks all for your help!
OK, attached is the test sheet.
Cells U17 and U18 should not equal more than 100%, but it is double counting PE2 and PE20 postcodes.
Any advice is welcome.
Thanks
E
So what happens if you insert the space into the formula? Since the post code is either "PE2 xxx" or "PE20 xxx", you have a handle there. Use "PE2 *" instead of "PE2*" -- that will not include PE20
it was boring for me to edit each formula so I use references (since they are already written).
And space works for me.
Formula:Please Login or Register to view this content.
Hi I changed the formula in cell W7 as follows and it worked correctly. Exactly as previously suggested.
JimBobBowie
Please Login or Register to view this content.
All, thanks for all your help and advice, the examples provided did the job!
Much Appreciated!
E
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks