Detecting and moving data

[jakpak]

Hello,

I have a very complex request that I’m hoping someone can help me with. In a spreadsheet, I have lists which are split across multiple cells. The lists are of different lengths, and take up different numbers of cells. I’m hoping someone can show me a macro that will automate moving the lists from multiple cells to individual cells. I’m aware of the =CONCATENATE() function, but since we have hundreds of these files, and they may each contain hundreds of lists, I was hoping someone with a better understand of Basic than me could help me automate the whole process.

These images show what my data currently looks like, and what I’d like it to look like. My best guess is for the program to detect empty cells in the ‘Part Number’ column, and then copy the Ref Des from the “Description” column to their new spot, repeat for every item.

Hope someone can help me!

-thanks

–UPDATE–
I figured out a way to do by converting the sheet to a CSV and then running text-edits on it, then importing the CSV back into Calc. I’d still like a way to do this IN Calc itself, if anyone has any ideas.

[doug]

don’t you want the RefDes in your example to repeat on each repeated row too? You would run a one-time script that would search for the populated row and then read the stuff ultimately copied into RefDes, put that in a new column etc.

[jakpak]

The new format is just what was requested by one of the companies I have to send these files to. Can you more fully describe that “one-time script” to me? If you’ve got a notion of how to do that, it would be very helpful, even if I ultimately have to make more changes to satisfy that company.

[doug]

Here is an example of the macro that you desire. This takes the contents of a unique sheet called Source and creates another sheet called Export formatted roughly as you desire. I am reverse-engineering your source format, so undoubtedly this is imperfect. At a high level, the macro iterates down the first column of your sheet until it runs out of cells with something in them. When it finds a cell with a value, it iterates across the columns and reaches down a few rows as dictated by the source format, copying the source into rows as desired. A copy of the calc spreadsheet and macro also is available here.

Sub CopyMoveRows

shs = ThisComponent.Sheets
If shs.hasByName("Export") Then shs.removeByName("Export")
newSheet = ThisComponent.createInstance("com.sun.star.sheet.Spreadsheet")
shs.insertByName("Export", newSheet)

srcSheet = shs.getByName("Source")
x = 0
y = 0
cellFocus = srcSheet.getCellByPosition(x,y)

Do While Len(cellFocus.String) > 0
  cellOutput = newSheet.getCellByPosition(x,y)
  cellOutput.String = cellFocus.String
  cellOutput.CharWeight = cellFocus.CharWeight
  x = x + 1
  cellFocus = srcSheet.getCellByPosition(x,y)
Loop

srcY = 1
srcX = 0
expRow = 1
CtEmpty = 0

Do While CtEmpty <= 8
  srcX = 0
  cellFirst = srcSheet.getCellByPosition(srcX,srcY)

  If Len(cellFirst.String) > 0 Then

    CtEmpty = 0
    i = 1
    RefDes = ""
    Do While i <= 3
      cellFocus = srcSheet.getCellByPosition(1,srcY + i)
      RefDes = RefDes & cellFocus.String & " "
      i = i + 1

    Loop

    i = 0    
    cellMfg = srcSheet.getCellByPosition(5,srcY)
    Do While Len(cellMfg.String) > 0 And i <= 3
    
      cellFocus = srcSheet.getCellByPosition(srcX,srcY)

      Do While Len(cellFocus.String) > 0
         cellOutput = newSheet.getCellByPosition(srcX,expRow)
         cellOutput.String = cellFocus.String
         srcX = srcX + 1
         cellFocus = srcSheet.getCellByPosition(srcX,srcY)

      Loop
    
      cellOutput = newSheet.getCellByPosition(srcX,expRow)
      cellOutput.String = RefDes
    
      cellOutput = newSheet.getCellByPosition(5,expRow)
      cellOutput.String = cellMfg.String
    
      cellMfg = srcSheet.getCellByPosition(5, srcY + i)
      expRow = expRow + 1
      i = i + 1
      srcX = 0
      cellMfg = srcSheet.getCellByPosition(5, srcY + i)
    
    Loop
  Else
    CtEmpty = CtEmpty + 1

  End If
  srcY = srcY + 1
Loop
End Sub

(if this answers your question, please accept the answer by clicking the check mark () to the left)

[jakpak]

Great, that’s really impressive! Thanks a bunch.
I do have one question about it though: the line “srcSheet = shs.getByName(“Source”)”
Is is possible to change this to get sheet from the sheet index? I found the get sheet index code, but don’t know how to make it select a sheet by index. This would just save me the minor step of renaming my sheets to “source” or changing the code to match the sheet name each time. Otherwise, this works quite well.

[doug]

That’s right, you can instead use the method .getByIndex(0) instead of .getByName("Source"). That assumes it is either the only or first sheet.

[jakpak]

Thanks, that’s great! I’d upvote you on all your helpful responses, but it won’t let me because I’m a new member >:-/

[doug]

i appreciate you accepting the answer.