Most valuable professional
  • Hot Topics

Excel Creating multiple rows from multiple unique numbers contained in a single cell

Hi, I'm trying to figure out some less painful ways of preparing a table so that it is ready for a spatial join to GIS data. I'm working with a table that has unique entries based on permit number. But each permit number can apply to multiple claims. The claims are all contained in a single column (so for each permit, in a single cell). What I would like is to separate out all of the claim numbers (I can do this by splitting to new columns) and then to have them as the unique entry (one row for each claim), with all of the relevant data duplicated for each claim that it applies to. Simplification of existing table (it is much larger than this): PERMIT p-1 p-2 p-3 p-4 What I'd like: CLAIM 15 52 76 98 So the resulting table will be much larger, having as many rows as there are unique claim numbers in the original table. I started doing this by turning the claims cell into individual columns, and then copying those and transposing them in a new sheet. Then copying all the other data and pasting them beside each claim. This takes a long time given the size of the table and the number of claims that some permits apply to. Any tips are greatly appreciated.

This question generated 16 answers. To proceed to the answers, click here.

This thread is current as of March 20, 2017.

For more resources for Microsoft Excel:

This article includes the following tags:

  • Excel
  • Microsoft Excel