# 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

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
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``````

Replies
7
Views
153
Replies
0
Views
216
Replies
3
Views
333
Replies
5
Views
306
Replies
1
Views
52