Text to next Cell...

[Stan_C]

I came up with a formula for this and I am probably over thinking this but, it fails about 500 cells down.

Col. (A) I have Item data that are names: Col. (B) I have data but would like b1=a1, b16=a2, b31=a3

So with column (B) +15 rows to = (A) +1

2% Milk	2% Milk
Ambrosia	Regular
American Chop Suey	3-4gm Na (NAS)
Apple Pie	2gm Na
Bacon Strip	Low Fat/Low Chol
Baked Beans	Fiber Restricted
Baked Chicken Breast	Consistent Carb
Baked Ham Pineapple Sauce	Cardiac Diabetic
BBQ Pork on Bun	2 gm K
Beef Barley & Veg Soup	Lactose Restricted
Beef Lasagna	Vegetarian LactoOvo
Beef Liver & Onions	Gluten Restricted
Braised Cabbage	Renal CSC
Bread Pudding	Small Portion
Broccoli	Large Portion
Broccoli & Cheese Quiche	Ambrosia
Brown Bread	Regular
Brownie	3-4gm Na (NAS)
Brussels Sprouts	2gm Na
Carnival Cake	Low Fat/Low Chol
Carrot Bisque	Fiber Restricted
Carrot Raisin Salad	Consistent Carb
Cheese Omelet	Cardiac Diabetic
Chicken Patty	2 gm K
Chicken Rice Soup	Lactose Restricted
Chicken Tenders	Vegetarian LactoOvo
Chilled Pineapple	Gluten Restricted
Cole Slaw	Renal CSC
Coleslaw	Small Portion
Cornbread	Large Portion
Cottage Cheese Fruit Plate	American Chop Suey
Cream of Wheat	Regular
Diced Beets	3-4gm Na (NAS)
Egg Salad Sandwich	2gm Na
Eggplant Roulette w/ Cheese	Low Fat/Low Chol
French Dip Sandwich	Fiber Restricted
French Fries	Consistent Carb
French Toast Slice w/ Syrup	Cardiac Diabetic
Fresh Whole Banana	2 gm K
Fruit Topping	Lactose Restricted
Garden Salad - Italian Dressing	Vegetarian LactoOvo
Ginger Cookies	Gluten Restricted
Gravy	Renal CSC
Green Beans	Small Portion
Grilled Cheese Sandwich	Large Portion
Ham Salad Sandwich	Apple Pie

<colgroup><col><col></colgroup><tbody>
</tbody>

I hope this makes sense and sorry for the bad table, my Excel to Forum add on is on my other laptop.

Thank you

 

[Rick Rothstein]

Because I have to go down about 400 times..... some like 6,000 cells and do not want to scroll or input data like that over and over again.

Okay, I'm confused (again). For some reason you and I are on different wavelengths regarding the black text values in Column B, so I'll ask again... where did they come from... another column via a formula or did you type them in? Maybe if I ask this question, I'll get the answer I need in order to answer your original question... select cell B3... look at the Formula Bar... post what you see in the Formula Bar in a response to this thread.

 

[Stan_C]

Okay, I'm confused (again). For some reason you and I are on different wavelengths regarding the black text values in Column B, so I'll ask again... where did they come from... another column via a formula or did you type them in? Maybe if I ask this question, I'll get the answer I need in order to answer your original question... select cell B3... look at the Formula Bar... post what you see in the Formula Bar in a response to this thread.

Okay..... The only cells that will have a formula are the cells b1, b16 etc....... The cells in between are just copied cells, hand typed.

 

[Rick Rothstein]

Okay..... The only cells that will have a formula are the cells b1, b16 etc....... The cells in between are just copied cells, hand typed.

