Guys I am new to excel but I got a complex situation at work. Hope someone can help.
I want a formula where if I change the date in one column, then the data is divided according to the formula used on those cells.
eg. If date is 1st October 2021, volume in cell A is 60 which is divided in cell B and C as per formula B/12*9 and C/12*3 respectively. But suppose the data changes to 1st December 2021, the data should be divided in cell B and C as B/12*11 and C/12*1 respectively. Dividing basically month vise.
I find it hard to understand what you are going for.
What I read is that you have a cell with a date in it.
Cell A2 (row 2 as an example) has value 60 and in B2 the formula =B/12*9, which isn't a proper formula. Same goes for C2.
If you want to know how to get the number 9 and 3 when the date is 1st October 2021 located in cell D1 (for example), then try this:
=MONTH(D1)-1 to get to 9
=12-(MONTH(D1)-1) to get to 3
Yes, it was a mistake and I wanted to divide the volume un A to B and C column. I was able to come up with the same formula. Thank you very much for your quick reply.
But I have now a new challenge. Can you please help me.
Suppose I have one row with data in column A,B,C,D,Eand F.
Now suppose the name in A and B has to be repeated 4 times in 4 rows but C D and E will come in different rows.
Eg I want my one row to be divided in 5 rows with the following combination.
1st row A B C
2nd row A B D
3rd row A B E
4th row A B F
I have like this thousands of rows for which I would like to then repeat this formula so that same thing happens to every different row
It not clear to me though, what you starting situation looks like.
If it looks like this:
Then use this code:
Sub RunMe()
Dim x, lRow As Long
lRow = Range("A" & Rows.Count).End(xlUp).Row
For x = 1 To lRow Step 5
Range("A" & x, "B" & x).Copy Range("A" & x + 1, "B" & x + 4)
Range("C" & x, "F" & x).Copy
Range("C" & x + 1).PasteSpecial Transpose:=True
Next x
Application.CutCopyMode = False
End Sub
When your data looks like this:
Then use this code:
Sub RunMe()
Dim x, lRow As Long
lRow = Range("A" & Rows.Count).End(xlUp).Row
For x = 1 To lRow * 5 Step 5
Rows(x + 1 & ":" & x + 4).Insert
Range("A" & x, "B" & x).Copy Range("A" & x + 1, "B" & x + 4)
Range("C" & x, "F" & x).Copy
Range("C" & x + 1).PasteSpecial Transpose:=True
Next x
Application.CutCopyMode = False
End Sub
If you are unfamiliar with codes then read the following:
How to implement and run a code:
- From Excel hit Alt + F11 to open the “Microsoft Visual Basic” window.
- Go to the top menu in the newly opened window > Insert > Module.
- Paste the code in the big white field.
- You can now close this window.
- Back at Excel, hit Alt + F8 to display the available macro’s.
- Double-click the macro you wish to run.
NOTE: macro’s cannot be reversed using the blue arrows. Always make sure you save your file (or create a back up to be entirely sure) before running a code, so you can re-open your file if something unforeseen happens or you want to go back to the situation before the code was run.
Yes, it was a mistake and I wanted to divide the volume un A to B and C column. I was able to come up with the same formula. Thank you very much for your quick reply.
But I have now a new challenge. Can you please help me.
Suppose I have one row with data in column A,B,C,D,Eand F.
Now suppose the name in A and B has to be repeated 4 times in 4 rows but C D and E will come in different rows.
Eg I want my one row to be divided in 5 rows with the following combination.
1st row A B C
2nd row A B D
3rd row A B E
4th row A B F
I have like this thousands of rows for which I would like to then repeat this formula so that same thing happens to every different row
Could you please help.
Best Regards,
Anup