Answered

How to Extract a portion of a single-column collection?

(0) Share

Report

Posted on by ChrisChong

Help please. Below is a portion of a single-column, 50-rows collection shown in a gallery.

How could I create another collection that consists of only the 8 rows (yellow) beginning with the line “Address:” and including or excluding blank rows? Put another way, I want to extract the 8 rows (yellow) of collection1 to a new collection.

I’ve tried this (among others) but it does not work as I want. It returns only the first row and not the following 7 rows.

OnSelect: ClearCollect(colAddress, FirstN( Search(colByNewLine, "Address:","Result"),8))

Gallery showing collection1

Thank you for any help.

Chris

Categories:

General Questions

I have the same question (0)

All responses (11)

Answers (2)

mdevaney 29,987 Moderator on at

Like (0)

Report
Re: How to Extract a portion of a single-column collection?

@ChrisChong

Can you please let me know how many records are stored in the collection when you put only this code in the OnSelect property.

Search(colByNewLine, "Address:","Result")

A screenshot of the collection in the Collections Viewer would be helpful too.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Was this reply helpful? Yes No
ChrisChong on at

Like (0)

Report

Re: How to Extract a portion of a single-column collection?

I'm sorry to report that I still get only the row showing "Address:". Here are the screen shots if it helps:

Was this reply helpful? Yes No
mdevaney 29,987 Moderator on at

Like (0)

Report

Re: How to Extract a portion of a single-column collection?

@ChrisChong

That was only a test to see what the result of SEARCH was and not meant to be a fix.

Can you please give further details (including code) on how you made the text highlighted in yellow?

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Was this reply helpful? Yes No
ChrisChong on at

Like (0)

Report
Re: How to Extract a portion of a single-column collection?

So sorry to be misleading. The yellow highlighting was made by cutting/pasting from PA Studio into a in a graphics program in an attempt to make what I was referring to clearer. As to the code,
I have a single-column collection I called "collection1" of some 55 rows; the single column name is "Result".
I would like to create a 2nd collection--let's call it "collection2"-- which should be a subset of collection1.
Collection2 should contain only 8 rows of collection1 beginning with the row containing the word, "Address:"
Hope this is clearer.
Chris

Was this reply helpful? Yes No
mdevaney 29,987 Moderator on at

Like (0)

Report

Re: How to Extract a portion of a single-column collection?

@ChrisChong

Can I see more of what Collection1 looks like in the Collections Viewer. I am still trying to wrap my head around the structure of that collection.

Was this reply helpful? Yes No
ChrisChong on at

Like (0)

Report

Re: How to Extract a portion of a single-column collection?

Ok. I appreciate very much your help.
Chris

Was this reply helpful? Yes No
mdevaney 29,987 Moderator on at

Like (0)

Report
Re: How to Extract a portion of a single-column collection?

@ChrisChong

That screenshot is VERY helpful. Could you isolate the desired information using LASTN and FIRSTN functions?

LastN(FirstN(colByNewLine, 16), 7)

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Was this reply helpful? Yes No
ChrisChong on at

Like (0)

Report

Re: How to Extract a portion of a single-column collection?

No, I could not use the code you suggest: LastN(FirstN(colByNewLine, 16), 7) because the desired portion does not consistently begin on the same row as in this example. That's the reason I wanted to extract 8 rows beginning with the row containing the word, "Address:"
I appreciate your suggestions.
Chris

Was this reply helpful? Yes No
Verified answer

mdevaney 29,987 Moderator on at

Like (1)

Report
Re: How to Extract a portion of a single-column collection?

@ChrisChong

If the text does not always appear in the same place then is is necessary to add a new column for Row Number to your collection. When you detect the position of "Address:" you can get the Row Number and then supply it to the LASTN + FIRSTN formula I shared.

//Row counter ClearCollect(rowCounter,{Value:1}); //Create a new collection with the row number ForAll( colByNewLine, Collect(myNumberedCollection,{RowNumber: First(rowCounter).Value, Result: colByNewLine[@Result]}); Patch( rowCounter, First(rowCounter), {Value: First(Sort(myNumberedCollection,RowNumber,Descending)).RowNumber + 1} ); ); //Find what row Address appears on Set(startingRow, Lookup(myNumberedCollection, Result = "Address:", RowNumber);

Then you could supply this code to the Items property of your gallery to make the Address section appear

LastN(FirstN(myNumberedCollection, startingRow + 7), 7)

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Was this reply helpful? Yes No
ChrisChong on at

Like (1)

Report

Re: How to Extract a portion of a single-column collection?

Pure genius. Thank you. I admit I thought of a similar solution but I convinced myself there must be another way. I am pretty sure your solution will work. Please allow me to implement it and report back.
Thanks again.
Chris

Was this reply helpful? Yes No