1) showroom name and date of delivery (2) Product code, I want to place the date of delivery data next to the product code according to showroom name

[Jahedul]

Can anyone help me with this?

 

[Jahedul]

View attachment 120777
Can anyone help me with this?

 

[djclements]

If your Office Version is really 2010, you could try using VBA. Perhaps something along these lines:

Option Explicit

Sub UnpivotByCol()

    Dim rng As Range, n As Long, arr() As Variant
    Set rng = Sheet1.Range("A1:D5") ' Adjust the range of your Data of Delivery table as needed
    n = Application.WorksheetFunction.Sum(rng.Offset(1, 1).Resize(rng.Rows.Count - 1, rng.Columns.Count - 1))
    ReDim arr(1 To n, 1 To 3)

    Dim data As Variant, i As Long, j As Long, k As Long, rowId As Long
    data = rng.Value
    For j = 2 To UBound(data, 2)
        For i = 2 To UBound(data, 1)
            For k = 1 To data(i, j)
                rowId = rowId + 1
                arr(rowId, 1) = Format(rowId, "A00000")
                arr(rowId, 2) = data(i, 1)
                arr(rowId, 3) = data(1, j)
            Next k
        Next i
    Next j

    Sheet1.Range("J2").Resize(n, 3).Value = arr ' Adjust the output cell as desired

End Sub

With a modern version of Excel (Office 365), you could do the same thing with a formula:

=LET(
    rng, A1:D5,
    r, TAKE(DROP(rng,1),,1),
    c, DROP(TAKE(rng,1),,1),
    v, DROP(rng,1,1),
    t, TOCOL(IFS(v,v),2,1)>=SEQUENCE(,MAX(v)),
    HSTACK(
        TEXT(SEQUENCE(SUM(v)),"A00000"),
        TOCOL(IFS(t,TOCOL(IFS(v,r),2,1)),2),
        TOCOL(IFS(t,TOCOL(IFS(v,c),2,1)),2)
    )
)

NOTE: I assumed "Showroom Name" is in cell A1, as depicted in your second screenshot. Adjust the range references as needed.

 

[Jahedul]

Your solution is working, I am very happy to have found the solution, thank you very much

 

[djclements]

You’re welcome. Happy New Year!

 

[HighAndWilder]

If your Office Version is really 2010, you could try using VBA. Perhaps something along these lines:

Option Explicit

Sub UnpivotByCol()

    Dim rng As Range, n As Long, arr() As Variant
    Set rng = Sheet1.Range("A1:D5") ' Adjust the range of your Data of Delivery table as needed
    n = Application.WorksheetFunction.Sum(rng.Offset(1, 1).Resize(rng.Rows.Count - 1, rng.Columns.Count - 1))
    ReDim arr(1 To n, 1 To 3)

    Dim data As Variant, i As Long, j As Long, k As Long, rowId As Long
    data = rng.Value
    For j = 2 To UBound(data, 2)
        For i = 2 To UBound(data, 1)
            For k = 1 To data(i, j)
                rowId = rowId + 1
                arr(rowId, 1) = Format(rowId, "A00000")
                arr(rowId, 2) = data(i, 1)
                arr(rowId, 3) = data(1, j)
            Next k
        Next i
    Next j

    Sheet1.Range("J2").Resize(n, 3).Value = arr ' Adjust the output cell as desired

End Sub

With a modern version of Excel (Office 365), you could do the same thing with a formula:

=LET(
    rng, A1:D5,
    r, TAKE(DROP(rng,1),,1),
    c, DROP(TAKE(rng,1),,1),
    v, DROP(rng,1,1),
    t, TOCOL(IFS(v,v),2,1)>=SEQUENCE(,MAX(v)),
    HSTACK(
        TEXT(SEQUENCE(SUM(v)),"A00000"),
        TOCOL(IFS(t,TOCOL(IFS(v,r),2,1)),2),
        TOCOL(IFS(t,TOCOL(IFS(v,c),2,1)),2)
    )
)

NOTE: I assumed "Showroom Name" is in cell A1, as depicted in your second screenshot. Adjust the range references as needed.

I'm trying to understand this formula djclements.

What does this array represent and how does this line work?

t,TOCOL(IFS(v,v),2,1)>=SEQUENCE(,MAX(v)),

Thanks.

showroom name and date of delivery.xlsm
	J	K	L
3	T
4	TRUE	TRUE	FALSE
5	TRUE	TRUE	TRUE
6	TRUE	TRUE	FALSE
7	TRUE	TRUE	FALSE
8	TRUE	TRUE	FALSE
9	TRUE	TRUE	FALSE
10	TRUE	FALSE	FALSE
11	TRUE	TRUE	FALSE
12	TRUE	TRUE	FALSE
Sheet1 (2)

