Jul 24 2020 02:29 AM
Hi, I am trying to combine two columns into one. Column F has the product sold and column G has the postage. I would like to know if it is possible for the amount from column G to be moved to column F by inserting a row in column F for it to be moved to that is not a manual process but a function or macro that can be set up.
Hope that makes sense.
Thanks
Jul 24 2020 02:59 AM
Jul 24 2020 03:05 AM
Here is a macro:
Sub InsertAndMove()
Dim r As Long
Dim m As Long
Application.ScreenUpdating = False
m = Range("F" & Rows.Count).End(xlUp).Row
For r = m To 2 Step -1
Range("F" & r + 1).Insert Shift:=xlShiftDown
Range("F" & r + 1).Value = Range("G" & r).Value
Range("G" & r).Clear
Next r
Application.ScreenUpdating = True
End Sub
If you want to insert an entire row instead of only a cell in column G, change the line
Range("F" & r + 1).Insert Shift:=xlShiftDown
to
Range("F" & r + 1).EntireRow.Insert Shift:=xlShiftDown
Jul 24 2020 04:41 AM - edited Jul 24 2020 04:48 AM
You wrote: Hi, I am trying to combine two columns into one....I would like to know if it is possible for the amount from column G to be moved to column F by inserting a row in column F for it to be moved to that is not a manual process but a function or macro that can be set up.
Hope that makes sense.
It did make sense, in the sense that you were clear on what you wanted to do. And you've gotten two answers that, I'm assuming, make it possible.
There's another way to "making sense" however....and my "sense maker" is wondering "Why?" As in "Why are you trying to do this in the first place?" In general--and maybe your situation is an entirely legitimate exception--Excel does very well with information such as you're describing arrayed as an Excel Table, which is to say, one record per row. So such things as a record of a sale would be stored as a single row, arrayed perhaps as
Date.....SKU....ProdID...Quantity.....BuyerName...BuyerAddress....Postage.....
And from that kind of record you could do all kinds of things quite easily....reports on how many of each product were sold in a given month, how many were sold to buyers in NC....whatever.
It's the very great ease of accomplishing almost anything from a database that holds each record as a single row that makes me wonder the "Why?"
If all you are doing is creating a report that shows summary numbers by Product of Postage costs to ship, that would be a report.....
Anyway, if you could excuse the impertinence of my asking, I'd like to understand how it makes sense in this respect: what was the purpose?
(There might well have been an easier way to accomplish that purpose.)
As an aside, this is particularly interesting because within the last few hours, another user posted a question wanting to go in exactly the opposite direction....turning data in one column but two rows, into one row, two columns.... https://techcommunity.microsoft.com/t5/excel/split-a-column-by-rows/m-p/1543283
That other request makes more sense from a database (or Excel Table) point of view... in that case, my question would have been "Why was it in that other layout in the first place?"
Jul 24 2020 06:14 AM
Jul 24 2020 06:20 AM
@Hans Vogelaar I will give it a try, thank you.
Jul 24 2020 07:03 AM
You very nicely replied to my impertinent question of "Why?!": I am looking to create a CSV file that can be upload to an accounting package and it is very specific about the layout or it rejects it. The files I download have all the information I need but in the wrong places (multiple columns) as I need certain information in one column, linked to a name and order number arranged in rows rather than columns.
First, thank you for explaining the purpose you were seeking to serve.
Second, your answer, I must say, surprises me. If only for this reason: when I get CSV files from various financial sources--Fidelity, Chase, Citibank, AmEx--they all are arrayed with records in rows, not columns. I.e., in what I'd call standard database fashion. So it is surprising to hear that this accounting package is asking for data records (or at least some parts of each record) arranged in columns.