CALC - adding automatically rows when two cells match in different tabs

English
#1

Hi all,

I have an automatisation question but it feels like a long stretch. i will try to explain it as simple as possible. In one CALC file i have different tabs:

1st tab is as following:
article number 1 | article name 1 | other value | other value | …
article number 2 | article name 2 | other value | other value | …
article number 3 | article name 3 | other value | other value | …

2nd tab is as following:
article name 1 and underneath are different rows containing different values and decriptions.
article name 2 and underneath are different rows containing different values and decriptions.
article name 3 and underneath are different rows containing different values and decriptions.

- - EDIT - -
My new goal is how i can transport the information in the same row as the article name from TAB 1 to TAB 2 with a formula (or other automatisation). It should recognize the cell article name, since this is available in both tabs.

Does somebody knows how?

I put the above in an example document. I made a 3th tab where you can see how it should look.

CALC exemple.ods (23.8 KB)

#2

Welcome!
Answer Yes, it’s manageable to your question

How about solving the problem in reverse order? I mean don’t insert blocks of cells into the first list, but take the second list and replace article names with strings from the first list?

I assure you, by trying to simplify the description of the problem, you will end up with a simplified answer that will not help solve the real problem. The best and fastest way to discuss a problem and find a solution is to provide a sample file instead of a verbal description.

#3

I did not look at it like that. The question still remains, but i don’t need to tackle inserting various rows anymore. Thanks for that perspective.

#4

What now?
Will there be a modified question replacing the one posted here?

#5

I see what you mean. I’ll see if i can muster up a sample file and reformulate the question.

#6

I edited the question and put in an example file.

#7

Cross posted on the AOO forum

#8

It seemed to me that the macro will do this task better than the built-in tools of Calc. Here is the code that (I think) does the job:

Sub combimeData
Const MAIN_SHEET_NAME = "TAB 1"
Const CHILD_SHEET_NAME = "TAB 2"
Const MATCH_COLUMN_NUMBER = 1	' Column B

Dim oSheets As Variant, oSheet As Variant 
Dim oCursor As Variant, oRange As Variant
Dim oSearchDescriptor As Variant, oFound As Variant
Dim aIndex As Variant 
Dim oRangeAddressMain As New com.sun.star.table.CellRangeAddress
Dim oRangeAddressChild As New com.sun.star.table.CellRangeAddress
Dim oTargetCellAddress As New com.sun.star.table.CellAddress
Dim i As Long, j As Long, nRow As Long
Dim sNewSheetName As String 
	oSheets = ThisComponent.getSheets()
Rem Are the names for the data sheets correct?
	If Not oSheets.hasByName(MAIN_SHEET_NAME) Then 
		MsgBox("Sheet " & MAIN_SHEET_NAME & " with Main data not found.", MB_ICONSTOP, " Check sheet name.")
		Exit Sub 
	EndIf 
	If Not oSheets.hasByName(CHILD_SHEET_NAME) Then 
		MsgBox("Sheet " & CHILD_SHEET_NAME & " with child subdata not found.", MB_ICONSTOP, " Check sheet name.")
		Exit Sub 
	EndIf 
Rem We do not spoil the original data - the macro result will be placed in a new sheet
	sNewSheetName = "Result_" & Replace(Replace(Time(),":","_")," ","_")
	If oSheets.hasByName(sNewSheetName) Then oSheets.removeByName(sNewSheetName)
	oSheets.insertNewByName(sNewSheetName,oSheets.getCount())
	
Rem Where is the main data located?
	oSheet = oSheets.getByName(MAIN_SHEET_NAME) 
	oCursor = oSheet.createCursor()
	oCursor.gotoEndOfUsedArea(True)
	oRangeAddressMain = oCursor.getRangeAddress()
	nRow = oRangeAddressMain.EndRow + 1
Rem Get a list of keywords to search
	oRange = oSheet.getCellRangeByPosition(MATCH_COLUMN_NUMBER,0,MATCH_COLUMN_NUMBER+2,nRow)
	aIndex = oRange.getDataArray()
