# Thread: Consolidating based on first three characters and counting

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

4. Originally Posted by Kevin@Radstock
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)

7. They are the same! Just copy.

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

8. 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.

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. 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 Last

#### Posting Permissions

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