Forum Discussion

williamdhicks's avatar
williamdhicks
Copper Contributor
Feb 06, 2023

Extract Attribute (column name) from data living in a cell

I'm trying to create a backwards formula (extraction): That will find the attribute (column name)in a All Columns value (based on data in a created formula.)   For example,    Column 1   Column ...
BI & Data Analysis
excel
Formulas and Functions
Macros and VBA
mtarler's avatar
mtarler
Silver Contributor
Feb 07, 2023
since you asked for help on this post I assume the post by peiyezhu didn't help. Can you please explain what you need a little better? unfortunately the text formatting makes it really hard to read and even if I'm reading it correctly I still don't get it. Maybe attach an example file or if it won't let you do that you can share it on Sharepoint or Onedrive or similar or you can PM it to me. another option is to the 'Table' tool in the editor here to at least make the data line up. Regardless, please include additional information about what you have and what you want.
williamdhicks's avatar
williamdhicks
Copper Contributor
Feb 08, 2023
I'm trying to figure out if I have a paragraph of data...if the data in one cell of the column is in it...and then if it is, capture that column header, so I can backward out a formula to create that paragraph for other cells. Hope that makes sense.
  • mtarler's avatar
    mtarler
    Silver Contributor
    Feb 08, 2023
    I can't really make sense of it all but you can check if Col X data is in the paragraph by using
    ISNUMBER(SEARCH( ... ) )
    If you want to do all the columns dynamically you can use LAMBDA and a helper function like MAP or BYCOL or REDUCE to cycle through all the elements.
    I would write something more specific but I still don't get what is needed.
    What I get so far is you have table with X columns or data and then in column Y you have paragraph and want to know which columns of data have values found in that paragraph. and the final output should be what? a list of column titles? is that a list in 1 cell or and array of cells? and what is that about create that paragraph for other cells?
    is that paragraph an 'ordered list' of prior column values and you want to have the same 'ordered list' but using values from a different row?
    • williamdhicks's avatar
      williamdhicks
      Copper Contributor
      Feb 13, 2023
      The final output would be a formula to create that paragraph in the future, so that you could see what was in it and gather all the data in those columns to create it. Basically you have a description ( a paragraph) and you are trying to extract the attributes (the columns), so in the future you can recreate that description (with the formula)
      Example: By seeing the Output Formula, and using the one in the To Recreate Formula, I can now create the paragraph in the Dog row. (also by seeing al the Output Formulas for the column, I can decide which one is correct for that group of items):
      Col A Col B Col C Paragraph Output Formula To Recreate Formula
      Cat 10 lbs. Diabetes Cat, 10 lbs., Diabetes Col A, Col B, Col C A&,", "&B&", "&C
      Dog 24 lbs. Broken Leg Col A, Col B, Col C
      • mtarler's avatar
        mtarler
        Silver Contributor
        Feb 13, 2023

        williamdhicks ok if you can't attach an actual sheet you can upload to onedrive, sharepoint, or similar and share a link or PM it directly to me.  At the least you can use the 'full editor' here, hit the 3 dots (...) and then insert a table

        so we can read what you are trying to show.

        That all said I think I might know what you want and here is a formula to try:

        =TEXTJOIN(",",1,FILTER($A$1:$H$1,($A2:$H2<>"")*ISNUMBER(SEARCH($A2:$H2,$I2))))

        The columns are A:H and the paragraph is in I.  IF any column with something in it will be in the final paragraph then you only need the ($A2:$H2<>"") part.  This will join the column headers ($A$1:$H$1) in a comma delineated list but you could change that to use what ever you want.  Regardless you will need to parse that list in a formula to 'recreate' that paragraph (i.e. even if the text in the cell is designed to be the exact formula needed to recreate the paragraph Excel doesn't support an EVAL() command to execute that text for safety reasons.

        See attached.

         

Resources