How to change colums to values

[CoachJohn]

Not sure if the title represents the question I am asking but here is the challenge. I have a spreadsheet with has the value "Name" and each month as the column header. In each row it will have the persons name and under each month header it will have their sales amount. I have included an example in this thread.



My goal is to upload this spreadsheet into access with the columns name, month and sales amount so I can use this temporarily to compare with other tables.



The first tab shows how the current data is formatted. The second tab show how I would like to possibly store it in access.


Prepping data is not my strong point, so feel free to give me other ideas. Once I get it into access, I can write sql all day. Can I get some ideas on how to go from tab1 to tab2?



Thanks!

CoachJohn

[oeldere]

With the macro below.

See the attached file.

Please Login or Register  to view this content.

[CoachJohn]

Thank you Oeldere!

I see how that works. Very nice. I am not a vb, but I do partially understand this. Now if I would like to add the id before the date, what would I need to change on this macro?

Thank you again! This is helping a lot.

CoachJohn

[martindwilson]

with a few functions

[CoachJohn]

Never mind I figured it out. Thanks for your help!!

[oeldere]

@CoachJohn

Since you got help from several members, it is good to refer to whom you responding.

Thanks for the reply.

Glad I could help you.

[CoachJohn]

My bad oeldere! I thought I mentioned your name.

I took your solution and modified it to add more header information. I am not real familiar with functions. I have recorded some basic macros but never using VBA. In my new role, this will really come in handy so I am going to learn so I can help myself and others!

Happy Fathers Day!

Sincerely,
CoachJohn

[oeldere]

It was a more general statement.

thanks for the reply.

glad i could help you.