[Solved] Array of row numbers where cell condition matches

[modern man]

Hi there; I hope it's ok to ask yet another uneducated question...

Like some others, I don't have programming skills. I understand it's necessary to learn about it, but it seems quite much to start from zero, so I'll try to approach it from my very specific use case.

What I want to do:
Get every row number where a condition matches a cell in a specified column and store the row_number into an array.
I'll write some bash-like pseudocode:

Code: Select all

for every_nonempty_cell in column_c; do
 if [[ stringvalue of cell starts with ^B ]];
   add row_number_of_this_cell to array_B
 fi
done

Seems rather trivial but I find it hard to browse the documentation of the API and learn about the Object Inspection tools and so on.
So if someone feels inclined to point me to a solution for this specific case, I'd be grateful.

Cheers and merry holiday everyone

[JohnSUN-Pensioner]

Merry Christmas!
Please tell me: this your question - is a continuation of the theme of the hidden Berlin?
Let me make you a gift: throw the development of this macro, use the facilities available office suite. Saving the time - and yours, and ours - for something more interesting, right?

Click here
Small demo AutoFilter

And once more - Merry Christmas!

[modern man]

Hi JohnSUN,

actually my initial approach was to do it in the document, but I could not figure out how to do it. Of course I'm happy to save everyone's time. Using the filter is kind of what I was looking for.
I'd like to have it in a way that all users can filter out the rows by choosing a value from an item list that's given in a cell.
Maybe someone can point me (in natural language) to the steps that are needed to accomplish the following:

- user chooses a value from a selection list
- the chosen value triggers a hide/show filter that
- is applied to a given range of cells
- filters rows based on the leading character of the value of a column

From your nice gif I can see how the filter works, I'd just like to have that automated after choosing the value from the selection list.

I am willing to read into it, I'd just like to know what I need; it's hard to start from zero.
The topic may be moved to the appropriate section of the forums of course.

And, of course, if someone feels like wasting some of their time, they can still explain to me how to do it as a macro like I asked in the first post .

Thanks for your time and efforts,

Cheers Stephan

[JohnSUN-Pensioner]

No problem, Stephan! Please wait and I show you a few variants of solutions. But please say me - what do you want to do with filtered rows? Only print report? Or you want edit values of this rows?

[modern man]

No, the values are fixed, no need to process them in any way. I get the data from elsewhere, so the columns etc. are a given (i.e. I have to filter by the leading character instead of putting it in its own column).
So, everything I'd need is for all users to just select a value (=town) from a selection list, and after that value is selected, automatically apply or remove a filter to just show the rows that are linked (by means of the leading char in a column) to that selection.
Only printing/reading needed.

[JohnSUN-Pensioner]

Oh, this is a very easy task to be solved at all without macros. If I remember correctly, the first time many years ago, I saw a sample filter data using formulas by Ken Johnson (Sydney). The solution is so elegant that MrProgrammer designed it as a separate tutorial.

But as I understand it, you are determined to realize this problem by using a macro, to a bit learn how to write for the office. So I'll give you macros.
Another request, Stefan. FJCC wrote to you a macro "in general". I'm also going to show you examples of code "in general", without binding to your data.
Perhaps it would be better if you put your sample workbook to the next message. It is very difficult to invent an example test data and then get to know that the asker was referring to a completely different data structure.

[modern man]

I will post a screenshot. If that's considered bad style, I'll remove it of course:

This should clarify what I'm trying to do. Creating a filter to show only needed rows and that will be applied when selecting a value.

Macro is not that important, but since I came to the point where I can set the visible attribute to rows with the absolute row number, it might be interesting to determine the row numbers by content. But it's probably much faster and more correct by doing it with a filter instead of a macro.

I attached a sample ods file.

[JohnSUN-Pensioner]

Well, thanks for waiting.
Please try this

[Villeroy]

Like the vast majority of all Calc questions, this one is a database related question and could be solved within seconds by using a database.
Anyhow,
First I declared the list range "Liste"
2) added a named filter range "Criteria" = $J$1:$J$2 with input cell I2
3) defined an "advanced filter" for the list range using the criteria range.
4) added a SUBTOTAL formula counting the text entries in the first list column. Why do you enter time values as text anyway?

Usage:
1. Enter another letter in I2
2. Data>Filter>Advanced...
2.1. [OK] if the filter range is declared
2.2. pick "Criteria" from the list if not

[Villeroy]

Nother way.
With the first letters stored in separate fields you would have a lot more and better options. IMHO, macros are no way to go.

[modern man]

@JohnSUN and Villeroy, thanks so much for your efforts!
I only checked the first solution from John so far, which works. I am trying to figure out what the macro does from start to end .
I will check out the other solution. Nice way to learn something about the mechanics of calc/OO/LO to have different solutions. I'll be back for some feedback after a while. Greatly appreciated!

Cheers

Stephan

PS:

With the first letters stored in separate fields you would have a lot more and better options

Yeah, I receive this data from 'upstream' and have to deal with it

[Villeroy]

Yeah, I receive this data from 'upstream' and have to deal with it

Well, then you should import correct date/times at least. Always check option "detect special numbers" in the text import dialog. The attachment demonstrates how to split the first letter and create a pivot table on a separate sheet. The pivot table shows the total sum for each category at every day.
The pivot table is the only spreadsheet feature that is able to simulate a primitive kind of database query.

[modern man]

Has been tagged solved already. Sorry I haven't found the time to get into it much further; John's solution perfectly accomplishes what I had in mind, though I don't fully understand it. The other two approaches not quite as good, but as you see, I have such little understanding of oo/lo that it's more efficient for me to first understand the basics of how spreadsheets work .
Many many thanks for your efforts and time, I really appreciate it!
Cheers, Stephan