Move data from a column to another

[Panoos64]

Hi to all of you, kindly require to provide me support so that to prepare a VBA code which should run through the range “(I29:I14800)” and if cells contain prices should cut any texts or numbers from column “C” and placed them in column “A” opposite description’s cells.
In additions, prices should place them opposite description’s row too in row "I". I present below my original data and the expected result. Therefore that, this post is similar to my previous but with new options. Apologies for any inconvenience I caused. Thank you all in advance!

 

[Kerryx]

Ever think of using power query to handle it

Book1
	A	B
1	Description	Price
2	89000101
3	Lemonade tin 330rnI	1.2
4	89000205
5	Orange Squash 750ml	2.35
6	89000313
7	Lemon Squash 750m'	2.35
8	92030314
9	Wholemeal sliced 1000gr	3.25
10	WHY00750
11	Whisky 750ml	15.25
12	WHY00500
13	Whisky 500ml	9.75
14	BAGSSM
15	Smal bags	2.35
Sheet1

Book1
	A	B	C
1	Code	Description	Price
2	89000101	Lemonade tin 330rnI	1.2
3	89000205	Orange Squash 750ml	2.35
4	89000313	Lemon Squash 750m'	2.35
5	92030314	Wholemeal sliced 1000gr	3.25
6	WHY00750	Whisky 750ml	15.25
7	WHY00500	Whisky 500ml	9.75
8	BAGSSM	Smal bags	2.35
Table1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Description", type any}, {"Price", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 2), type number),
    #"Added Custom" = Table.AddColumn(#"Inserted Modulo", "Code", each if [Modulo] = 0 then [Description] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Description1", each if[Modulo]=1 then [Description] else null),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"Description", "Index", "Modulo", "Code", "Description1", "Price"}),
    #"Filled Down" = Table.FillDown(#"Reordered Columns",{"Code"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Modulo] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Description", "Index", "Modulo"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Description1", "Description"}})
in
    #"Renamed Columns"

 

[Panoos64]

Thank you Kerryx for your support and your time spent for my project. Is so complicate for to convert the spreadsheet to table and use "Power Query" and so I prefer a VBA code. I appreciated you attempt to solve my issue but unfortunately for me I couldn't with your code. Thank you once again. Have a great day!

 

[Kerryx]

Actually PQ is very simple easier than VBA , if you want to know more here are some great links from alansidman

VBA to transpose blocks (arrays) of data

I have to transform literally hundreds of blocks of data exported from financial software into a different format for my manager. The source data is on the left in the image and the required output is on the right. Doing it manually is not that difficult but i will drive me to drink! Any help...

www.mrexcel.com

 

[Alex Blakenburg]

Typically you want the output consolidated and in a cleaner format.
Does you data heading really start at row 27 ?
Do you really want it back in the same place with blank columns and blank rows in between ?

 

[Alex Blakenburg]

Just to keep it moving along, give this a try:

Sub ReformatData()

    Dim sht As Worksheet
    Dim rng As Range
    Dim lastRow As Long
    Dim arr As Variant
    Dim i As Long, colPrice As Long
    
    Set sht = ActiveSheet
    With sht
        lastRow = .Range("C" & Rows.Count).End(xlUp).Row + 1
        colPrice = 9
        Set rng = .Range(.Cells(28, "A"), .Cells(lastRow, "I"))
        arr = rng.Value
    End With

    For i = 1 To UBound(arr)
        If arr(i, colPrice) <> "" Then
            arr(i, 1) = arr(i, 3)
            arr(i, 3) = arr(i + 1, 3)
            arr(i + 1, 3) = Empty
        End If
    Next i
    
    rng.Value = arr
    
End Sub

 

[Panoos64]

Thank you Alex! Nice code which works perfect and according to my project. Regarding your above notes kindly be informed that the rows from 'A1" : "I27 are just headings e.g.
- supplier's Brand Name,
- Invoice date
- Credit period
- Account number
- e.t.c.
In addition, the blank columns have data too, like quantity, unit measure, expire date e.t.c. Therefore that, the above is an extract of the data and i entered only the columns and rows which they should be affected. I express my apologies if not mentioned the contents of all columns and rows, this was causes the code complicate.
However, i express my thanks once again. Have a lovely day!