# Copy to column if cell matches certain value

#### jb1180

##### New Member
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

 PART1 MANUFACTURER NAME PART1 MANUFACTURER PART NUMBER 1 PART2 MANUFACTURER NAME PART2 MANUFACTURER PART NUMBER 2 PART3 MANUFACTURER NAME PART3 MANUFACTURER PART NUMBER 3 PART4 MANUFACTURER NAME PART4 MANUFACTURER PART NUMBER 4

<tbody>
</tbody>

Sample Desired output

 MANUFACTURER PART NUMBER MANUFACTURER PART1 1 NAME PART2 2 NAME PART3 3 NAME PART4 4 NAME

<tbody>
</tbody>

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

<tbody>
</tbody>

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

#### AFPathfinder

##### Well-known Member
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``````

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,168,117
Messages
5,857,483
Members
431,882
Latest member
saaaaaaaaaaaaaaaaaaaaaa

### 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.

### Which adblocker are you using?

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

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