Duplicate Entire Rows or Values Based on Cell Value in LibreOffice Calc using a Macro
This macro will duplicate values in column A & B according to the value specified in column C. The results will be generated in columns D & E. The first row is the header. The macro is assigned to a button (named Push in this case). This macro works only in LibreOffice Calc and uses LibreOffice Basic
Sample Data before running the Macro |
The Macro:
Sub DuplicateRowsAsPerValue()
Dim i As Double
Dim j As Double
Dim z As Double
Dim w As Double
sheet = ThisComponent.getSheets().getByIndex(0)
For i = 0 To 10000
j = sheet.getCellByPosition(2, i).Value
For z = 1 To j + 1
sheet.getCellByPosition(3, z + w).String = sheet.getCellByPosition(0, i).String
sheet.getCellByPosition(4, z + w).String = sheet.getCellByPosition(1, i).String
Next z
w = w + z - 2
Next i
End Sub
Dim i As Double
Dim j As Double
Dim z As Double
Dim w As Double
sheet = ThisComponent.getSheets().getByIndex(0)
For i = 0 To 10000
j = sheet.getCellByPosition(2, i).Value
For z = 1 To j + 1
sheet.getCellByPosition(3, z + w).String = sheet.getCellByPosition(0, i).String
sheet.getCellByPosition(4, z + w).String = sheet.getCellByPosition(1, i).String
Next z
w = w + z - 2
Next i
End Sub
The Macro (Adapted to run on LibreOffice Calc) |
Assign the Macro to a Button (in this case the the green one named Push) |
Duplicated values in columns D & E as per values in column C |
Tested on:
LibreOffice
Version: 7.4.7.2 / LibreOffice Community
Build ID: 40(Build:2)
CPU threads: 8; OS: Linux 6.1; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Debian package version: 4:7.4.7-1+deb12u7
Calc: threaded
Excel Macro from here: https://techcommunity.microsoft.com/discussions/excelgeneral/formula-to-duplicate-entire-rows-based-on-cell-value/3269120
Comments
Post a Comment