# Thread: Consolidating based on first three characters and counting

1. ## 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. 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.

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

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. Hi Yoshi

Criteria:
KIA
K1B
K2E

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)

2)

#### Posting Permissions

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