I would like to create a list of all existing unique pairs of items that belong to each category.
The input table looks like this:
| Category | Item |
|---|---|
| A | 1 |
| A | 3 |
| A | 4 |
| B | 1 |
| B | 2 |
| C | 4 |
| C | 5 |
The output table should look like this:
| Category | Item1 | Item2 |
|---|---|---|
| A | 1 | 3 |
| A | 1 | 4 |
| A | 3 | 4 |
| B | 1 | 2 |
| C | 4 | 5 |
Massive thanks in advance for your help.
I just can´t figure this out.
CodePudding user response：
Try this
Sub CreatePairs()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Dim i As Long, j As Long
Dim cat As String, item1 As String, item2 As String
Dim currentRow As Long
currentRow = 1
For i = 2 To lastRow
cat = ws.Cells(i, 1).Value
item1 = ws.Cells(i, 2).Value
For j = i 1 To lastRow
If ws.Cells(j, 1).Value = cat Then
item2 = ws.Cells(j, 2).Value
currentRow = currentRow 1
ws.Cells(currentRow, 3) = cat
ws.Cells(currentRow, 4) = item1
ws.Cells(currentRow, 5) = item2
End If
Next j
Next i
End Sub
CodePudding user response：
By formula using Excel 365:
=LET(
category, A2:A8,
item, B2:B8,
DROP(
REDUCE(
{1, 1, 1},
UNIQUE(category),
LAMBDA(a, b,
LET(
count, SUM(
--(
b =
category
)
),
combos, COMBIN(
count,
2
),
start, XMATCH(
b,
category
) - 1,
ind, TOCOL(
MAKEARRAY(
count,
count,
LAMBDA(
r,
c,
IF(
c >
r,
100 *
r
c,
0
)
)
)
),
sel, FILTER(
ind,
ind > 0
),
VSTACK(
a,
HSTACK(
IF(
SEQUENCE(
combos
),
b
),
INDEX(
item,
sel /
100
start
),
INDEX(
item,
MOD(
sel,
100
)
start
)
)
)
)
)
),
1
)
)