Then there is no formula solution possible besides the one I gave you (there is a VB solution... more about that in a second). Perhaps you are not aware, but a cell can only contain one thing... either a constant (think hand-typed or copy/pasted) or a formula, but not both. This means there is no way to put a formula in a cell and copy it down without destroying the contents of the cell being copied over. So, you cannot have "Regular" in B2, put a formula in B1 that reproduces A1, copy the formula down and still have the text constant "Regular" in B2. The only way that would work (and the reason behind my insistence on where the black text values in Column B came from) is if the text in B2:B15, B17:B30:B32:etc came from a formula itself... then we could produce a combination formula that would still retrieve the text they originally came from for those cells and pickup the values from Column A for the rows you indicated in your first message. Anyway, here is a VB macro solution that will insert the appropriate formulas in B1, B16, B31, etc. while leaving the existing values in the other cells of Column B alone...

Sub InsertColumnAValuesEvery15RowsInColumnB()
  Dim X As Long, LastRowA As Long, LastRowB As Long
  Const StartRow As Long = 1
  LastRowA = Cells(Rows.Count, "A").End(xlUp).Row
  LastRowB = Cells(Rows.Count, "B").End(xlUp).Row
  For X = StartRow To LastRowA
    If X < LastRowB Then
      Cells(15 * X - 14, "B").Formula = "=A" & X
    End If
  Next
End Sub

A couple of notes about this function. Because you did say, I had to guess... one, the formula inserted into B1, B16, B31, etc. overwrites the value currently existing in that cell (that is, the exising cell value is lost) and, two, no formulas are inserted into any cells past the row containing the last piece of data in Column B. If these restictions are not right for you, post back telling us what should happen and I'll adjust the code accordingly.

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (InsertColumnAValuesEvery15RowsInColumnB) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. To make things simpler for you (especially if you will need to call this macro often), when you select the macro from the list, before clicking Run, you can click the Options button and assign a keyboard shortcut to it first and then use that keyboard shortcut the next time you want to run the macro.

 

[Stan_C]

Then there is no formula solution possible besides the one I gave you (there is a VB solution... more about that in a second). Perhaps you are not aware, but a cell can only contain one thing... either a constant (think hand-typed or copy/pasted) or a formula, but not both. This means there is no way to put a formula in a cell and copy it down without destroying the contents of the cell being copied over. So, you cannot have "Regular" in B2, put a formula in B1 that reproduces A1, copy the formula down and still have the text constant "Regular" in B2. The only way that would work (and the reason behind my insistence on where the black text values in Column B came from) is if the text in B2:B15, B17:B30:B32:etc came from a formula itself... then we could produce a combination formula that would still retrieve the text they originally came from for those cells and pickup the values from Column A for the rows you indicated in your first message. Anyway, here is a VB macro solution that will insert the appropriate formulas in B1, B16, B31, etc. while leaving the existing values in the other cells of Column B alone...

Sub InsertColumnAValuesEvery15RowsInColumnB()
  Dim X As Long, LastRowA As Long, LastRowB As Long
  Const StartRow As Long = 1
  LastRowA = Cells(Rows.Count, "A").End(xlUp).Row
  LastRowB = Cells(Rows.Count, "B").End(xlUp).Row
  For X = StartRow To LastRowA
    If X < LastRowB Then
      Cells(15 * X - 14, "B").Formula = "=A" & X
    End If
  Next
End Sub

A couple of notes about this function. Because you did say, I had to guess... one, the formula inserted into B1, B16, B31, etc. overwrites the value currently existing in that cell (that is, the exising cell value is lost) and, two, no formulas are inserted into any cells past the row containing the last piece of data in Column B. If these restictions are not right for you, post back telling us what should happen and I'll adjust the code accordingly.

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (InsertColumnAValuesEvery15RowsInColumnB) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. To make things simpler for you (especially if you will need to call this macro often), when you select the macro from the list, before clicking Run, you can click the Options button and assign a keyboard shortcut to it first and then use that keyboard shortcut the next time you want to run the macro.

I thank you for the work that you have input into this question. I have figured my issue out, stuck with the simple solution of adding another row to make the division even so the formula will not mess up past 500.v Again thank you!