Help with rearranging spreadsheet

[myzopoda]

Hi everyone. I have been struggling for a few months to rearrange the data in a spreadsheet. I'm sure it should be simple but it's beyond me! I've tried Pivot Tables, Lookup and Index/Match amongst other things but with no success. I've attached a copy of the spreadsheet . Basically i have a schedule showing survey dates which are divided into duk or dawn in the next column. Then I have columns of surveying, the data being the locations at which they should be surveying.
Image10.jpg

i want to rearrange it in a separate sheet so that i get the address in the first column, date in the second, dusk/dawn in the third and the surveyors name in the fourth column.

Image11.jpg

Ideally i don't want any empty rows or cells.
Can anyone help this Excel rookie!
many thanks

[sandy666]

is that what you want?

Site	Date	Dawn/Dusk	Surveyor
Stowe	11/05/2023	Dawn	Brian
Stowe	11/05/2023	Dawn	Steve
Broder	12/05/2023	Dawn	Brian
Broder	12/05/2023	Dawn	Steve
Battlefield	12/05/2023	Dawn	Helen
Battlefield	12/05/2023	Dawn	Mark
Battlefield	12/05/2023	Dusk	Helen
Battlefield	12/05/2023	Dusk	Mark
Jamestown	13/05/2023	Dusk	Brian
Jamestown	13/05/2023	Dusk	Steve
Stowe	14/05/2023	Dusk	Helen
Stowe	14/05/2023	Dusk	Mark
Battlefield	16/05/2023	Dusk	Brian
Battlefield	16/05/2023	Dusk	Steve
Stowe	17/05/2023	Dusk	Brian
Stowe	17/05/2023	Dusk	Steve
Broder	18/05/2023	Dusk	Brian
Broder	18/05/2023	Dusk	Steve
Battlefield	19/05/2023	Dusk	Helen
Battlefield	19/05/2023	Dusk	Mark
Jamestown	20/05/2023	Dusk	Brian
Jamestown	20/05/2023	Dusk	Steve
Broder	20/05/2023	Dusk	Helen
Broder	20/05/2023	Dusk	Mark
Broder	22/05/2023	Dawn	Helen
Broder	22/05/2023	Dawn	Mark
Jamestown	22/05/2023	Dusk	Steve
Jamestown	22/05/2023	Dusk	Helen
Jamestown	22/05/2023	Dusk	Mark

see Power Query in the attachment

[myzopoda]

Thanks Sandy - that's exactly it - but I'm not sure how you did it? How do create the sheet so that when i make a changes (new locations for surveyor to attend etc) it updates?

[sandy666]

update your source (blue) table then go to the green table, right click and choose Refresh/Reload, every chsnge in blue table you need to confirm by Refresh green table

if you want to see HOW TO... use Data tab / Show Queries & Connections / double click on the table on the right side (it will open Power Query Editor) and you see steps on the right side

in PQEditor:
if you want to see whole M (M is a language of Power Query) choose Home / Advanced Editor

[AliGW]

Also useful to know, I think: right-click the query and select Properties > you can set the query to refresh automatically every n minutes and/or every time you open the workbook.

[myzopoda]

Wow - thanks Sandy - you're a genius!
If i wanted to add in another surveyor column what do i need to change to make this work? I quickly messed about and inserted Cathy between two existing surveyors but it didn't update.

[AliGW]

Did you refresh the query?

[windknife]

Try this,
Sheet2!A2

Please Login or Register  to view this content.

copied down.

Sheet2!B2

Please Login or Register  to view this content.

copid down and across to C Column.

Sheet2!D2

Please Login or Register  to view this content.

copied down.

[sandy666]

here is modified M and you can add more Surveyors as well as values in them

btw. in the first post you didn't mention you want to add more surveyors or I am blind

[myzopoda]

Sorry Sandy, i didnt mention it to start with as i wanted to keep it simple and to see if its possible. You've saved me a lot of work and I'm very grateful!

[AliGW]

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[sandy666]

Sorry Sandy, i didnt mention it to start with as i wanted to keep it simple and to see if its possible. You've saved me a lot of work and I'm very grateful!

next time don't be so frugal with information and write everything in detail
You are welcome

[myzopoda]

That's really helpful - thanks Ali!

[AliGW]

It might have been, however you didn't mark the thread as SOLVED.

Please do so now.