Need help with VBA Code

[Einz]

I’m a noob with Macros but that hasn’t stopped me from learning and trying to understand code.

I have a table that has various columns ranging from b thru Al for each row. I need to be able to add a row for each column since each column should have its own row.

I hope I’m making sense. I’m including a picture of what I’m dealing with.

Thanks in advance ?

 

[Alex Blakenburg]

Are you after a result or are you using this to learn VBA ?
If you are after a result, then you could do this in Power Query in only a couple of steps using the PQ unpivot function.

If you are doing it to learn vba then give us

• the sheet name your data is on,
• the sheet name you want the output to go to
• whether you want blank cells in your output
• whether you want zero value cells in your output

An XL2BB or some other copyable version of some sample data would be helpful and saving us having to recreate data at our end.

 

[Einz]

Are you after a result or are you using this to learn VBA ?
If you are after a result, then you could do this in Power Query in only a couple of steps using the PQ unpivot function.

If you are doing it to learn vba then give us

• the sheet name your data is on,
• the sheet name you want the output to go to
• whether you want blank cells in your output
• whether you want zero value cells in your output

An XL2BB or some other copyable version of some sample data would be helpful and saving us having to recreate data at our end.

I am after the result which I would be doing this on a monthly basis.

I haven't worked with Power Query and to be honest never heard of it lol but whatever method is best is really appreciated.
(Wow!!!! Just learned about Xl2bb!!!!!)

Example.xlsx
	A	B	C	D	E	F	G	H	I
1	Original Data						Desired Data
2
3	Account	Dept 1	Dept 2	Dept 3	Dept 4		Account		Dept 1
4	1000	100.00	25.00	200.00	300.00		1000	Dept 1	100.00
5	2000	50.00	75.00	50.00	200.00		1000	Dept 2	25.00
6	3000	200.00	300.00	100.00	50.00		1000	Dept 3	200.00
7	4000	300.00	200.00	25.00	75.00		1000	Dept 4	300.00
8	5000	75.00	50.00	75.00	100.00		2000	Dept 1	50.00
9	6000	25.00	100.00	300.00	25.00		2000	Dept 2	75.00
10							2000	Dept 3	50.00
11							2000	Dept 4	200.00
12							3000	Dept 1	200.00
13							3000	Dept 2	300.00
14							3000	Dept 3	100.00
15							3000	Dept 4	50.00
16							4000	Dept 1	300.00
17							4000	Dept 2	200.00
18							4000	Dept 3	25.00
19							4000	Dept 4	75.00
20							5000	Dept 1	75.00
21							5000	Dept 2	50.00
22							5000	Dept 3	75.00
23							5000	Dept 4	100.00
24							6000	Dept 1	25.00
25							6000	Dept 2	100.00
26							6000	Dept 3	300.00
27							6000	Dept 4	25.00
Sheet1

 

[Alex Blakenburg]

I will show what it looks like using Power Query first. If you are still interested in doing it this way I can either send you the code that you can drop into Power Query or talk you through the 4 steps (after creating the table)

Power Query needs to create a table for your original data.
Are you comfortable with Excel Tables ?
Once you have it set up, to refresh the table you can just zero out your table and copy in the new data, then hit refresh on the table.


Power query results screen.

Power Query screen - 4 steps

 

[Einz]

I will show what it looks like using Power Query first. If you are still interested in doing it this way I can either send you the code that you can drop into Power Query or talk you through the 4 steps (after creating the table)

Power Query needs to create a table for your original data.
Are you comfortable with Excel Tables ?
Once you have it set up, to refresh the table you can just zero out your table and copy in the new data, then hit refresh on the table.


Power query results screen.

View attachment 56980

Power Query screen - 4 steps

View attachment 56981

Yes definitely interested if this is something that I can manipulate if I have more columns than my example does, and yes I've dabbled a bit with tables.

 

[Alex Blakenburg]

I can give you the M Code to just drop into the Power Query Advanced editor but here is the step by step instructions if you want to give it a try first.

Convert the Data Table to an Excel Table

• Select a cell in the data range and hit Ctrl+T
  Select My Table has headers
• Under Table Design > Give it a Table Name (box far left)
  I suggest you use the same name I have for now which is tblData

Power Query (PQ) steps

• With a cell inside the table selected
  in Excel > Data > From Table
• This will put you in PQ
• In the navigation pane on the right
  • Delete the Changed Type step
  • In the name box give it a different name (the output table will use this name)
• With the 1st column (Account) seleted.
  Transform > Unpivot Columns > Unpivot Other Columns
• You are nearly done
• Hit ctrl+A to select all the cells in the data grid and go
  Tranform > Detect Data Type
• Click on the 123 of the Value field and I changed it to decimal. Click on “replace current” when it pops up.
  (if you know you will never have more than 4 decimal places you could select currency)
• Double click on any column headings you want to change and type in the heading you want.
• Home > Close and Load To > Select
  • Table
  • Existing worksheet
  • Navigate to where you want to put it

As long as you put the new data in the same table, you just need to hit refresh on the query.

 

[Einz]

I can give you the M Code to just drop into the Power Query Advanced editor but here is the step by step instructions if you want to give it a try first.

Convert the Data Table to an Excel Table

• Select a cell in the data range and hit Ctrl+T
  Select My Table has headers
• Under Table Design > Give it a Table Name (box far left)
  I suggest you use the same name I have for now which is tblData

Power Query (PQ) steps

• With a cell inside the table selected
  in Excel > Data > From Table
• This will put you in PQ
• In the navigation pane on the right
  • Delete the Changed Type step
  • In the name box give it a different name (the output table will use this name)
• With the 1st column (Account) seleted.
  Transform > Unpivot Columns > Unpivot Other Columns
• You are nearly done
• Hit ctrl+A to select all the cells in the data grid and go
  Tranform > Detect Data Type
• Click on the 123 of the Value field and I changed it to decimal. Click on “replace current” when it pops up.
  (if you know you will never have more than 4 decimal places you could select currency)
• Double click on any column headings you want to change and type in the heading you want.
• Home > Close and Load To > Select
  • Table
  • Existing worksheet
  • Navigate to where you want to put it

As long as you put the new data in the same table, you just need to hit refresh on the query.

This is so crazy!!!! I am in awe! Thank you very much, sir. This worked like a charm. Probably easier than going the VBA route. Thanks!

 

[Alex Blakenburg]

That's great to hear. You have made my day.