Results 1 to 10 of 13

Thread: Consolidating based on first three characters and counting

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1

    Question Consolidating based on first three characters and counting

    Hello,

    I would like to know how consolidating based on first three characters and count.
    Here are the details:

    There are a list of postal codes in the sheet SUBSCR, under the column POSTAL.
    They need to be consolidated based on the first the characters.

    Example:
    K1A 0H8
    K1A 0M5
    K1A 0T8
    K1A 0T8
    K1B 2U4
    K2E 1O6
    K2E 9D7

    Consolidates to:
    KIA
    K1B
    K2E

    There needs to be a count of each three digit in an adjacent column:
    K1A 4
    K1B 1
    K2E 2


    Regards,
    Yoshi

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,453
    Articles
    44
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Set up a column with your consolidation codes in them. From there, you just need to modify the function you got here to count them. Assuming your lists starts in A5, then I believe:

    =SUMPRODUCT(COUNTIF(SUBSCR!A1:A30,A5)-COUNTIF(SUBSCR!A1:A30,"POSTAL")

    Should work.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Acolyte Kevin@Radstock's Avatar
    Join Date
    Oct 2012
    Posts
    52
    Articles
    0
    Excel Version
    365
    Hi yoshimura

    Assuming your data is in A1:A7 and your criteria is in C1:C3, Try in D1 and copy down: =SUMPRODUCT(--(LEFT($A$1:$A$7,LEN(C1)+1)=C1&" "))

    Kevin

  4. #4
    Quote Originally Posted by Kevin@Radstock View Post
    Hi yoshimura

    Assuming your data is in A1:A7 and your criteria is in C1:C3, Try in D1 and copy down: =SUMPRODUCT(--(LEFT($A$1:$A$7,LEN(C1)+1)=C1&" "))

    Kevin
    Hey Kevin

    To clarify, the data = postal codes.
    What is the Criteria?

    Yoshi

  5. #5
    Acolyte Kevin@Radstock's Avatar
    Join Date
    Oct 2012
    Posts
    52
    Articles
    0
    Excel Version
    365
    Hi Yoshi

    Criteria:
    KIA
    K1B
    K2E

  6. #6
    Hello,

    This is all pretty new to me so I appreciate the patience.

    I'll go about a different way for solving my need.


    In the "SUBSCR" sheet I have postal codes (see 1 screenshot below).
    I need all the postal codes in the POSTAL column to be reduced to three characters in the FSA column within the "SUMMARY" sheet (see screenshot for desired outcome).

    1)
    Click image for larger version. 

Name:	SUBSCR.png 
Views:	11 
Size:	29.7 KB 
ID:	927

    2)
    Click image for larger version. 

Name:	SUMMARY.png 
Views:	10 
Size:	32.8 KB 
ID:	928

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •