macro or table? to cut/paste dates vertically

So i used my whole day on this, trying out with macro guides etc., guess its time to ask !

I have a bunch of numbers, each number is a registered person.
This person has some different activities with start and end dates.

When i collect my scheme it will come out like this.
1.PNG

But i need som tool to make it like this
2.PNG

So it will copy two rows to the right of a person-number and insert it after the two first start/end date.
(its important they come in order from left to right, not by date but at the same way they go down vertical)

If it then can delete the rows the dates were copied from so it will end up as last picture it will be a big plus !

Hope someone can help.

Hi, welcome to the forum

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

Until then maybe ...

Not with macro but formula wise maybe this? When finished copy F2:M6 and paste values only back into F2:M6. Then delete columns A:C.

Copy / paste persons into column E. Apply "Remove duplicates".

In F2:M6

Formula:

Please Login or Register  to view this content.

A

B

C

D

E

F

G

H

I

J

K

L

M

1

Person

Start date

End date

Person

Start date

End date

Start date 2

End date 2

Start date 3

End date 3

Start date 4

End date 4

2

3789

22-Jul

25-Jul

3789

22-Jul

25-Jul

26-Jul

29-Jul

30-Jul

5-Aug

3

3789

26-Jul

29-Jul

2222

22-Jul

25-Jul

26-Jul

29-Jul

30-Jul

5-Aug

4

3789

30-Jul

5-Aug

1111

22-Jul

25-Jul

26-Jul

29-Jul

5

2222

22-Jul

25-Jul

7567

30-Jul

5-Aug

6

2222

26-Jul

29-Jul

8963

1-Jun

6-Sep

7

2222

30-Jul

5-Aug

8

1111

22-Jul

25-Jul

9

1111

26-Jul

29-Jul

10

7567

30-Jul

5-Aug

11

8963

1-Jun

6-Sep

I tried to use your formula #FlameRetired, but i guess that im more of a newb to this than i thought myself

I forgot to mention regional settings. My bad My apologies.

My guess is your regional settings use semi-colons as argument separators and not commas as my formula above. The attached upload will correct for that at your end.

In the latest example there are some extra steps.

In the 'Input' sheet we need to address the blank cells between the "person numbers" by filling those in. Follow these steps. It's faster than hand typing them in yourself: (This is already done in the attached.)

• Select A2:A20 in the 'Input' sheet.
• Press the F5 function key on the upper keyboard.
• Click the "Special ..." in the lower left corner.
• Click the "radio button" next to "Blanks"
• Click OK.
• You will find all the blank cells selected.
• Without touching anything else type "=" (without the quotes) followed by the UP arrow key. Now while pressing and holding down the Ctrl key hit Enter. That will correctly fill in all the blanks with "person numbers".

Copy that list and paste into column A of 'Output'.
While still selected apply the Remove Duplicates feature.
You will have a list of unique "person numbers".

Find this formula in B2 filled down and across to I10. This should look different in the workbook uploaded below if I am correct about regional settings.

Formula:

Please Login or Register  to view this content.

Thank you very much!

Worked like it should and just how i wanted it !
And thanks for the extra tips with the f5 function!

Reputation your way sir.!

Edit: have no idea how to mark this solved! ^^

Glad to help. Thanks for the feedback and rep.

Re: Marking the thread Solved ...

Select Thread Tools from the menu link above your first post. Mark SOLVED. Thanks for asking.