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):

p-1 12 52 76 98 ABC NE x y
p-2 85 32 51 62 66 78 90 DEF NE x y
p-3 59 GHI NW x y
p-4 9 111 884 273 860 349 683 309 797 323 634 683 790 800 JKL NW x y
What I'd like:
15 p-1 ABC NE x y
52 p-1 ABC NE x y
76 p-1 ABC NE x y
98 p-1 ABC NE x y
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. Thanks

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