Excel Help

[Adam]

Afternoon - I am hoping one of the resident Excel gurus can assist me with the following.
I need a way of being able to complete what I want to achieve in bulk rather than doing it all manually - hopefully :smile:

I have a list of data, example below, where I need to get the unique site name added as 3 new rows with different text strings at the end of the site name below an existing row that's already on my sheet, I need to do this for 200+ existing rows, so with 3 new ones x 200 basically - I hope that makes sense but the below pic should help....



So where you can see I've got to 157 - Lancaster and then there are 3 different rows underneath for Survey, Survey results and Survey Approval with 157 Lancaster at the beginning, I need to continue that format for all of the sites below that starting with 375 - Medway, they each need to have the same 3 sub rows with the relevant site name and code in front again. The dashes (-, -- and ---) are also relevant

Cheers
Adam

 

[HarvB]

So for your Lancaster example you want to get the following?
-- 157 - Lancaster goes to ->

-- 157 - Lancaster
--- 157 - Lancaster - Survey ...
--- 157 - Lancaster - Survey ...
--- 157 - Lancaster - Survey ...

 

[Adam]

No, Lancaster is done in the example above, what I need is for the ones below Lancaster starting with Medway, so it would be...

-- 375 - Medway (which already exists on the sheet)
--- 375 - Medway - Survey/WIC Install (new row needed)
--- 375 - Medway - Survey Results Uploaded (new row needed)
--- 375 - Medway - Survey Approval (new row needed)

Hope that makes sense

 

[HarvB]

Yeah, that's kinda what I meant! :tongueout:

Try this, I've uploaded a spreadsheet with an example set, but what it does is use string manipulation to create a single row with 4 columns - one each for the survey bits and one header. We can then define it as a range, and use a transpose function to then convert that into a single column with multiple values.

The spreadsheet is here: https://a.uguu.se/JBbISGaUcIPb_RowExpander.XLSX
The details on the transpose are here (I'm using the transpose with a formula): https://www.extendoffice.com/docume...pose-multiple-columns-into-one-column.html#a1

 

[Adam]

Yeah, that's kinda what I meant! :tongueout:

Try this, I've uploaded a spreadsheet with an example set, but what it does is use string manipulation to create a single row with 4 columns - one each for the survey bits and one header. We can then define it as a range, and use a transpose function to then convert that into a single column with multiple values.

The spreadsheet is here: https://a.uguu.se/JBbISGaUcIPb_RowExpander.XLSX
The details on the transpose are here (I'm using the transpose with a formula): https://www.extendoffice.com/docume...pose-multiple-columns-into-one-column.html#a1

Thanks mate, appreciated.

Think I need to get my head around it first haha

 

[Adam]

What's going to be the easiest way to get the columns populated with the site name and code that changes each time?

 

[HarvB]

If you copy+paste the site names into column B (yellow), then highlight the formulas which generate the sub-sections, and in the bottom-right corner hover over the square it'll change the mouse to the fill tool - this fills sites as you drag it (double-click for auto-fill) and changes the cell refs automatically.

Once that's done do the same highlight+fill on column H to convert the rest of the range to a column.

 

[Adam]

Cheers mate, will give it a go