Convert time planning to single line

[Hemmerl]

Hello,

I have a planning file that is filled in by the different departments, that i need to convert to a .csv file to be able to upload it to our payroll system.

The planning sheet looks like the tab data and in the result tab you can see how the .csv file needs to look.

Anybody that can help me with a macro or another way to make this conversion?

Thanks & regards

Luk

[nankw83]

Hello Hemmerl & welcome to the forum,

Try the below code. This will insert a new sheet with the expected results, if you wish the output in a certain sheet, let me know to amend

Please Login or Register  to view this content.

[Hemmerl]

Hello nankw83, and thak you for the help and the fast reply.

I tried your code, and this is the output i get:
pn date code
1 04/05/2020 a
1 05/05/2020 c
1 d b
2 04/05/2020 b
2 05/05/2020 b
2 d c
3 04/05/2020 c
3 05/05/2020 a
3 d d
4 04/05/2020 a
4 05/05/2020 d
4 d a
5 04/05/2020 d
5 05/05/2020 a
5 d b


So almost there. 1 more question, as you can imagine, my original file is a lot bigger, how do i need to adapt the code to take into account for the longer ranges? (about 2300 people, and a planning for several months.

Thank you very much in advance for your help, as i really am not good with VBA

kind regards

Luk

[nankw83]

The code should work in any range. In your sample file sheet "data" cell D1, this should be 6-May & it's a typo as d or this is intentional ?

[Eastw00d]

Hi Hemmerl,
you get this, because D1 in Sheet Data = "d".

Cheers
Erwin

[Hemmerl]

Hi All,

Indeed it was a typo in my file.

Thank you very much for the help, the code works perfectly, you saved me days of manual work.

Cheers
Luk

[nankw83]

Glad to help & you are welcome


If the provided solution takes care of the original question, please mark the thread as 'SOLVED' from the Thread Tools menu at top of the page & consider adding reputation(s) to those who have help by clicking the star under their usernames