nested loops

[TheSardOz]

Hi all,
I am trying to create a nested loop to sort out a price list.
I want to add the finish type abbreviation to the code number.
The issue that i am facing is that for each cell in the code column I need to add AB or BC based on the Adjicent cell value (the price).
If the column B-C-D-E-F has a value in I need to add a value to the code.
the logic is:
cells A2 has the value of 396, cell B2 has a value in so in a new sheet in cell A2 I need to paste in 396-AB and the rest of the values in the same row
then repeat this process for column C3 ( if no value skip) and check the next cell, D3 has a value in then in the new sheet in A3 i will have 396-NP ect.
I am not sure how to tackle this one i have try few nested loops but I keep on failing.

Thank you in advance for your help

 

[Georgiboy]

Perhaps a formula like below:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Code	Finish AB	Finish BC	Finish NP	Finish PB	Finish SC
2	396	137.9		137.9	137.9				396-AB	137.9
3	410	258.9	100.7	258.9	258.9				396-NP	137.9
4	411	343.2		343.2	343.2				396-PB	137.9
5	412	443		443	443	300			410-AB	258.9
6									410-BC	100.7
7									410-NP	258.9
8									410-PB	258.9
9									411-AB	343.2
10									411-NP	343.2
11									411-PB	343.2
12									412-AB	443
13									412-NP	443
14									412-PB	443
15									412-SC	300
16
17
Sheet3

