How can we help?

New post
0

spreadsheet - find row based on first column value

Avatar
T V
None
Follow

I have a spreadsheet of expense values by month. The rows represent different expense categories; the columns months.

 

I would like to search for the row whose first value matches "abcdefg", for example. I tried the following statement but it's erroring out:

 

select(@Income Statement,1:150;, (@Income Statement,A:A;="abcdefg"))

 

I assume there is a problem with the first parameter in the select() function, but I wasn't sure what it should look like. I need to search all of the rows in the spreadsheet to find the correct row. My row will always be called "abcdefg", but I have no guarantees that it will always be row 9, or whatever. So I cannot just simply click that row.

Thanks for your help!

--Trudy

Date Votes

7 comments

  • Avatar
    Janice Janczyn Official comment

    Hi Trudy,

    Typically SELECT is used to select from one column of data based on data in another column of data. The 2 parameters in the SELECT must align exactly, so they must contain the same number of items. To select a row of data based on the data in one column, you must select each column in the row. Typically this would look like

       SELECT( @Income Statement,A:A, (@Income Statement,A:A;="abcdefg"))

       SELECT( @Income Statement,B:B, (@Income Statement,A:A;="abcdefg"))

       SELECT( @Income Statement,C:C, (@Income Statement,A:A;="abcdefg"))

    To select the entire row at once, you still must specify the individual columns, but you can concatenate them using the CONCAT function (inserting commas between the columns as a separator)

       SELECT( CONCAT( @Income Statement,A:A, ",", @Income Statement,B:B, ",", @Income Statement,C:C ), (@Income Statement,A:A;="abcdefg"))

     

    For details, please see the following articles:

    Thanks,

           Janice

     

  • 0
    Avatar
    T V

    I tried your example, but it keeps returning the first column, not the entire row if that makes sense. It keeps returning the "January" value for "Expenses", not the entire "Expenses" row.

    I was thinking maybe I could try using array() instead of concat()? I am not sure what else to do. It seems like this is a very straightforward action with spreadsheets, select the row whose column A value is "Expenses". 

    If you have any more ideas, please share! Thank you!

    --Trudy

  • 0
    Avatar
    Janice Janczyn

    Hi Trudy,

    Your observation is correct: SELECT( @Income Statement,A:A, (@Income Statement,A:A;="abcdefg")) will return data only from a single column, in this case, A:A.

    To return a row of data, you must use CONCAT to ensure your data is correctly aligned by rows as in this example which concatenates 3 columns (A:A, B:B and C:C).

        SELECT( CONCAT( @Income Statement,A:A, ",", @Income Statement,B:B, ",", @Income Statement,C:C ), (@Income Statement,A:A;="abcdefg"))

    If you need further assistance, please contact our support at support@klipfolio.com

     

    Thank, you,

               Janice

     

  • 0
    Avatar
    T V

    Janice,

    Sorry - I should have specified. I tried SELECT() with CONCAT(). I triple-checked to make sure I had all of the commas etc correct. And it returns only the first column's data. It is hard to trouble-shoot that statement, though, because I have 13 columns to concatenate. The statement gets unwieldy quickly, and the function input box is not resizeable so it's hard to see if everything is typed in correctly.

    I was thinking maybe a MAP() would work - if I create a map from the rows of the spreadsheet with their columnA values as keys? Then I could easily lookup what I needed. Does that sound feasible?

    --Trudy

     

  • 0
    Avatar
    Janice Janczyn

    Hi Trudy,

    Please send a ticket to support@klipfolio.com and enable support access so we can take a closer look: 

        https://support.klipfolio.com/hc/en-us/articles/215634348-Granting-Klipfolio-Support-access-to-a-user-account

     

    Thanks,

         Janice

  • 0
    Avatar
    David Woodard

    Hi Janice,

    Was there a solution to this problem?  I believe I am having a similar issue with trying to select a "row" based off information in "Column A".  I am creating a bar/line chart. In my data sheet I have the months listed across the top with quantities below each and site locations listed below "Column A.  I am trying to use a variable that would select from "Column A" and slice the the row across.

    Thanks,

    David

  • 0
    Avatar
    Janice Janczyn

    Hi David, 

    If your columns are fixed, that is column A followed by columns B  through M (for January through December), you can use the same approach suggested above. For example, the following selects from columns B, C and D where column A = $variableName.

       SELECT( CONCAT( @B:B, ",", @C:C, ",", D:D ), 

                      (@A: = $variableName) )

    If you have further questions, please send a ticket to support@klipfolio.com and enable support access so we can take a closer look.

     

    Thanks,

              Janice

Please sign in to leave a comment.