Copy selected columns in one sheet to another sheet

[MaxJock]

Hi, newbie here and a beginner/intermediate with Excel, so I'm looking for some help please.

I have a sheet for dog club members and their dogs, one row per member. Each member has name, address, contact etc. plus details for up to 4 of their dogs.

What I'd like to do is have some of their details from this sheet copied to a new sheet where their name is repeated for each of their dogs. First name in column A, last name in column B, class name in column C, dog name in column D.

e.g from something like this:

Date

First Name

Last Name

Street Address

Dog 1 Name

Dog 1 Breed

Dog 1 class

Dog 2 Name

Dog 2 Breed

Dog 2 class

Dog 3 Name

Dog 3 Breed

Dog 3 class

4/01/2023

Ju

Smith

4th st

kay

kelpie

3

beauty

staffy

2B

jock

BC

1A

10/01/2023

Joe

Blo

2nd ave

max

collie

1D

22/02/2023

clark

kent

5th st

zoom

GSP

Puppies

zulu

poodle

1E

 To this:

First Name	Last Name	Current Class	Dog Name
Ju	Smith	3	kay
Ju	Smith	2B	beauty
Ju	Smith	1A	jock
Joe	Blo	1D	max
clark	kent	Puppies	zoom
clark	kent	1E	zulu

Thanks!

[#2 · Accepted solution]

@MaxJock 

An alternative could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.

The data layout in the screenshot and in the attached file is for illustration. You can place the green result table to the right of the blue table or in another worksheet.

[#3]

@OliverScheurich 

Yes! that works great, thank you so much!