# Help in generating P/N's from multiple columns

#### ewOOD

##### New Member
I'm try to generate a P/N in column A for each combination associated with the categories. Cat 1 will always be a constant, but then the P/N should reflect (in order) Cat 2 (c2), Cat 3 (D2), Cat 4( d2), then start all over again in order to list all combinations. There should also be a second column (description) that generates the description associated with each selection.

Any help would be greatly appreciated. This is only the first table of about 10 that I need to complete.

Thanks so much!

ewOOD

 P/N Description CAT 1 CAT 2 CAT 3 CAT 4 CAT 5 Category Part Code Part Description BF1811 BAR FLAT 1/8" 1" L/C B F 18 1 1 01 B BAR BF1812 R 14 112 2 02 F FLAT BF1813 S 38 2 3 02 R ROUND H 12 212 02 S SQUARE 58 3 02 H HEX 114 4 03 18 1/8" 112 6 03 14 1/4" 2 34 03 38 3/8" 212 114 03 12 1/2" 3 312 03 58 5/8" 312 12 03 114 1-1/4" 4 58 03 112 1-1/2" 6 03 2 2" 618 03 212 2-1/2" 8 03 3 3" 5 03 312 3-1/2" 512 03 4 4" 03 6 6" 03 618 6-1/8" 03 8 8" 03 5 5" 03 512 5-1/2" 04 1 1" 04 112 1-1/2" 04 2 2" 04 212 2-1/2" 04 3 3" 04 4 4" 04 6 6" 04 34 3/4" 04 114 1-1/4" 04 312 3-1/2" 04 12 1/2" 04 58 5/8" 05 1 L/C 05 2 304L 05 3 316L

<tbody>
</tbody>

<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.
This is a bit crude but it seems to do the job, genrates circa 2500 P/Ns

Code:
``````Sub genPrtNos()

Dim a, b, c, d, e As Long
Dim Cat1, Cat2, Cat3, Cat4, Cat5 As Long
Dim p1, p2, p3, p4, p5 As Variant
Dim d1, d2, d3, d4, d5 As Variant
Dim Sh1 As Worksheet
Dim lst As Long

Set Sh1 = Sheet2

Cat1 = Sh1.Range("C" & Rows.Count).End(xlUp).Row
Cat2 = Sh1.Range("D" & Rows.Count).End(xlUp).Row
Cat3 = Sh1.Range("E" & Rows.Count).End(xlUp).Row
Cat4 = Sh1.Range("F" & Rows.Count).End(xlUp).Row
Cat5 = Sh1.Range("G" & Rows.Count).End(xlUp).Row

For a = 2 To Cat1
For b = 2 To Cat2
For c = 2 To Cat3
For d = 2 To Cat4
For e = 2 To Cat5
p1 = Sh1.Range("C" & a)
p2 = Sh1.Range("D" & b)
p3 = Sh1.Range("E" & c)
p4 = Sh1.Range("F" & d)
p5 = Sh1.Range("G" & e)

d1 = WorksheetFunction.VLookup(p1, Sh1.Range("I2:J38"), 2, 0)
d2 = WorksheetFunction.VLookup(p2, Sh1.Range("I2:J38"), 2, 0)
d3 = WorksheetFunction.VLookup(p3, Sh1.Range("I2:J38"), 2, 0)
d4 = WorksheetFunction.VLookup(p4, Sh1.Range("I2:J38"), 2, 0)
d5 = WorksheetFunction.VLookup(p5, Sh1.Range("I2:J38"), 2, 0)

lst = Sheet3.Range("A" & Rows.Count).End(xlUp).Row + 1
Sheet3.Range("A" & lst) = p1 & p2 & p3 & p4 & p5
Sheet3.Range("B" & lst) = d1 & " " & d2 & " " & d3 & " " & d4 & " " & d5
Next e
Next d
Next c
Next b
Next a

End Sub``````

You will need to adjust the ranges to suit!

HTH

Dave

Dave

It worked like a charm, thanks so much for your help! I appreciate it.

ewOOD

You're welcome, thanks for the feedback

Replies
1
Views
107
Replies
1
Views
101
Replies
4
Views
106
Replies
36
Views
614
Replies
6
Views
225

1,196,508
Messages
6,015,615
Members
441,906
Latest member
gafoor

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