Reformat spreadsheet so every owner has it's own line

[ryangodammit]

Hi,

I have a spreadsheet of property addresses that I need to change the format of, but I'm not sure how to do it without manually going through it line by line.

On the left hand column is the property address, next is company name, then 1st owner, then 2nd owner if any, then third, then postal address.

I would like to create a new line for every single owner, ie if the property has three owners (including the company) the property will be listed three times, each with only one owner name associated to it.

Is is possible to do this with some sort of conditional formula? I'm imagining if it is, it will be a multi-step process.

Thanks so much for any help.

Regards
Ryan

[TMS]

Is is possible to do this with some sort of conditional formula?

Probably not. I think you'd need VBA. Unless there are a lot of properties, it might be easier to replicate the entries manually.

That said, it's a lot of repetitive data with lots of opportunities for typing errors to creep in.

If it were me, I’d allocate each property a unique reference and have a "master" list of properties. Then, on a second sheet, I’d list the owners with the unique property code. You could also include things like tenancy start and end dates. You could use VLOOKUP or INDEX/MATCH to bring across the property details (avoiding the duplication of static data).

Just my thoughts ...

Out of interest, how many properties are there?

[ryangodammit]

I forgot to include the attachment! I'm so embarrassed!

Here it is, it will give you a much clearer picture of what I'm saying.

On my actual document there are about 160 lines in total

Ryan

[ryangodammit]

I came up with a pretty simple solution, thanks mainly to your post TMS and me just letting it sit for a few days.

I simply numbered every property then as you said created new sheets that included the same property number and simply copied one of the corresponding owner columns. I created a new sheet for each owner column and tied it back to the original property number. I was then able to make one big long sheet with all the relevant details. When I then ordered the sheet based on property number it bought everything tidily back together again.

Thanks for your help
Ryan

[TMS]

You're welcome.

Not quite what I had in mind but, whatever, if it works for you, it works