DJMC
Platinum Member | Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору Код: Sub aaa() lLastRow1 = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row For k = 1 To lLastRow1 Step 2 lLastRow2 = Sheets("Лист2").Cells(Rows.Count, 1).End(xlUp).Row For n = 1 To 15 Sheets("Лист2").Cells(lLastRow2 + 1, n) = n marks = ActiveSheet.Range(Cells(k, 1), Cells(k, 15)).Find(Sheets("Лист2").Cells(lLastRow2 + 1, n), LookAt:=xlWhole).Offset(1, 0).Value Sheets("Лист2").Cells(lLastRow2 + 2, n) = marks Sheets("Лист2").Cells(lLastRow2 + 3, n) = ActiveSheet.Cells(k + 1, n) Sheets("Лист2").Cells(lLastRow2 + 4, n) = ActiveSheet.Cells(k, n) Sheets("Лист2").Cells(lLastRow2 + 5, n) = "хз" Sheets("Лист2").Cells(lLastRow2 + 6, n) = n Sheets("Лист2").Cells(lLastRow2 + 7, n) = "хз2" Next n Next k End Sub | чтобы вытащить данные в скобках: Код: Mid(Range("A6"), InStr(Range("A6"), "(") + 1, Len(Range("A6")) - InStr(Range("A6"), "(") - 1) | Код: marks = ActiveSheet.Range(Cells(k, 1), Cells(k, 15)).Find(Sheets("Лист2").Cells(lLastRow2 + 1, n), LookAt:=xlWhole).Offset(1, 0).Value | Код: let data = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content], names = List.Buffer ( Table.ColumnNames ( data ) ), sortNames = List.Buffer ( List.Sort ( names, ( x ) => Number.From ( x ) ) ), transform = Table.FromRecords ( Table.TransformRows ( data, ( r ) => [ vals = List.Transform ( Record.FieldValues ( r ), ( x ) => Text.Split ( Text.BetweenDelimiters ( x, "(", ")" ), "," ) ), sortVals = List.Transform ( Record.FieldValues ( Record.ReorderFields ( r, sortNames ) ), ( x ) => Text.Split ( Text.BetweenDelimiters ( x, "(", ")" ), "," ) ), transform = List.Transform ( List.Zip ( { vals, sortVals } ), ( x ) => Text.Combine ( { "(", Text.Combine ( List.Intersect ( x ), "," ), ")" } ) ), firstVals = List.Transform ( Record.FieldValues ( r ), ( x ) => Text.BeforeDelimiter ( x, "-" ) ), resultLst = List.Transform ( List.Zip ( { firstVals, transform } ), ( x ) => Text.Combine ( x, "-" ) ), result = Record.ReorderFields ( Record.FromList ( resultLst, names ), sortNames ) ][result] ) ) in transform | |