grouping data

  • Thread starter Thread starter billrebels4
  • Start date Start date
B

billrebels4

Hello everyone-

I have to go through about 15,000 addresses in an excel sheet and pull
out the addresses that are in groups of 6 or more per zip code.. does
anyone know how i could get the sheet to do this automatically???

i want to pull out all of the addresses (which are multiple fields:
first name, last name, street, city, state, zip.) that have 6 or more
in the same zip code...

can anyone help me do this?/?

Thanks, BR4
 
Assuming all your data has the Zip Codes in the same Column, select all the data and then
sort by Zip Code. Now, assuming your Zip Codes are all in Column F, starting in F2, then
select the entire set of Zip codes, eg F2:F15000, and do Insert / name / Zips.

Now, in G2 put the following formula:-

=COUNTIF(Zips,F2) and then copy that down to F3:F15000

Now select A1:G15000 and do Data / Filter / Autofilter

Click on the dropdown over Col G and do Custom / Greater or Equal to 6 to show you just
those records that have 6 or more entries in the same Zip - OR - click Custom / less than
6, then do select everything you can see, and do edit / Go To / Special / Visible Cells
only, then do Edit / delete / Entire Row. Undo the filter and you will only have left
what you needed.
 
Ken- Thank you thank you thank you... This has helped me more than you
can ever know... One more thing though: The zips have a 4 digit routing
number exp:39180-1114.... is there anyway I can sort and count by 39180
and make the computer disregard the four digits at the end?? those dont
matter when i seperating by zip code... Thanks... BR4
 
Sure, you can do one of two things:-

1) Separate the Zip and the routing number into 2 cells. To do this, make sure you have
a blank column to the right of the Zips, then select all the zips and do Data / Text to
Columns / Delimited / In the Other field put a - and then tick it. Hit OK and you will
now have them in their own cells. Select the entire set of data and sort on the zip.

2) Create a helper column with nothing but a zip in it, whilst leaving your original data
untouched. Assuming your first zip in F2, in G2 put the following formula and then just
copy it dowm:-

=LEFT(F2,LEN(F2)-5) which will give you the Zip in text format in each cell, or

=--LEFT(F2,LEN(F2)-5) which will give you the Zip in numberical format. Not that I can
think of a reason why a zip would need to be numeric, but just in case. :-)

Select the entire set of data and sort on the zip.
 
Back
Top