Forum Discussion

Jack1295's avatar
Jack1295
Copper Contributor
Oct 16, 2024

Fill Column based on Specific data from another Column

Hi All,

 

Flash fill has been playing up a bit on this column, I am trying to create a Formula to allow excel to check what colour is in ROW F and then Put that colour into ROW D. There is around 128,000 products.

 

I am having issues with flash fill because some of the fields have Black + Honeycomb Filter and flash fill just puts in "Filter" which isnt a colour. Or Instead of putting Anodic Bronze Matt it just puts "Matt"

 

Is there a Formula that can do this ?

 

Happy to make a table with all the colours etc....

 

Thanks !

excel
Formulas and Functions
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Oct 18, 2024

    Jack1295 

    This formula utilizes a recursive Lambda called 'ExtractColor'.  I thought it was best to avoid functions that would be slow (e.g. SEARCH, FIND) to calculate with a large data set and go with a function that will terminate when a color is found.

    A few presumptions:

    -each cell has 1 color

    -you provide a list of colors to locate

     

     

    =BYROW(texts, LAMBDA(each_text, ExtractColor(each_text, ColorsTbl)))

     

    ExtractColor being:

     

    ExtractColor = LAMBDA(string, list_of_colors,
        LET(
            next_color, TAKE(list_of_colors, 1),
            err, ISERROR(next_color),
            check, ISNUMBER(
                XMATCH("*" & next_color & "*", string, 2)
            ),
            discard, DROP(list_of_colors, 1),
            IF(
                check,
                next_color,
                IF(err, "", ExtractColor(string, discard))
            )
        )
    )

     

    The above function resides in the attached workbook. An easy way to use it is to simply lay in your data in the workbook (move/copy sheet into existing workbook).

     

    This gets a little easier once the Regex functions become available!

     

Resources