Combine two columns into one by inserting a row.

[SueDuff]

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

[#2]

That would be the easiest way to do it, of course it can also be done with VBA ... if there are a lot of rows / columns.

F2 G2 H2 Formula H2
TEST CAR TEST CAR =F2&" "&G2

If you liked the solution, please post it as a Best Responses with a like (press thumbs up). If you did not like the solution, please give me a little reply to find a more customized solution for you.

I would be happy to find out if I could help.

Nikolino
I know I don't know anything (Socrates)

[#3]

@SueDuff 

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

[#4]

@SueDuff 

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?"

[#5]

Thanks for the advice. 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.

[#6]

@Hans Vogelaar I will give it a try, thank you.

[#7]

@SueDuff 

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.