Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13

Thread: Consolidating based on first three characters and counting

  1. #1

    Question Consolidating based on first three characters and counting



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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,284
    Articles
    57
    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
    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
    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:	7 
Size:	29.7 KB 
ID:	927

    2)
    Click image for larger version. 

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

  7. #7
    They are the same! Just copy.

    Or upload a sample workbook with dummy data and your requirements.

  8. #8
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,284
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Yoshi,

    I'd agree with Kevin here. If you want to upload some sample data (just make sure there's nothing sensitive in it), then you can do so by clicking the "Go Advanced" button next to the "Post Quick Reply", or by double clicking the "+Reply To Thread" button. There is a file manager where you can upload files in the resulting window.
    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.

  9. #9
    Hey guys,

    I just used the LEFT function and it works… except that I need it to start referencing from a cell and then down the whole column.

    An example that is giving me an error:

    =LEFT(C3:C,3)

  10. #10
    Here's the answer to my question:
    INDIRECT function.

    I thought I would have to use A:A to make the reference extendable, but it didn't work.

    Using A1 worked instead.

    =INDEX('[file.xlsx]Consolidated Summary'!A2,1,1)

Page 1 of 2 1 2 LastLast

Posting Permissions

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