Copy to column if cell matches certain value

jb1180

New Member
Joined
Dec 6, 2017
Messages
1
I have a 3 row spreadsheet that needs to be separated into multiple columns. Row 1 is the unique value. Row 2 will become the column name. Row 3 contains the data for the column. Ultimately will result in about 200 columns.

Is there a good formula that would accomplish this? Thanks in advance.

Sample Current format

PART1MANUFACTURERNAME
PART1MANUFACTURER PART NUMBER1
PART2MANUFACTURERNAME
PART2MANUFACTURER PART NUMBER2
PART3MANUFACTURERNAME
PART3MANUFACTURER PART NUMBER3
PART4MANUFACTURERNAME
PART4MANUFACTURER PART NUMBER4

<tbody>
</tbody>

Sample Desired output

MANUFACTURER PART NUMBERMANUFACTURER
PART11NAME
PART22NAME
PART33NAME
PART44NAME

<tbody>
</tbody>

****** id="cke_pastebin" style="position: absolute; top: 134px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
MANUFACTURER

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

AFPathfinder

Well-known Member
Joined
Apr 8, 2012
Messages
519
I believe this is beyond a formulas type solution. You'll need a macro like this:
Code:
Sub Zipper()
    Dim i As Integer
    Dim finalRow As Integer
    
    Range("A1").EntireRow.Insert
    
    finalRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("B1").Value = "Manufacturer Part Number"
    Range("C1").Value = "Manufacturer"
    
    For i = 2 To finalRow
        If Cells(i, 1).Value = "" Then
            Exit For
        End If
        
        Cells(i, 2).Value = Cells(i, 3).Value
        Cells(i, 3).Value = Cells(i + 1, 3).Value
        Range("A" & i + 1).EntireRow.Delete
    Next i
    
    MsgBox "The macro has finished."
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,118,415
Messages
5,571,973
Members
412,430
Latest member
Huuktkt
Top