Cell Formulas
Range	Formula
J4:L12	J4	=LET( rng, A1:D5, r, TAKE(DROP(rng,1),,1), c, DROP(TAKE(rng,1),,1), v, DROP(rng,1,1), t, TOCOL(IFS(v,v),2,1)>=SEQUENCE(,MAX(v)),t)
Dynamic array formulas.

 

[djclements]

@HighAndWilder Basically, there are two separate methods used in this formula, but they're nested together to keep it relatively concise. The first method/step is to unpivot the "Date of delivery" table and arrange the data (represented as r = row labels, c = column labels, and v = values) in three separate columns for "Name", "Date" and "Quantity". As a demonstration, this is how the first step would look on its own:

=LET(
    rng, A1:D5,
    r, TAKE(DROP(rng,1),,1),
    c, DROP(TAKE(rng,1),,1),
    v, DROP(rng,1,1),
    HSTACK(
        TOCOL(IFS(v,r),2,1),
        TOCOL(IFS(v,c),2,1),
        TOCOL(IFS(v,v),2,1)
    )
)

Since the values in this dataset are numeric, I simply chose to use v as the logical_test1 argument for IFS, because any numeric value other than 0 is interpreted as TRUE. And when only one logical test is provided, the IFS function will return #N/A errors for any value that fails the logical test (e.g. 0's or blank cells in this case). With the optional [ignore] argument of TOCOL set to 2 - Ignore errors, these records are removed from the final output.

The second method/step is to repeat each record ("Name" and "Date") based on their applicable "Quantity", which is where the SEQUENCE-MAX logical test comes into play (represented as t = test). TOCOL(IFS(v,v),2,1)>=SEQUENCE(,MAX(v)) can be read as quantity>=SEQUENCE(,MAX(quantity)), the result of which is an array of TRUE and FALSE values (as shown in your mini-sheet sample). When these results are then passed to the logical_test1 argument of the subsequent IFS function, the same rules apply... each of the FALSE values become #N/A and those records are removed by TOCOL (ignore errors). For example, TOCOL(IFS(t,TOCOL(IFS(v,r),2,1)),2) can be read as TOCOL(IFS(quantity>=SEQUENCE(,MAX(quantity)),name),2).

I hope that makes sense. A very simple demonstration of step 2 can be found here: Creating a dynamic repeating sequence of arrays

 

[HighAndWilder]

@HighAndWilder Basically, there are two separate methods used in this formula, but they're nested together to keep it relatively concise. The first method/step is to unpivot the "Date of delivery" table and arrange the data (represented as r = row labels, c = column labels, and v = values) in three separate columns for "Name", "Date" and "Quantity". As a demonstration, this is how the first step would look on its own:

=LET(
    rng, A1:D5,
    r, TAKE(DROP(rng,1),,1),
    c, DROP(TAKE(rng,1),,1),
    v, DROP(rng,1,1),
    HSTACK(
        TOCOL(IFS(v,r),2,1),
        TOCOL(IFS(v,c),2,1),
        TOCOL(IFS(v,v),2,1)
    )
)

Since the values in this dataset are numeric, I simply chose to use v as the logical_test1 argument for IFS, because any numeric value other than 0 is interpreted as TRUE. And when only one logical test is provided, the IFS function will return #N/A errors for any value that fails the logical test (e.g. 0's or blank cells in this case). With the optional [ignore] argument of TOCOL set to 2 - Ignore errors, these records are removed from the final output.

The second method/step is to repeat each record ("Name" and "Date") based on their applicable "Quantity", which is where the SEQUENCE-MAX logical test comes into play (represented as t = test). TOCOL(IFS(v,v),2,1)>=SEQUENCE(,MAX(v)) can be read as quantity>=SEQUENCE(,MAX(quantity)), the result of which is an array of TRUE and FALSE values (as shown in your mini-sheet sample). When these results are then passed to the logical_test1 argument of the subsequent IFS function, the same rules apply... each of the FALSE values become #N/A and those records are removed by TOCOL (ignore errors). For example, TOCOL(IFS(t,TOCOL(IFS(v,r),2,1)),2) can be read as TOCOL(IFS(quantity>=SEQUENCE(,MAX(quantity)),name),2).

I hope that makes sense. A very simple demonstration of step 2 can be found here: Creating a dynamic repeating sequence of arrays

Many thanks djClements.

This particular 'feature' of the IFS function does not seem to be documented and explained when the multiple tests are explained.

Plan for the day - play around on Excel.