Apple Event: May 7th at 7 am PT
I have nearly 400 clients. I want to populate each address into a spreadsheet within that workbook.
Example. 30909 populates into a cell next to a cell labeled Augusta.
Do I have to create that spreadsheet and cell, and then manually add those locations, or is there a way to get it done automatically using Numbers?
Thanks in advance,
RC
MacBook Pro 15″, macOS 12.4
You don't have to create a column for the zip code
I imported the zip code data that was recommended by SG in a sheet.
Column A is now formatted as text.
With XLOOKUP you can search for the zip code and get the County and Town / City as a result.
Formula for cell C2: = XLOOKUP(REGEX.EXTRACT($B2,"\d+$",match-occurrence,capture-group-occurrence),zip_code_database::Table 1::A,zip_code_database::Table 1::H,"--",0,1)
Formula for cell D2: = XLOOKUP(REGEX.EXTRACT($B2,"\d+$",match-occurrence,capture-group-occurrence),zip_code_database::Table 1::A,zip_code_database::Table 1::D,"--",0,1)
Here the 2 sheets that I use
Ralf
You don't have to create a column for the zip code
I imported the zip code data that was recommended by SG in a sheet.
Column A is now formatted as text.
With XLOOKUP you can search for the zip code and get the County and Town / City as a result.
Formula for cell C2: = XLOOKUP(REGEX.EXTRACT($B2,"\d+$",match-occurrence,capture-group-occurrence),zip_code_database::Table 1::A,zip_code_database::Table 1::H,"--",0,1)
Formula for cell D2: = XLOOKUP(REGEX.EXTRACT($B2,"\d+$",match-occurrence,capture-group-occurrence),zip_code_database::Table 1::A,zip_code_database::Table 1::D,"--",0,1)
Here the 2 sheets that I use
Ralf
rcandrewz wrote:
at the very least I would love for it to populate in groups the zip codes.
From the example you posted it looks as if you can easily extract the postal code into a separate column, with something like this:
=REGEX.EXTRACT(B2,"\d+$")
(replace the , with ; if your machine is set to a region that uses , as a decimal separator)
Once you have the zip codes in a separate column then it is easy to group by postal code by adding a Category.
However, looking up the city/town/subdivision in an automated fashion is not something Numbers can do natively. That will require using one of the various api's available. or perhaps looking up the data in a spreadsheet that can be downloaded for a fee.
BTW, Numbers does not have "workbooks." The equivalent of an Excel workbook is called a document or spreadsheet. And a sheet in Numbers is a blank canvas on which you can place multiple tables, charts, shapes and other objects. A table contains a grid with rows and columns of cells. Therefore, I labeled it "Master Table" and not "Master Sheet."
SG
Could you please post a screenshot of your address list, maybe a mix of 10-15 in different cities.
Show these addresses in a second screenshot in the format that you’d like to have as the end result.
That will make it easier to get exactly what you want.
Please replace all company names with dummies, then nobody can see confidential information.
Ralf
Yes that makes sense.
From where do I get the information for county, city/town and subdivision, these are not listed in master sheet.
Should I just assume that they are in master sheet or how are they generated?
I generated them by hand and went about the laborious and time consuming research. That is why I was wondering how to go about setting up a workbook and spreadsheet(s) that would do it automatically when the zip code gets populated.
I am not sure if I am explaining this correctly.
BUT at the very least I would love for it to populate in groups the zip codes.
I understand, but because it took Apple 30 years to create a spreadsheet application better than Excel as an Apple user since the late 80s, some old habits die hard, such as calling a document a workbook.
But That was very helpful and much appreciated. I should also note that I am using an Excel workbook file sent to me and converting it into my "environment."
I will then send everything as a PDF back to them.
It was tough because whoever entered the data had 15 addresses in one field on one spreadsheet and what you see on most other spreadsheets.
The whole time I thought MAYBE Numbers would have a work-around that didn't require me having to make a zip code column, nonetheless.
Thank you.
RC
Does this make sense?
Hey Ralf, give me a bit to create dummy names.
Thanks,
RC
Most helpful.
Thank you,
RC
Using Categories to Populate Locations
