# Consolidating based on first three characters and counting

#### yoshimura

##### New member
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

#### Ken Puls

Staff member
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.

##### New member
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

#### yoshimura

##### New member
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

Hi Yoshi

Criteria:
KIA
K1B
K2E

#### yoshimura

##### New member
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)

##### New member
They are the same! Just copy.

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

#### Ken Puls

Staff member
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.

#### yoshimura

##### New member
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)

#### yoshimura

##### New member
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)

##### New member
yoshimura

What result would you return if you had a post code: BA14 5TT!

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)

#### yoshimura

##### New member
Hello Kevin,

The result would be BA1. Is there something particular that you're trying to figure out, or?

I used the INDEX function to meet my need for this. My next issue is in this thread (I also uploaded the Excel files).