Rem Where is the additional data?
	oSheet = oSheets.getByName(CHILD_SHEET_NAME) 
	oCursor = oSheet.createCursor()
	oCursor.gotoEndOfUsedArea(True)
	oRangeAddressChild = oCursor.getRangeAddress()
	nRow = oRangeAddressChild.EndRow
	aIndex(UBound(aIndex))(1) = nRow + 1
Rem We will not search for keywords throughout the sheet, but only in the specified column
	oRange = oSheet.getCellRangeByPosition(MATCH_COLUMN_NUMBER,0,MATCH_COLUMN_NUMBER, nRow)
Rem Find row numbers where keywords are located	
	oSearchDescriptor = oRange.createSearchDescriptor()
	oSearchDescriptor.SearchType = 1
	oSearchDescriptor.SearchWords = True
	For i = LBound(aIndex) To UBound(aIndex)-1
		If Trim(aIndex(i)(0)) = "" Then
	 		aIndex(i)(1) = -1
	 	Else 
			oSearchDescriptor.setSearchString(aIndex(i)(0))
	 		oFound = oRange.findFirst(oSearchDescriptor)
	 		If IsNull(oFound) Then 
	 			aIndex(i)(1) = -1
	 		Else 
	 			aIndex(i)(1) = oFound.getCellAddress().Row
	 		EndIf 
	 	EndIf
 	Next i
Rem Find (calculate) the last row numbers for each subrange
	For i = LBound(aIndex) To UBound(aIndex)-1
		If aIndex(i)(1) >= 0 Then 
			nRow = aIndex(UBound(aIndex))(1)
			For j = LBound(aIndex) To UBound(aIndex)
				If aIndex(j)(1) > aIndex(i)(1) Then 
					If aIndex(j)(1) < nRow Then 
						nRow = aIndex(j)(1) - 1
					EndIf 
				EndIf 
			Next j
			aIndex(i)(2) = nRow
		EndIf 
	Next i
Rem Copy data to result sheet
	oSheet = oSheets.getByName(sNewSheetName)
	oTargetCellAddress = oSheet.getCellByPosition(0, 0).getCellAddress()
	nRow = oTargetCellAddress.Row
Rem Copy every row from main sheet	
	For i = LBound(aIndex) To UBound(aIndex)-1
		oRangeAddressMain.StartRow = i
		oRangeAddressMain.EndRow = i
		oSheet.copyRange(oTargetCellAddress, oRangeAddressMain)
		oTargetCellAddress.Row = oTargetCellAddress.Row + 1
Rem If required, add (copy) the range from the child sheet
		If aIndex(i)(1) >= 0 Then
			oRangeAddressChild.StartRow = aIndex(i)(1)+1
			oRangeAddressChild.EndRow = aIndex(i)(2)
			oSheet.copyRange(oTargetCellAddress, oRangeAddressChild)
			oTargetCellAddress.Row = oTargetCellAddress.Row + aIndex(i)(2) - aIndex(i)(1)
		EndIf 
	Next i
Rem Set the optimal column width
	oCursor = oSheet.createCursor()
	oCursor.gotoEndOfUsedArea(True)
	oCursor.getColumns().OptimalWidth = True
Rem Activate result sheet
	With ThisComponent.getCurrentController()
		.Select(oSheet.getCellByPosition(0,0))
		.Select(ThisComponent.createInstance("com.sun.star.sheet.SheetCellRanges"))
	End With 
End Sub

Tab 1 contains a button that will execute this code - CALC exemple macro.ods (25.5 KB)

Please don’t be surprised that after running the macro for the first time, you don’t get the result you expected. The matter is that for the keyword Subtext 2 there is no corresponding block on the sheet Tab 2. Just fix Ssubtext 2 on the sheet Tab 2 and press the button again. Now you know where to look for the error in the first place if the macro ever gives wrong results.

Please excuse me for the delay in replying. The macro was ready a few hours ago, but I did not have time to publish it - the power went out. As you know, russian terrorists are shelling the energy infrastructure in my country. Thus, these barbarians harm not only me, not only all the inhabitants of Ukraine, but also you and other people around the world whom I could help if our power plants were not bombed.

#9

Thank you, this even more than i would dream or i would ask of you.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.