Cell Formulas
Range	Formula
I2:J15	I2	=LET(rng,Sheet3!B2:F5,codes,TOCOL(IF(SEQUENCE(,COUNTA(rng))<=rng,Sheet3!A2:A5,#N/A),3), vals,TOCOL(IF(SEQUENCE(,COUNTA(rng))<=rng,rng,#N/A),3), heads,RIGHT(TOCOL(IF(SEQUENCE(,COUNTA(rng))<=rng,Sheet3!B1:F1,#N/A),3),2), HSTACK(codes & "-" & heads,vals))
Dynamic array formulas.

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Code	Finish AB	Finish BC	Finish NP	Finish PB	Finish SC
2	396	137.9		137.9	137.9				396-AB	137.9
3	410	258.9	100.7	258.9	258.9				396-NP	137.9
4	411	343.2		343.2	343.2				396-PB	137.9
5	412	443		443	443	300			410-AB	258.9
6									410-BC	100.7
7									410-NP	258.9
8									410-PB	258.9
9									411-AB	343.2
10									411-NP	343.2
11									411-PB	343.2
12									412-AB	443
13									412-NP	443
14									412-PB	443
15									412-SC	300
Lists

Cell Formulas
Range	Formula
I2:J15	I2	=HSTACK(TOCOL(IF(B2:F5="",x,A2:A5),2)&"-"&TOCOL(IF(B2:F5="",x,RIGHT(B1:F1,2)),2),TOCOL(B2:F5,1))
Dynamic array formulas.

 

[TheSardOz]

Perhaps a formula like below:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Code	Finish AB	Finish BC	Finish NP	Finish PB	Finish SC
2	396	137.9		137.9	137.9				396-AB	137.9
3	410	258.9	100.7	258.9	258.9				396-NP	137.9
4	411	343.2		343.2	343.2				396-PB	137.9
5	412	443		443	443	300			410-AB	258.9
6									410-BC	100.7
7									410-NP	258.9
8									410-PB	258.9
9									411-AB	343.2
10									411-NP	343.2
11									411-PB	343.2
12									412-AB	443
13									412-NP	443
14									412-PB	443
15									412-SC	300
16
17
Sheet3

Cell Formulas
Range	Formula
I2:J15	I2	=LET(rng,Sheet3!B2:F5,codes,TOCOL(IF(SEQUENCE(,COUNTA(rng))<=rng,Sheet3!A2:A5,#N/A),3), vals,TOCOL(IF(SEQUENCE(,COUNTA(rng))<=rng,rng,#N/A),3), heads,RIGHT(TOCOL(IF(SEQUENCE(,COUNTA(rng))<=rng,Sheet3!B1:F1,#N/A),3),2), HSTACK(codes & "-" & heads,vals))
Dynamic array formulas.

Thank you

 

[TheSardOz]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Code	Finish AB	Finish BC	Finish NP	Finish PB	Finish SC
2	396	137.9		137.9	137.9				396-AB	137.9
3	410	258.9	100.7	258.9	258.9				396-NP	137.9
4	411	343.2		343.2	343.2				396-PB	137.9
5	412	443		443	443	300			410-AB	258.9
6									410-BC	100.7
7									410-NP	258.9
8									410-PB	258.9
9									411-AB	343.2
10									411-NP	343.2
11									411-PB	343.2
12									412-AB	443
13									412-NP	443
14									412-PB	443
15									412-SC	300
Lists

Cell Formulas
Range	Formula
I2:J15	I2	=HSTACK(TOCOL(IF(B2:F5="",x,A2:A5),2)&"-"&TOCOL(IF(B2:F5="",x,RIGHT(B1:F1,2)),2),TOCOL(B2:F5,1))
Dynamic array formulas.

Thank you!

 

[TheSardOz]

Thank you for your solutions @Fluff and @Georgiboy. I was wondering if you might have an equivalent solution in VBA too.

 

[Georgiboy]

Thank you for your solutions @Fluff and @Georgiboy. I was wondering if you might have an equivalent solution in VBA too.

Maybe something like:

Sub test()
    Dim oRng As Range, iRng As Range
    Dim var As Variant, eRow As Long
    Dim x As Long, y As Long, z As Long
    
    eRow = Range("A" & Rows.Count).End(xlUp).Row
    Set oRng = Range("A1:F" & eRow)
    Set iRng = Range("B2:F" & eRow)
    var = oRng.Value
    
    ReDim oVar(Application.CountA(iRng) - 1, 1)
    
    For x = 2 To UBound(var)
        For y = 2 To UBound(var, 2)
            If var(x, y) <> "" Then
                oVar(z, 0) = var(x, 1) & "-" & Right(var(1, y), 2)
                oVar(z, 1) = var(x, y)
                z = z + 1
            End If
        Next y
    Next x
    
    Range("I2").Resize(UBound(oVar) + 1, UBound(oVar, 2) + 1) = oVar
End Sub

 

[TheSardOz]

Maybe something like:

Sub test()
    Dim oRng As Range, iRng As Range
    Dim var As Variant, eRow As Long
    Dim x As Long, y As Long, z As Long
   
    eRow = Range("A" & Rows.Count).End(xlUp).Row
    Set oRng = Range("A1:F" & eRow)
    Set iRng = Range("B2:F" & eRow)
    var = oRng.Value
   
    ReDim oVar(Application.CountA(iRng) - 1, 1)
   
    For x = 2 To UBound(var)
        For y = 2 To UBound(var, 2)
            If var(x, y) <> "" Then
                oVar(z, 0) = var(x, 1) & "-" & Right(var(1, y), 2)
                oVar(z, 1) = var(x, y)
                z = z + 1
            End If
        Next y
    Next x
   
    Range("I2").Resize(UBound(oVar) + 1, UBound(oVar, 2) + 1) = oVar
End Sub

Thank you kindly!

 

[Georgiboy]

May even get away with shortening the last line to:

Range("I2").Resize(z, 2) = oVar

 

[montecarlo2012]

maybe

Sub AddFinishToCode()

    Dim dataSheet As Worksheet
    Set dataSheet = ThisWorkbook.Sheets("Data") ' Change "Data" to the name of your sheet
    
    Dim outputSheet As Worksheet
    Set outputSheet = ThisWorkbook.Sheets.Add(After:=dataSheet) ' Creates a new sheet for the output
    outputSheet.Name = "Output" ' Change "Output" to the name you want for the output sheet
    
    Dim lastRow As Long
    lastRow = dataSheet.Cells(Rows.Count, 1).End(xlUp).Row
    
    Dim i As Long
    Dim code As String
    Dim finish As String
    
    For i = 2 To lastRow ' Assumes data starts on row 2, change as needed
        code = dataSheet.Cells(i, 1).Value ' Get the code from column A
        If Not IsEmpty(dataSheet.Cells(i, 2)) Then ' Check if there's a price in column B
            finish = "-AB" ' Set the finish based on the adjacent price
        ElseIf Not IsEmpty(dataSheet.Cells(i, 3)) Then
            finish = "-BC"
        Else
            finish = "" ' If no price in adjacent columns, don't add a finish
        End If
        If finish <> "" Then ' Only write to output if there's a finish to add
            outputSheet.Cells(i, 1).Value = code & finish ' Write the new code to the output sheet
            outputSheet.Cells(i, 2).Resize(1, 4).Value = dataSheet.Cells(i, 2).Resize(1, 4).Value ' Copy the rest of the data to the output sheet
        End If
    Next i
    
End Sub