Combine rows that have the same zip - but some are long postal codes and others are short

[CNichols84]

Hi, Completely noob here obviously, but I'm glad to be here and learning!

I know this will have a simple answer, but i'm not able to put together in my brain the way to do this.

I have two rows that each have a zip and revenue amount

1. 01040 $1000

2. 01040-2223 $2000

I want to sum this into one line

01040-2223 $3000

There could be any number of lines that have these zip codes, so my thinking is I need some sort of formula that says "If first 5 numbers match, then sum together"

I'm sure there is a very simple way to do this, but I haven't learned it yet :)

Thanks for the help

[JamesCharnley · Accepted solution]

Hi @CNichols84,

This shouldn't be too difficult, but the exact answer will depend on which ZIP you want in your final output. Matching the first five as you said can be done with a formula and summarize, but the output will still be the first 5 for now. If you wanted the max length for example, we could do that but it would take more tools.

[DataNath · Accepted solution]

This ought to work as long as there's no massive variation in your formats that you haven't mentioned in your post. Can keep/remove fields as you wish with a Select tool afterwards:

[CNichols84]

@DataNath @JamesCharnley 

Thank you both very  much for the help and it was also helpful to see two different ways of slicing the same bread.

The above worked great, my only final question - and again a complete noob question - but when I summarize the fields, it doesn't sum the null/empty field with a row which has a name.

For example, There was actually a third column "Rep"; My output is now

Row 1 01001, $3000, Null/empty field

Row 2 01001, $1000, Stacy Smith

It worked combining the zip codes, but now it won't fully group the zip even with empty fields in the Rep column.

what I would want to see is

01001, $4000, Stacy Smith

Again, I know this is very basic stuff, I swear I've started doing the tutorials but I clearly have a long way to go!

Thanks for the help!

[DataNath]

Hey @CNichols84 no problem at all - happy to help.

In the Summarize tool, you should just be able to select your [Rep] field and then go to String > Concatenate. If there's only one rep per postcode then you can also use Longest or something.