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
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