Consolidating based on first three characters and counting

yoshimura

New member
Joined
Nov 22, 2012
Messages
18
Reaction score
0
Points
0
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

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,519
Reaction score
4
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
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.
 

Kevin@Radstock

New member
Joined
Oct 5, 2012
Messages
52
Reaction score
0
Points
0
Excel Version(s)
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
 

yoshimura

New member
Joined
Nov 22, 2012
Messages
18
Reaction score
0
Points
0
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
 

yoshimura

New member
Joined
Nov 22, 2012
Messages
18
Reaction score
0
Points
0
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)
SUBSCR.png

2)
SUMMARY.png
 

Kevin@Radstock

New member
Joined
Oct 5, 2012
Messages
52
Reaction score
0
Points
0
Excel Version(s)
365
They are the same! Just copy.

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

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,519
Reaction score
4
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
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.
 

yoshimura

New member
Joined
Nov 22, 2012
Messages
18
Reaction score
0
Points
0
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
Joined
Nov 22, 2012
Messages
18
Reaction score
0
Points
0
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)
 

Kevin@Radstock

New member
Joined
Oct 5, 2012
Messages
52
Reaction score
0
Points
0
Excel Version(s)
365
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
Joined
Nov 22, 2012
Messages
18
Reaction score
0
Points
0
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).
 
Top