Bring value (variable position) from rows to column

Dampa88

Board Regular
Joined
Apr 28, 2016
Messages
53
Office Version
  1. 365
Platform
  1. Windows
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.

ex3Yd3u.png


Do you have any idea how can I obtain this?

Thanks for your help.

Daniele
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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.
 
Upvote 0
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
 
Upvote 0
"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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Hi Joe,

Maybe this can help, the 2 tables below:


Code:
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
 
Upvote 0
Which columns are all those fields in?
 
Upvote 0
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
 
Upvote 0
Try this:
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top