Software

Ask something
Discussion Locked

Sorting for multiple data in Excel

0 helpful
4 replies

by swdrake1

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have a large sheet of mailing data for doing a mail merge (>17,000 lines) but I would like to sort these many lines to show me only 125 zip codes among all the lines of data and zip codes. Each line of data has a column with it's zip code but I only need at this time 125 of the many more zip codes in the sheet. Is there a way to sort them to the top of the list? Or in some way "mark" them so I know which ones I want?

4 replies

by chitosunday

You can use data sort but before you do this, you need to fill up the empty cells with the data on top. click any cell in the area you want to sort. Then click the edit menu goto special blank. in the top blank for example you have a data in cell a1 and a blank cell below , in cell a2 type the formula =a1 then press ctrl enter . It will fill in all empty cells with the data on top which is not empty. Then you can sort it now

by bschaettle

You can use the MATCH function to create a sorting column.

[1] in your spreadsheet, create a new worksheet
[2] on the new worksheet, enter your 125 "special" ZIP codes in Column A.
[3] Create a named range:
[3.1] select Column A and click on Insert+Name+Define
[3.2] On the dialog box that pops up, type in SpecialZIPs, then click on OK.
[4] go back to the original worksheet
[5] in an empty cell in Row 1, enter this formula:
=MATCH(F1,SpecialZIPs)
>> this assumes that the ZIP code is in column "F"...adjust as needed.
[6] copy the formula down the entire column

Now you'll see that the cells in this column display either a number or "#N/A". If you sort on this column, the rows that have your 125 "special" ZIP codes will be at the top. To make other ZIP codes sort to the top, just change the ZIP code list you created on the new worksheet, and re-sort the data.

This discussion is closed to new replies.