[Solved] Merging Multiple Table into one without blanks

[alset6]

I am fairly new at this, so I don't know if what I need is even possible. I have several lists that have similar data. I want to create another list that will combine data from the lists if the data in the third column is greater than a certain number. I also want the new list not to contain any empty spaces. I made one list that combined the two list, then made another list that checked if the value in col 3 is greater than 0.
List 1
Academics Alchemy/Chemistry 3
Academics Astro Sciences 0
Academics Computers 0

List 2
Academics History/Civics 0
Academics Law 1
Academics Life Sciences 0
Academics Medicine 0
Academics Military Theory 0

Combined List
Academics Alchemy/Chemistry 3
Academics Astro Sciences 0
Academics Computers 0
Academics History/Civics 0
Academics Law 1
Academics Life Sciences 2
Academics Medicine 0
Academics Military Theory 0


=IF(Charts.$C1>0;Charts.A1;"") =IF(Charts.$C1>0;Charts.B1;"") =IF(Charts.$C1>0;Charts.C1;"") yields:
Academics Alchemy/Chemistry 3



Academics Law 1
Academics Life Sciences 2

Is there a way to display this new chart without the lines between rows that have Alchemy and Law? Also, is there a way to do this without having to combine the other lists first? Any help would be greatly appreciated.

[alset6]

I used the sort and filter options, but if the original table is changed, the sort/filter doesn't stay. I was able to figure out how to do it in excel, but am not sure how to change the formula to work in openoffice. Here is the formula I used in excel, maybe it can give you an idea of what I am trying to accomplish:

=IFERROR(INDEX(Charts!$A$1:$C$84, SMALL(IF((INDEX(Charts!$A$1:$C$84, , 3)>0), MATCH(ROW(Charts!$A$1:$C$84), ROW(Charts!$A$1:$C$84)), ""), ROWS(Character!T3:$T$3)), COLUMNS($A$1:A1)),"")

I did change all the , to ; and ! to . but it still returns an error.

I was looking at the tutorial you suggested; however, I must be missing something, cause it's not making sense to me. Any further assistance you can give, would help me and would be greatly appreciated.

[robleyd]

Is the error #Name? It helps if you tell us what the error is.

Code: Select all

Charts!$A$1:$C$84

Calc uses a period as the separator between the sheet name and range, so try replacing ! with .

[alset6]

Thanks, but as you can see from my earlier post, I did change the ! to a .

Here is the excel formula with those changes: =iferror(INDEX($Organization.$A$1:$C$84;SMALL(IF((INDEX($Organization.$A$1:$C$84;;3)>0); MATCH(ROW($Organization.$A$1:$C$84);ROW($Organization.$A$1:$C$84)); "");ROWS($Character.T$3:$T4)); COLUMNS($A$1:A2));"")

It returns a #Value! error.

As stated, I have been able to get it to work as far as only displaying those entries that match a certain criteria; however, there are spaces between the entries for those that did not match. I tried using Data>Filter>Standard Filter. It did get rid of the excess lines; however, if the data is updated, the filtered list does not update. This can be a problem if one person updates and does not let the next person know.

Any further help would be appreciated.

[Lupp]

(Basically this is a kind of task done with databases by a SELECT ... WHERE statement in SQL.)

If you need to get the selection in a spreadsheet with upates on recalculation (without further interaction) there are two ways:
-1- Use helper columns and slightly complicated formulae bloating the file size and spoiling efficiency.
-2- Develop user code to create and output the updated selection after any change to the primary data.

How to do it the way -1- is demonstrated in the attached example. I cannot recommend this way for more than about 2000 rows of primary data.

Resorting to user code always comes with disadvantages, in specific if the document (file) needs to go to different systems.

[alset6]

Thank you!!!!!!!!! This was a great help, and the example sheet was awesome. I was able to figure out what I needed to do, and will be able to use it in other projects!