Bring value (variable position) from rows to column

[Dampa88]

Hi all,

I'm stacked with a formula in Excel or a way to get things sorted.

Basically, I have some rows that need to be moved into columns an repeated x-times.

Do you have any idea how can I obtain this?

Thanks for your help.

Daniele

 

[Joe4]

There are a number of ways:

- Copy -> Paste Special -> Transpose
- Formulas (i.e. OFFSET, etc)
- VBA

The "best" way usually depends on the exact structure and conditions, as well as what you are willing and able to use.

 

[Dampa88]

Hi Joe,

Thanks for your reply.

Potentially I will have more than 200 of different "places", so I'd exclude the transpose option.

In the reality, I'm already building a macro but my knowledge is still basic, that's why I was thinking about a formula.
Also in this case, I'm a bit blocked on how to move forward with the OFFSET.

May I ask you some suggestion regarding the 2 options? OFFSET or macro.

Thank you very much.

KR,
Daniele

 

[Joe4]

"General" questions lead to "general" answers.
If you can provide a specific example (with details!) of your current data structure, and what you want it to do, we can show you different ways of exactly how to accomplish that.
Be sure to include specific range references in your example.

 

[Dampa88]

Hi Joe,

Indeed you are right

I've tried to upload an example here: https://ufile.io/6fup0
Range references are variable and I can have also 100 different "zones".

Please let me know in case you cannot see it.

Thanks for your help.

KR,
Daniele

 

[Joe4]

Unfortunately, due to my work's security policy, I am unable to download files from the internet. I may be able to much later tonight when I am home, or perhaps someone will be able to help before then.

 

[Dampa88]

Hi Joe,

Maybe this can help, the 2 tables below:

AS IS

[TABLE="width: 163"]
<tbody>[TR]
[TD]UPC[/TD]
[TD]QTY[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000101[/TD]
[TD]Zone[/TD]
[/TR]
[TR]
[TD="align: right"]906654529[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]906654529[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]906654536[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]906654536[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]906654543[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]906654550[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]906654550[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]906654567[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000102[/TD]
[TD]Zone[/TD]
[/TR]
[TR]
[TD="align: right"]191930537398[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]191930537237[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]191930537152[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]191930537398[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]191930537589[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]191930537480[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]191930537589[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]191930537985[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]191930537886[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]191930537787[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]191930537688[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]190850674435[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]190850674350[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]190850674275[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]190850674190[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000103[/TD]
[TD]Zone[/TD]
[/TR]
[TR]
[TD="align: right"]190852607875[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]190852607929[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]190852607967[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]190852608032[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]190852608087[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]190852608032[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]190852608087[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]190852608032[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]190852608124[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]190852608124[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]190852608155[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]190852608155[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]190852608216[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]190852608186[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000104[/TD]
[TD]Zone[/TD]
[/TR]
[TR]
[TD="align: right"]887974690211[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]887974690211[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]887974690204[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]887974690204[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]887974690198[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]887974690181[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]887974690181[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]887974690044[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]887974690174[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]887974690174[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]887974690167[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]887974690020[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]887974690020[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]


TO BE

[TABLE="width: 262"]
<tbody>[TR]
[TD]Zone[/TD]
[TD]UPC[/TD]
[TD]QTY[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000101[/TD]
[TD="align: right"]906654529[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000101[/TD]
[TD="align: right"]906654529[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000101[/TD]
[TD="align: right"]906654536[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000101[/TD]
[TD="align: right"]906654536[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000101[/TD]
[TD="align: right"]906654543[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000101[/TD]
[TD="align: right"]906654550[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000101[/TD]
[TD="align: right"]906654550[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000101[/TD]
[TD="align: right"]906654567[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000102[/TD]
[TD="align: right"]191930537398[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000102[/TD]
[TD="align: right"]191930537237[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000102[/TD]
[TD="align: right"]191930537152[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000102[/TD]
[TD="align: right"]191930537398[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000102[/TD]
[TD="align: right"]191930537589[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000102[/TD]
[TD="align: right"]191930537480[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000102[/TD]
[TD="align: right"]191930537589[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000102[/TD]
[TD="align: right"]191930537985[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000102[/TD]
[TD="align: right"]191930537886[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000102[/TD]
[TD="align: right"]191930537787[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000102[/TD]
[TD="align: right"]191930537688[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000102[/TD]
[TD="align: right"]190850674435[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000102[/TD]
[TD="align: right"]190850674350[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000102[/TD]
[TD="align: right"]190850674275[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000102[/TD]
[TD="align: right"]190850674190[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000103[/TD]
[TD="align: right"]190852607875[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000103[/TD]
[TD="align: right"]190852607929[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000103[/TD]
[TD="align: right"]190852607967[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000103[/TD]
[TD="align: right"]190852608032[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000103[/TD]
[TD="align: right"]190852608087[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000103[/TD]
[TD="align: right"]190852608032[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000103[/TD]
[TD="align: right"]190852608087[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000103[/TD]
[TD="align: right"]190852608032[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000103[/TD]
[TD="align: right"]190852608124[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000103[/TD]
[TD="align: right"]190852608124[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000103[/TD]
[TD="align: right"]190852608155[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000103[/TD]
[TD="align: right"]190852608155[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000103[/TD]
[TD="align: right"]190852608216[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000103[/TD]
[TD="align: right"]190852608186[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000104[/TD]
[TD="align: right"]887974690211[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000104[/TD]
[TD="align: right"]887974690211[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000104[/TD]
[TD="align: right"]887974690204[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000104[/TD]
[TD="align: right"]887974690204[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000104[/TD]
[TD="align: right"]887974690198[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000104[/TD]
[TD="align: right"]887974690181[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000104[/TD]
[TD="align: right"]887974690181[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000104[/TD]
[TD="align: right"]887974690044[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000104[/TD]
[TD="align: right"]887974690174[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000104[/TD]
[TD="align: right"]887974690174[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000104[/TD]
[TD="align: right"]887974690167[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000104[/TD]
[TD="align: right"]887974690020[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2340000000104[/TD]
[TD="align: right"]887974690020[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

Thank you,
Daniele

 

[Joe4]

Which columns are all those fields in?

 

[Dampa88]

Hi Joe,

The columns are A (mix of zones - always starting with 23* or with 6 zeros in the middle + UPC) and B (qty) and needs to be A (Zone), B (UPC) and C (QTY).

Thanks,
Daniele

 

[Joe4]

Try this:

Sub MyMacro()

    Dim rw As Long
    Dim zn As String

    Application.ScreenUpdating = False

'   Insert column A and set ormat to Text
    Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("A:A").NumberFormat = "@"
    
'   Insert header
    Range("A1").FormulaR1C1 = "Zone"
    
'   Loop through rows
    rw = 2
    Do
'       If no data in column C, then exit loop
        If Cells(rw, "C") = "" Then Exit Do
'       Check to see in column C is "Zone"
        If Cells(rw, "C") = "Zone" Then
'           Capture Zone value and delete row
            zn = Cells(rw, "B")
            Rows(rw).Delete
        Else
'           Insert previous zone value in column B and increment row count
            Cells(rw, "A") = zn
            rw = rw + 1
        End If
    Loop
    
    Application.ScreenUpdating = True
    
End Sub