topic RE: Selecting Matching Character Values in the Same Column in Discussions

RE: Selecting Matching Character Values in the Same Column

Jaz — Fri, 09 Jun 2023 23:25:19 GMT

Hi,

I'm aware I could make use of loops to solve this problem but I was wondering if there was a function / another way to find matching character values in one row in my data table in JSL?

Any help would be appreciated.

RE: Selecting Matching Character Values in the Same Column

txnelson — Sun, 15 Jul 2018 17:25:35 GMT

Create a column called RowNum and give it the formula

Row();

Then run the Stack Platform

Tables==>Stack

stacking all of the character columns you want to find matches for. Also select the column RowNum to be included in the new data table.

Now run the Summary Platform

Tables==>Summary

Select the column called Data as the Statistics column choosing "N" as the statistic, and then select the column RowNum as the Group column.

This will give you a new data table, where the column N Rows, will indicate the number of matches for each value for each row. Just select one of the cells where only 1 match was found, and right click. Select, "Select Matching Cells". Once all of the matching rows are selected, go to the RowState column, and right click on one of the selected rows. Select "Delete Rows". What is left are all of the rows with matches for values in those rows.

RE: Selecting Matching Character Values in the Same Column

gzmorgan0 — Sun, 15 Jul 2018 20:54:57 GMT

Jim provided an excellent suggestion. Here is another alternative, that uses Combine Columns. From the UI,

select the columns to be searched, for my example table below, :C1 thru :C10
select from the Main Menu > Cols > Utilities > Combine Columns, I used the carat (^) as the delimiter and called the new column "Search"
create a new column, and create a function; here I am searching for "y". If you want a count use the formula. Note Words(:Search,"^") creates a list of column values, Loc returns what positions match the search string ("y") and N Row is the Count

N Row( Loc( Words( :Search, "^" ), "y" ) )

if you want to know which columns (here I am searching for "y"), use the formula below. It returns a vector of which columns contain the search string., e.g, [1,5,7] or [] if no matches.

Loc( Words( :Search, "^" ), "y" )

Attached is a script that creates a sample table, creates the combined columns, and create the two formula columns for Count and loc