Create new rows from existing columns

[Dwjenkins]

Hi Everyone,

I was wondering if you could possibly help with something that I'm going to have to do monthly and figure there has to be a better way than copying and pasting. I've done some searches but don't seem to be able to get the term correct as it always offers me the same results.

I receive a file in the following format and I want to create a row for each of the last four columns to look like the second table I've posted below. If it's possible, I don't know whether it would be better to do it via functions or VBA or do I just bite the bullet with copying and pasting?

Reservation

Channel Reservation Id

Confirmed At

Check In

Check Out

Source

Status

Guest Name

# Of Nights

# Of Guests

Listing Nickname

Total Host Payout

Accommodation Fare

Channel Fee

Cleaning Fee

BC-06yR73NN

123​

24/06/2023​

30/06/2023​

02/07/2023​

Booking.com

confirmed

Darth Vader

2​

4​

24A-2-1-5

330​

270​

-49.5​

60​

BC-4OjxR4Y7

456​

18/04/2023​

30/06/2023​

02/07/2023​

Booking.com

confirmed

Luke Skywalker

2​

2​

7-3-1-6

411.5​

288.75​

-61.73​

65​

BC-686L5yVNL

789​

11/06/2023​

13/06/2023​

16/06/2023​

Booking.com

confirmed

Han Solo

3​

2​

1-2-4-1

330​

225​

-49.5​

60​

How I want it to look.

Reservation

Channel Reservation Id

Confirmed At

Check In

Check Out

Source

Status

Guest Name

# Of Nights

# Of Guests

Listing Nickname

Amount

Item

BC-06yR73NN

123​

24/06/2023​

30/06/2023​

02/07/2023​

Booking.com

confirmed

Darth Vader

2​

4​

24A-2-1-5

330​

Total Host Payout

BC-06yR73NN

123​

24/06/2023​

30/06/2023​

02/07/2023​

Booking.com

confirmed

Darth Vader

2​

4​

24A-2-1-5

270​

Accommodation Fare

BC-06yR73NN

123​

24/06/2023​

30/06/2023​

02/07/2023​

Booking.com

confirmed

Darth Vader

2​

4​

24A-2-1-5

-49.5​

Channel Fee

BC-06yR73NN

123​

24/06/2023​

30/06/2023​

02/07/2023​

Booking.com

confirmed

Darth Vader

2​

4​

24A-2-1-5

60​

Cleaning Fee

BC-4OjxR4Y7

456​

18/04/2023​

30/06/2023​

02/07/2023​

Booking.com

confirmed

Luke Skywalker

2​

2​

7-3-1-6

411.5​

Total Host Payout

BC-4OjxR4Y7

456​

18/04/2023​

30/06/2023​

02/07/2023​

Booking.com

confirmed

Luke Skywalker

2​

2​

7-3-1-6

288.75​

Accommodation Fare

BC-4OjxR4Y7

456​

18/04/2023​

30/06/2023​

02/07/2023​

Booking.com

confirmed

Luke Skywalker

2​

2​

7-3-1-6

-61.73​

Channel Fee

BC-4OjxR4Y7

456​

18/04/2023​

30/06/2023​

02/07/2023​

Booking.com

confirmed

Luke Skywalker

2​

2​

7-3-1-6

65​

Cleaning Fee

BC-686L5yVNL

789​

11/06/2023​

13/06/2023​

16/06/2023​

Booking.com

confirmed

Han Solo

3​

2​

1-2-4-1

330​

Total Host Payout

BC-686L5yVNL

789​

11/06/2023​

13/06/2023​

16/06/2023​

Booking.com

confirmed

Han Solo

3​

2​

1-2-4-1

225​

Accommodation Fare

BC-686L5yVNL

789​

11/06/2023​

13/06/2023​

16/06/2023​

Booking.com

confirmed

Han Solo

3​

2​

1-2-4-1

-49.5​

Channel Fee

BC-686L5yVNL

789​

11/06/2023​

13/06/2023​

16/06/2023​

Booking.com

confirmed

Han Solo

3​

2​

1-2-4-1

60​

Cleaning Fee

Appreciate any input or guidance that you can offer!

 

[Skybluekid]

What version of excel are you using? This can be done in Power Query.

Firstly, I created a table using the orginal table
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content]
Change Type = Table.TransformColumnTypes(Source,{{"Reservation", type text}, {"Channel Reservation Id", type text}, {"Confirmed At", type text}, {"Check In", type text}, {"Check Out", type text}, {"Source", type text}, {"Status", type text}, {"Guest Name", type text}, {"# Of Nights", type text}, {"# Of Guests", type text}, {"Listing Nickname", type text}, {"Total Host Payout", type text}, {"Accommodation Fare", type text}, {"Channel Fee", type text}, {"Cleaning Fee", type text}})
Unpivoted Only Selected Columns = Table.Unpivot(#"Changed Type", {"Total Host Payout", "Accommodation Fare", "Channel Fee", "Cleaning Fee"}, "Attribute", "Value")

 

[Dwjenkins]

Office 365.

Excellent, I'll have at look at PowerQuery as I've never used it before and feed in!

 

[alansidman]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

 

[Skybluekid]

Office 365.

Excellent, I'll have at look at PowerQuery as I've never used it before and feed in!

As @alansidman has mentioned 365 comes with Power Query installed.
Please see the below link on a great tutorial on Power Query

Please could you mark as a solution for the rest of the forum.