Help in generating P/N's from multiple columns

[ewOOD]

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>

 

[dave3009]

This is a bit crude but it seems to do the job, genrates circa 2500 P/Ns

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

 

[ewOOD]

Dave

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

ewOOD

 

[dave3009]

You're welcome, thanks for the feedback