RE: Selecting Matching Character Values in the Same Column

[#1]

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.

[#2]

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.

[#3]

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